Almost nine years ago I started writing a blog series about SQL Server Analysis Services (SSAS) performance. Our monitoring support for SSAS had been released a couple years prior, and as our lead Solutions Engineer, I found myself answering a lot of the same questions and helping people effectively interpret and act upon all the valuable information that was available for SSAS. A lot of the time I was speaking to people who were fairly experienced SQL Server DBAs but had little to no prior experience with SSAS.
That series was ultimately rolled up into an e-Book that I hope has benefitted many people over the years. The original blog posts and e-Book were written prior to the introduction of Tabular mode in 2012. But a lot of the information provided is still relevant today for both Multidimensional and Tabular mode.
That said, I wanted to provide some updates with more of a focus on Tabular mode, as it has clearly become the new standard for how most organizations deploy SSAS. I recommend referring to that original series for a focus on Multidimensional mode, but much of the information provided in this blog post will be applicable to both modes. I recommend referencing Allen White’s blog post on SQL Server counters to learn more about many of the Windows-level counters that apply across the board, as I won’t speak to them here.
Let’s dive into the 15 SSAS performance counters you should be monitoring.
<!–[if lte IE 8]>
<![endif]–>
Process (msmdsrv)\% Processor Time
This Windows-level counter is specific to SSAS and critical to determine how much of the total CPU utilization is directly related to the SSAS instance on your server. If you are running any other intensive services, such as SQL Server or SQL Server Integration Services (SSIS), you will want to distinguish them as well.
MSAS:Processing\Total Rows Read
With both SSAS modes, by far the most common focus of troubleshooting for the customers I have worked with is related to processing activity. Although there are more counters that show processing activity for Multidimensional mode than Tabular mode, this counter works for both and shows when rows are being read from the relational databases for processing into SSAS. This can be useful for identifying when processing is occurring and how much data is being pulled in from your source databases.
MSAS:Storage Engine Query\Rows sent/sec
Just as it’s important to understand when processing is occurring and how much data is being ingested, this counter gives you visibility into the rate of data being sent by the server to clients in response to submitted queries.
MSAS:Threads\Query pool
MSAS:Threads\Processing pool
MSAS:Threads\Processing pool I/O
These three counters are good indicators of how SSAS is using CPU. The query pool refers to Formula Engine activity in response to query requests. The processing pool refers to Storage Engine processing activity. There is another category for Multidimensional SSAS, processing pool I/O, which relates to non-processing Storage Engine activity. Typically, this is activity in which query requests require the Storage Engine to query the file system directly. In Tabular mode, you should not see any activity in this category.
Each of these categories contain counters for Busy, Idle, Job Rate, and Queue Length. They allow you to see the thread activity for each engine. If you are seeing consistently high queue lengths but not high CPU utilization, you might want to adjust your MaxThreads and/or CoordinatorExecutionMode properties for your SSAS instance where it applies for each mode.
Note that the SSAS Formula Engine is single threaded per request, so increasing the query pool setting might not improve the performance of any one query but doing so might improve the performance in handling multiple simultaneous requests.
MSAS:Memory\Cleaner Memory KB
MSAS:Memory\Cleaner Memory shrinkable KB
MSAS:Memory\Cleaner Memory nonshrinkable KB
These three counters refer to the background cleaner for SSAS. The first counter refers to the amount of memory known to the background cleaner in total. That memory is then divided into shrinkable and non-shrinkable memory. The counter shows what portion of the known memory is subject to purging by the cleaner based on memory limits. The cleaner value is likely to be a bit lower than the total usage value, but it’s important so that you know how much room you have to actually work with when it comes to memory management.
In Tabular mode, memory is further subdivided into the two counters described in the following section.
MSAS:Memory\VertiPaq Paged KB
MSAS:Memory\VertiPaq Nonpaged KB
These counters are a subset of non-shrinkable memory and show how much of your instance’s memory is being used specifically by your compressed databases in the VertiPaq in-memory storage engine. Do not be fooled by the name VertiPaq Paged—it does not actually mean paged memory, but memory that can be paged.
Memory Limit Counters
MSAS:Memory\Memory Limit Low KB
MSAS:Memory\Memory Limit High KB
MSAS:Memory\Memory Limit Hard KB
For Multidimensional mode, the behavior with these limits is straightforward. As total cleaner memory—the sum of shrinkable and non-shrinkable memory—exceeds the Low limit, the cleaner process starts to look for objects in-memory that it can eject. Once you exceed the High limit, also known as the Total limit, the cleaner process gets much more aggressive. Finally, at the Hard limit, processes might be killed and you might receive out of memory errors.
A great explanation of these properties and counters for Multidimensional mode, along with real-world examples of their use, is covered in Greg Gonzalez’ blog post, “Analysis Services Memory Limits.”
In Tabluar mode, however, there is one additional memory limit to monitor.
MSAS:Memory\Memory Limit VertiPaq KB
In most cases, VertiPaq memory allocations before this limit are essentially ignored by the cleaner process. This makes identifying memory pressure and potential paging more difficult to diagnose. To help shed more light on these behaviors and how to identify them with SQL Sentry, view my blog post on analyzing VertiPaq memory usage.
Monitoring SSAS Performance Counters with SQL Sentry
All the counters I have covered in this blog post are available through PerfMon and other standard collection methods. However, the most efficient way to collect and retain these counters, while easily correlating them with other events such as individual queries or processing commands, is with SQL Sentry. I highly recommend you request a demo so that you can see just how comprehensive the data gathering is and how the tool can help you establish baselines. SQL Sentry can help you to know when attention is required and where to focus your attention when it is required.
