SQL Server has management studio reports, performance dashboard reports to identify top cpu, duration, io queries which is possible due to the DMVs viz sys.dm_exec_query_stats which is available to track the queries.
However with SSAS, we do not have any DMV similar to sys.dm_exec_query_stats which tracks and captures the statistics of the queries fired against SSAS cube. In this blog post, I would discuss on how we can achieve something similar with SSAS.
While doing performance tuning of the cube, it is very important to identify top mdx queries which are running slow or consuming high amount of resources on the server.To achieve this, we need to use profiler to capture the MDX queries.
Note: Profiler is a resource intensive tool and should be run only on the server only when there is no cpu,memory or io pressure on the server.
Using Profiler, we need to capture following events (Make sure all columns is checked)
Entire Query Processing Events
Once the above events are configured, the profiler needs to be run on the server for around 20-30 mins during peak activity on the server to ensure most of the hard hitting mdx queries are captured.
Once the Mdx queries are captured using profiler, the trace file(s) needs to be moved to another non-production workstation which has SQL Server Database Engine installed. Like SQL Server, we can load the analysis service profiler trace into a table in SQL database.
You can open the profiler trace and click as File –> Save as –> Trace Table, specify the sql instance name, database name, table name.
Depending upon the amount of the data captured in profiler trace, the time taken to load the trace in table may vary accordingly. You can monitor the status by looking at the bottom left corner of the profiler trace which shows the status as Saving 20%
Once the data is saved in the trace table, you can use the following query to identify TOP 10 slow duration queries running in the environment.
Select TOP 10 TextData AS [Query],SPID,Duration FROM UserLoad -- UserLoad is Table in which profiler trace is loaded where EventClass=10 -- EventClass 10 represents Query End event Order by Duration DESC
In order to tune the mdx queries, it is important to identify where the query has spent most of its time, is it the storage engine or formula engine. The tuning approach will be decided once it is clear where most of the query processing time is spent.
Hence to identify this we can use the following query
Select TOP 10 TextData AS [Query],SPID,Duration, b.[Storage Engine],Duration-b.[Storage Engine] AS [Formula Engine] FROM UserLoad a cross apply ( SELECT SUM(duration) AS [Storage Engine] FROM UserLoad where spid = a.spid and Rownumber < a.Rownumber and EventClass=11 -- Eventclass 11 represents Query Subcube events ) b where EventClass=10 Order by Duration DESC
From the above query, we can identify where the most of the time is spent by the query.
If the query spends most of its time in Storage Engine, we can use aggregations, cache warmers or memory tuning approach to improve the response time of the query.
If the query spends most of its time in Formula Engine, we need to check for cell by cell evaluation mode, cell security or empty cell calculation all of which can cause query to spent more time in formula engine.
Using the above approach, you can perform MDX query tuning in SSAS.
Hope this helps !!!
Premier Field Engineer