If you are familiar with SQL Sentry and SentryOne’s community experts, you might have seen many of our resources focused on tuning and troubleshooting SQL queries. But did you also know that SQL Sentry supports SQL Server Analysis Services (SSAS) performance monitoring?
We have supported in-depth, comprehensive monitoring of SSAS since 2009. In response, I wrote a series of blog posts (turned eBook) on monitoring and optimizing SSAS performance. That was before the introduction of Tabular mode, but much of the information in that eBook is relevant to both Multidimensional and Tabular SSAS. (Note that the eBook includes a section that discusses optimizing MDX queries. As MDX can be executed on Tabular instances, I recommend referring to that topic if you have MDX running in your environment.)
In this blog post, I want to focus on DAX and queries run in Tabular mode. That said, when talking about queries running on Multidimensional or Tabular SSAS, the first step in troubleshooting is the same.
When did it run?
Assuming you have eliminated general resource bottlenecks and have identified a particular poorly performing query, one of the first questions you might want to ask is, “Does this query consistently perform poorly or only at certain times?”
From the Top Commands tab for my SSAS instance in SQL Sentry, I can right click the query in question and select Jump To > Runtime Stats. Doing so displays a runtime history chart like the one shown below. From there, I can see the recent history of the selected query and easily spot any trends in the query’s runtime.
In the above example, I can see one particular outlier. This query seems to average about 5.5 seconds but took almost 9 seconds for the selected execution.
Another right-click and I can Jump To > This Instance in the Event Calendar view and easily see what else was running on the server at the same time. I am just looking at events for this SSAS instance, but I could include events from other instances (e.g., SQL Server, SSAS, SSIS) that might be sharing the same resources or running on other servers. This can be especially useful for eliminating issues if physical resources are over-provisioned.
In this case, I can see the query was running concurrently, not just with another query, but while a failed attempt at processing was occurring, too. The fact that these ran concurrently could easily explain why the query ran longer than normal and, possibly, why the processing command failed. Note I can highlight events in orange on the calendar automatically based on concurrency with other events in the view.
In this case, I might just write off the issue as a random occurrence, or I might want to look into the scheduling of my processing activity or other events to eliminate conflicts. When investigating a long-running SSAS query, determining when processing last occurred is extremely important, not only because of resource bottlenecks but also because caches might be cleared or more data added that could significantly impact the query runtime.
Where is SSAS spending most of its time?
Once you have eliminated any issues due to the timing of the execution, you need to dive deeper into the query itself.
The Formula Engine
Under the covers, there are two primary “engines” within SSAS—the Formula Engine (FE) and the Storage Engine (SE). The FE accepts requests and parses the query to process. It will send requests to the SE when data is needed. It then performs calculations on the retrieved data to provide a final result set.
An interesting thing about the FE is that it is single threaded. If you are watching the CPUs of your multi-core server with SSAS while a request is being handled and you only see one core spike, it’s probably because the FE was doing the work at that time.
The Storage Engine
The SE is multi-threaded and is where the biggest difference is found between Multidimensional and Tabular SSAS. In Multidimensional mode, the SE handles retrieving data from the file system when it is not found in cache. In Tabular, the entire SE is in-memory using VertiPaq technology.
You can see right away how this could lead to different query optimization strategies! In Multidimensional mode, you want to minimize work done by the SE, especially non-cached activity, as that often involves disk I/O. Conversely, it is common to want to push more work to the SE in Tabular to take advantage of the fact that it is both multi-threaded and working with data compressed in memory. That can mean many multiples better performance in the Tabular SE over Multidimensional.
Without SQL Sentry, answering the question of where SSAS is spending most of it’s time could mean manually running the queries while sifting through traces, extended events, or using tools such as DAX Studio.
In SQL Sentry, it is automatically collected for all high-impact queries and provided out of the box as you can see in the screenshot below. It is shown both as a raw count, in milliseconds, and as a percentage of the query’s execution runtime.
From here, whatever engine is involved in more than 50% of the query execution time is the bottleneck.
Storage Engine Bottlenecks
If the bottleneck is in the SE, the next step is to dig into the query’s individual VertiPaq subqueries. These VertiPaq Scan events give you visibility into how the FE is querying the VertiPaq in-memory SE under the covers.
From here, there are multiple possibilities—entire books have been written on the topic—but I want to point out where you can find the information to help you determine where to go next.
Functions
Often, with any query tuning, it is simply a matter of choice of function, along with the order of operations. With SQL, MDX, or DAX, there is more than one way to write a query and get the same result set. That said, each of those queries might perform dramatically different from one another to provide those same results. For example, a common DAX recommendation is to use the CALCULATE() function instead of FILTER(). Those new to DAX might often prefer the FILTER() function because it’s simpler to use, but CALCULATE() can often provide the same results with better performance. More about that to come.
Callbacks
One easy issue to spot in the SE is callbacks. Generally speaking, pushing simple calculations to the SE in Tabular SSAS is a good thing since it’s optimized in memory. That said, some calculations cannot be pushed to the SE and might require a callback to the FE on a row-by-row basis.
These are commonly seen with IF and LASTDATE functions. This will cause the SE to send a callback to the FE. If you are seeing callbacks involved with one of your longest VertiPaq scans for your query, look for options to change the order of operations to eliminate them in the query plan.
You can spot callbacks in the Top Commands tab of SQL Sentry at the query level in the Callbacks column, which will provide a count for the entire DAX or MDX statement. We will also highlight them in the Command Text of the individual VertiPaq scans where they occur.
Materialization
A final area to investigate for SE bottlenecks in Tabular SSAS is materialization. When multiple results need to be joined from VertiPaq storage so that the FE can perform its calculations, those temporary result sets are stored non-compressed in memory. On average, a single Tabular database experiences about 10x compression in memory compared to its non-compressed size on disk. That means, under the “right” circumstances, a single query could require many multiples of the entire database’s memory consumption simply in materializing the individual result sets returned by the SE to the FE to calculate and assemble your final query results.
Issues like this can cause performance issues due to memory pressure and paging. At worse, they could cause errors and cancelled requests. However, this can be easy to spot in the SQL Sentry Performance Analysis Dashboard.
In this case, look for opportunities to rewrite your query to reduce or eliminate materialization. Note this is where you might find a trade-off with callbacks, as callbacks avoid spooling. It all might depend on what resource is more limited for you, system memory or time.
Formula Engine Bottlenecks
As I mentioned earlier, a lot can come from your choice of function and the order of operations. The CALCULATE() versus FILTER() approach is one common example. Remember, SSAS Tabular is optimized by the In-Memory VertiPaq SE. Whenever possible, look for opportunities to push work to the SE as you can when using CALCULATE().
Another opportunity for optimization of FE bottlenecks is with calculated columns. In Multidimensional SSAS, we can create Aggregations, which are collections of pre-calculated values stored to disk. This saves query time by storing these values likely to be required for queries ahead of time. We basically pay for query time performance with storage and processing overhead, not unlike the benefit of indexes in relational SQL Server.
In Tabular mode, there are no Aggregations because the entire database is already stored, compressed, in memory. However, you can get a similar result by creating calculated columns in your Tabular database. Similar to Aggregations in Multidimensional SSAS, you are paying for query-time performance with processing time and overhead. This is often well worth the effort as query performance is typically at more of a premium than processing performance. With all strategies of this type, the trick is to find the optimal level of trade-off to maximize the return.
Identifying and Optimizing Your DAX Queries with SQL Sentry
Hopefully, you have found this information useful in tracking down and optimizing your DAX queries in SQL Sentry. If you are interested in really diving in with more examples, I recommend the Microsoft whitepaper, “Performance Tuning of Tabular Models.” Although it was originally written when Tabular was introduced for SSAS in 2012, it still has plenty of relevant information today.
In my next blog post, I will discuss another way you can use SQL Sentry to optimize your SSAS Tabular performance through optimization of your data model.
In the meantime, if you haven’t already, download a free trial of SQL Sentry and see how we can help you optimize your SSAS performance.
Additional Resources
