SSAS Performance Logger

SSAS Performance Logger is a tool that allows you to navigate through the metadata for a tabular model and select from measures and table attributes to build a simple query which is executed and timed.  The query can be executed many times to get an accurate sampling of test variations and to test performance while various loads are placed on the server.

image

My initial objective was to choose two different measures that were alternate methods of calculating the same value, run them side-by-side and then see which performed best.  Then it occurred to me that we can run any number of measure performance tests in iterative loops and compare the results by playing back the captured log results.  Since the results are captured in a log database, the test results for each measure or query can easily be compared and analyzed using tools like Excel and Reporting Services.

One of my main objectives for a future version of the tool is to add logging for server performance counters like memory usage, thread counts and CPU capacity; while these queries are running.

What you will need:

I’ve developed SSAS Performance Logger for use with SSAS Tabular in SQL Server 2012.  To manage the logging database, it uses the OLEDB data provider for SQL Server 2012 which may be backward compatible as far back as SQL Server 2008.  It also uses the ADOOMD data provider version for Analysis Services.  When MDX support is added to a future version, it should support SSAS sources as far back as SSAS 2008.

System requirements:

  • Microsoft .NET Framework 4.5
  • ADOMD 6.0 (can be installed with the SQL Server 2012 SP1 Feature Pack)
  • An instance of SQL Server 2008 or better with administrative/create database rights for the logging database

With these components installed, you should be able to copy the executable to a folder and run it.

To install:

  1. Download the zip file and extract it to a folder on your system
  2. Verify that the required dependent components are installed
  3. Run SSAS Perf Tester.exe

The first time it runs, the application will check for the logging database and prompt you to create it

SNAGHTML21000684

  1. On the SSAS Query page, enter the Analysis Services server or instance name and press Enter
  2. Select a model or perspective, measure and table/attribute from the drop-down list boxes to build the query
  3. Click the Start button to run the query once and see how long it took to run
  4. On the Options page, type or select the number of times to repeat the query
  5. Click the Start button the run the query in a loop.  The results are displayed in a line chart showing the duration in milliseconds for each execution

image

Every query execution is logged for later analysis.  Here is a view of the logItem table in the SSASPerformanceTest database:

image

Features I hope to add soon:

  • Generate MDX queries in a addition to DAX  (added in version 1.1)
  • Handle multiple measures in one testing batch
  • Log server performance counters

Download:

Version 1.2                      June, 2014
Version 1.1                      June, 2014

Advertisements

5 thoughts on “SSAS Performance Logger

  1. hi Paul, it appears that neither 1.15 nor 1.2 do not work scope out cube metadata well with non-instance named server (we have an SSAS Tabular clustered server that has no instance name). Looks like 1.0 is fine. Also the SQL Db, for some reason, cannot be created with these two later versions. If it might be of interest that we provide a testbed for the setup via webex, we could do so…. or provide the errors, etc… thx a bunch, Cos.

    Like

      • I see the button now but when I click on create database button I get the following error:
        —————————
        Bad things happened
        —————————
        GetLogStats exception

        System.Data.SqlClient.SqlException (0x80131904): Invalid object name ‘LogItem’.

        at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

        at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

        at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

        at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

        at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

        at System.Data.SqlClient.SqlDataReader.get_MetaData()

        at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

        at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

        at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

        at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

        at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

        at System.Data.SqlClient.SqlCommand.ExecuteReader()

        at SSAS_Perf_Tester.frmPerformanceTester.GetLogStats()

        ClientConnectionId:630f8b98-17d3-4af3-a5de-74ddb32c670e
        —————————
        OK
        —————————

        Like

  2. Pingback: SSAS Performance Logger Now Available | Paul Turley's SQL Server BI Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s