SQL Server Analysis Services MDX Query performance monitoring - highlights

When it comes to monitor the query performance whether it is TSQL or MDX, the process is similar. You need to have a better understanding on how queries are executed (architecture), what tools are available for monitoring and best practices to improve performance.

So similar to Relational database engine, Analysis Services engine architecture too have components that will help to fine tune the queries. Those three major components of the architecture are Session Management, MDX Query Execution, and Data Retrieval.  In any case when having a Analysis Services database then memory plays important role and having inadequate memory will force Analysis Services to swap data in and out of the Query Execution Engine Cache And Storage Engine Cache that will slow down your application availabiity. Don't forget about disk performance that will have knock-on affect data being read from the Dimension Data and Measure Group Data stores with high spikes on the processor to slow down the performance.

In this regard you can take help of PROFILER that will  allows you to view the events such as: query requests to the server, events to Watch with "Command Begin and Command End",
Execute MDX Script Begin and Execute MDX Script End & Query Dimension so on. When there is a slow performance reported on a cube access then make sure to add up the events, Query Cube Begin and Query Cube End, Get Data From Aggregation, and Get Data From Cache. I would like to enhance a bit on what these events can bring you in identifying the root cause, the Command Begin and Command End event will help to identify the XMLA command sent to the server from the client when the MDX statements are executed by referring the event "Query Begin and Query End". Similar to this the events Query Cube Begin and Query Cube End will help to report the beginning and end of the SELECT statement. The Duration column for the Query Cube End event reports the elapsed time for the execution of the SELECT statement portion of the MDX query, including all subcube queries to the storage engine utilized to resolve the MDX query. If you need to find whether the dimension data is resolved using cache or disk look at the event Query Dimension with EventSubclass column Get Data From Cache, both of these reports the reading of data from dimension cache and measure group cache in the cache system, rather than from a partition by the storage engine.

Similarly using PERFMON (SYSMON) and their counters allows you to track server activity  in monitoring queries, use the following counters:

• MSAS 2005:Cache
• Direct hit ratio
  Evictions / sec
  Misses / sec
• MSAS 2005:Connection
• Current Connections
  Current user sessions
• MSAS 2005:MDX
• Total calculation covers
  Total cells calculated
  Total recomputes
• MSAS 2005:Memory
• Memory Usage KB
• MSAS 2005:Storage Engine Queries
• Dimension queries / sec
  Measure group queries / sec
  Memory
  Available Mbytes
  Page Faults / sec
• Processor
• % Processor Time
  Physical Disk
  Avg. Disk Queue Length

Make sure to baseline your counters before collecting and worrying about whether they are under control or not, refer to the SQL Server performance monitoring requirement and specifications, heard about baseline and benchmarking? blog post as most of the counters are not meaningful unless you putup a baseline & benchmarking.

Going further down  to these counters value when collected refer to the following:

• Direct hit ratio - Ratio of cache direct hits to cache lookups, for the period between obtaining counter values.  Higher is better.
• Evictions / sec - Rate of evictions from the cache.  This is per partition per cube per database.  Typically due to background cleaner. Could indicate memory issue is the cache is being excessively cleaned.
• Misses / sec - Rate of cache misses.  High ratio indicates that queries are having to go to disk
• Current Connections – Current number of client connections established.
• Current user sessions - Current number of user sessions established.
• Total calculation covers - Total number of of calculation covering subcubes in MDX query plan. High value means that queries are being resolved cell by cell instead of block operations.
• Total cells calculated - Total number of cell properties calculated. High value means queries being resolved cell by cell.
• Total recomputes - Total number of cells recomputed due to error
• Memory Usage KB - Memory usage of the server process.  Same as perfmon counter for Process\PrivateBytes.
• Dimension queries / sec – high number means heavy trips to the storage engine
• Measure group queries / sec – high number means heavy trips to the storage engine
• Available Mbytes - Available MBytes is the amount of physical memory available to processes running on the computer
• Page Faults / sec – high page faults / sec could indicate inadequate memory.  Counter has both hard and soft page faults.  Soft page faults are okay.  Hard page faults require disk access and can hinder performance.
• % Processor Time - > 80 % constant value indicates inadequate processor power
• Avg. Disk Queue Length – disk queues occur when the disk is busy reading or writing information.  High disk queue lengths indicate inadequate disk performance.  Rule of thumb is Avg. Disk Queue Length < 3 for an extended period. 

As a golden rule none of these counters can be assesed further unless you keep up with a baseline such as during busy times & less traffic hours on the database.