In this post I am going to deviate from Hadoop and HDInsight to focus on SQL Server Analysis Services Mutli-dimensional and more specifically MDX queries. As a consultant one of the common issues I encounter more so than design is that of performance. Typically, the performance issues SSAS users encounter occur in one of two realms: cube processing and query execution, while this post will focus on the latter we start by establishing a higher level of understanding of what happens when an XMLA command is issued against our cube.
The XMLA Query Lifecycle
- The query is received by an XMLA Listener. The listener hands the query off to a parser.
- The parser, calls the session manager to determine whether the call belongs to an existing session on whether a new session needs to be created. As this point the command context is created and transaction created.
- Next the XMLA hands the query off to the dispatcher to determine the query type and then execute it. There are four primary types of commands or queries:
- DISCOVER – retrieves info about database or specific objects, typically low impact on system resources and returned immediately to the caller
- DDL – common CREATE, ALTER, DELETE commands, each command has its own dispatch execution path that involves locking of the affected objects and its dependents
- PROCESS – jobs that retrieve data from the relational database, process it and then store it in the Analysis Services file structures, these commands are handled by the storage engine
- EXECUTE – MDX queries. Processed by the command parse which determines which objects are needed (creates an Abstract Syntax Tree (AST)) before passing control to the formula engine. The formula engine gathers the data either from cache or from the storage engine, calculates the results and returns the data to the caller.
To keep the discussion high-level, a lot of the hardcore low-level detail has been omitted. The main idea is to understand whether the storage or formula engine is handling your request. If you are interested in getting deeper into the details, check out the Resources section at the bottom of this post.
With a high-level understanding of the internal process for which query requests are handled. let’s turn our attention to troubleshooting problematic queries.
Identifying Slow/Expensive Queries
Typically, your exercises in troubleshooting will start by a specific user complaint. Inevitably some report is running slow or there are complaints that the performance when browsing the cube is unacceptable. If this is the case then you starting point is easy. Otherwise, it may be necessary to start a Trace in the SQL Server Profile to identify the culprit. It will be easier to find the information you need if you store the trace log to a SQL table using the One the trace is start, using the ‘Save to Table’ option.
A quick query against the trace table filtering the data on EventClass 10 and sorting by Duration allows you to find which specific queries are causing the problem as he query is contained in the TextData field.
Isolate the Query
The first step in query testing is to isolate the query to eliminate contention from other users, queries or even cube processing. The query should be completely isolated and will need to be run both against a clean/cold cache as well as against a warmed cache. To clear the cache you can run the XMLA ClearCache command or to be more thorough use the Analysis Services Stored Procedure Project (Find it HERE) ClearAllCaches() method.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ClearCache> <Object> <DatabaseID>Adventure Works DW-Simple</DatabaseID> </Object> </ClearCache> </Batch>
Where is your query hanging out?
After eliminating external causes, its time to figure out where your query is hanging out: the Formula engine or Storage engine. To quickly determine this we will again use the trace data from the SQL Server Profiler session. The Query Subcube event (EventClass 11) tells us how much time was spent in the storage engine to satisfy the query request. Simply subtracting the total query time from the amount of time spent in the storage engine tells us how much time was spent in the formula engine. Ideally, the time spent in the storage engine should be less than 30% of the total query execution time. If it is greater than 30% we want to looking into optimizing the storage engine otherwise the formula engine is where we will focus.
Total Execution Time (Sum(EventClass 10)) = Formula Engine + Storage Engine (Sum(EventClass 11))
Tuning the Storage Engine
- Design Aggregations – designing usable aggregations that can be used by the storage engine. You can isolate calls to aggregations using the Get Data From Aggregation event in the SQL Trace. If you find that your queries are not resolving data from aggregations consider using the Usage Based Optimization to design aggregations based on your cube’s workload.
- Define Partitions – not only must partition be defined they must be defined properly and with a proper slicer. Setting the slicer allows the storage engine to perform partition elimination during look-up. When a query is issued to the subcube, you can see hits against a partition in the SQL Trace results using the Progress Report Begin/End event:
- Improve I/O Subsystem – The most obvious answer when all else fails…buy bigger, faster, meaner disk. A good place to start if you find yourself at this point is the SQL Server 2008 Analysis Services Performance Guide.
Occasionally, you can improve storage engine performance by pre-warming the cache prior to a subquery being requested. Cache warming should be used as a last resort and an effort should be made to ensure that the cache will satisfy a diverse range of queries. Techniques for warming the cache include queries issues by Reporting Services reports, Integration Services packages or the CREATE CACHE command issued an ASCMD batch.
Tuning the Formula Engine
If you determine that your performance issues are occurring in the formula engine (storage engine time < 30% total duration) the tuning exercise will focus on the MDX statement itself. Unfortunately, there is little info to glean from the SQL Trace and typically you will need to decompose the MDX statement into parts to determine the culprit.
While its not practical to provide an exhaustive list of potential bottlenecks some of the more common causes are listed below:
- Subspace/Block vs Cell-by-Cell Computation – queries that are evaluated cell-by-cell will drag on performance, certain functions force cell-by-cell operation. To determine whether a query is being processed in block or cell-by-cell mode you can monitor the Total cells calculated, Number of calculation covers, Total Sonar subcubes MDX performance counters. Beyond that knowing which functions are optimized for block computation as well as features which disable block computation is your best bet. Some of the features that disable block computation are:
- Aliased Set
- Unary Operators
- Late binding in either a function or custom member formula
- StrTo<Member/Set/etc> without Constrained
- IIF function
- Linked Objects
Tuning the formula engine as you can probably see is as much art as it is science. For more in depth detail, check out the resources section for the Identifying and Resolving MDX Bottlenecks white paper.
Whitepaper – SQL Server 2008R2 Performance Guide
Whitepaper – Identifying and Resolving MDX Bottlenecks
Hopefully this post helped you get a jump start on tuning your problem MDX as well as provided some insight into how MDX statements are evaluated to pinpoint your issues. In a later post I will discuss techniques for optimizing the cube processing process.
Till next time,