SentryOne comes preconfigured with well over a hundred alerting conditions for failures, offline servers, long running queries, and more. You may also be aware of SentryOne’s Advisory Conditions. This advanced alerting and response system allows you to truly customize the product to respond to any condition with complex logic based on performance counter thresholds, query results, and more. Lori Edwards recently blogged about Creating Advisory Conditions in SentryOne.
To get you started, SentryOne provides an Advisory Conditions Pack that can be downloaded and deployed directly from your Client, as well as downloaded from the website. Soon, we’ll be adding conditions for SSAS to this pack for use in BI Sentry.
In this post, I’ll walk through these conditions, provide links so you can download and import them, as well as introduce a couple more specific to Tabular mode. I don’t want to play spoiler, but these will give you a taste of some enhancements to our SSAS performance monitoring that will be coming soon that are specific to Tabular mode, the xVelocity (often referred to as VertiPaq) storage engine, and DAX analysis.
SSAS Formula Engine Query Pool Job Queuing
This condition is useful for both Multidimensional and Tabular mode SSAS.
SSAS Formula Engine Query Pool Job Queuing.condition
The query pool refers to Formula Engine activity for queries. If you are seeing consistently high queue lengths, but not high CPU utilization, you may want to adjust your MaxThreads and/or CoordinatorExecutionMode properties for your SSAS instance.
Remember that the FE is single threaded, so increasing the query pool setting may not improve performance of any one query, but may improve the performance in handling multiple simultaneous requests.
This condition is useful to Multidimensional modes on SQL Server 2012 and above.
SSAS Storage Engine IO Job Queuing.condition
The IO Process thread pool separates reads from other activities. If the I/O job queue length is consistently above 0, you may be experiencing an IO bottleneck.
See the Analysis Services MOLAP Guide for SQL Server 2012 and 2014 for more information, including optimizing for NUMA.
This condition is useful for both Multidimensional and Tabular modes of SSAS.
SSAS Storage Engine Processing Pool Job Queuing.condition
Depending on your version of SSAS, queuing of jobs in this pool can be related to all Storage Engine activity (SSAS 2005 to 2008R2), or strictly processing activity in SSAS 2012 and above.
See the appropriate Microsoft SSAS Performance Guide for more details on optimizing this activity for your version of SSAS.
This condition is useful for both Multidimensional and Tabular modes of SSAS.
SSAS Low Memory Limit Exceeded.condition
SSAS uses memory limit settings to determine how it allocates and manages its internal memory. Memory\LowMemoryLimit defaults to 65% of the total available physical memory on the machine (75% on AS2005), and Memory\TotalMemoryLimit (also sometimes called the High Memory Limit) defaults to 80%. This is the total amount of memory that the SSAS process itself (msmdsrv.exe) can consume.
Once memory usage hits the Low limit, memory cleaner threads will kick in and start moving data out of memory in a relatively non-aggressive fashion. If memory hits the Total limit, the cleaner goes into crisis mode… it spawns additional threads and gets much more aggressive about memory cleanup, and this can dramatically impact performance.
See Also: Analysis Services Memory Limits
This condition is useful for both Multidimensional and Tabular modes of SSAS.
SSAS High Memory Limit Exceeded.condition
SSAS uses memory limit settings to determine how it allocates and manages its internal memory. Memory\LowMemoryLimit defaults to 65% of the total available physical memory on the machine (75% on AS2005), and Memory\TotalMemoryLimit (also sometimes called the High Memory Limit) defaults to 80%. This is the total amount of memory that the SSAS process itself (msmdsrv.exe) can consume.
Once memory usage hits the Low limit, memory cleaner threads will kick in and start moving data out of memory in a relatively non-aggressive fashion. If memory hits the Total/High limit, the cleaner goes into crisis mode… it spawns additional threads and gets much more aggressive about memory cleanup, and this can dramatically impact performance.
See Also: Analysis Services Memory Limits
This condition is useful for Multidimensional models of SSAS.
SSAS Sustained Cache Evictions.condition
If Cache Evictions/sec or Memory : KB shrunk/sec are consistently above 0, you likely have memory pressure on the SSAS instance. This is often seen when SSAS memory usage exceeds configured limits.
SSAS Sustained Connection Failures
This condition is useful for both Multidimensional and Tabular models of SSAS.
SSAS Sustained Connection Failures.condition
A sustained value above zero indicates an inability for users to successfully connect to SSAS. This could be related to overburdened resources on the server.
What's new?
You note that many of these Advisory Conditions can alert you to issues with both Multidimensional and well as Tabular modes of SSAS. That said, while BI Sentry provides a wealth of information around Multidimensional instances in the Dashboard and Top Commands tab, there are a couple areas specific to Tabular mode that would also be helpful. Well, your wait is almost over. Additional visibility into the xVelocity in-memory analytics engine, also known as the VertiPaq storage engine, is coming soon to the BI Sentry dashboard. Along with that, will be more in-depth details around DAX analysis in the Top Commands tab.
But, even before those features are released, did you know you can be alerted today by BI Sentry on issues specific to the VertiPaq storage engine? Below I’ll provide a couple new Advisory Conditions you can set up today and start getting SSAS Tabular specific alerts.
Now, because these counters aren’t collected by default by SentryOne in the current version, there are a couple extra steps to take. While our native alerts can be version agnostic with regards to SSAS 2012, 2014, etc., for now I have created versions of these conditions for each version of SSAS. You should be able to import them and get set up pretty quickly, you’ll just need to ensure you’ve imported the version(s) applicable to SSAS in your environment for now. Once the new Tabular support is released for BI Sentry, I’ll be sure to include these Advisory Conditions in the universal importable format.
This condition is specific to SSAS Tabular mode only.
VertiPaq Nonpaged Memory Approaching VertiPaq Limit (SSAS 2012).condition
VertiPaq Nonpaged Memory Approaching VertiPaq Limit (SSAS 2014).condition
VertiPaq Nonpaged Memory Approaching VertiPaq Limit (SSAS 2016).condition
Until our enhanced support for SSAS tabular is released, I’ll have to provide a condition for each version of SSAS.
The behavior of the storage engine once VertiPaq Nonpaged Memory reaches the VertiPaq Memory Limit depends on the VertiPaq paging policy that is configured in the instance's SSAS properties. The default paging policy of 1, will allow paging to the Windows page file.
This means that if the required memory to store what is needed in the VertiPaq engine exceeds the configured limit, paging will occur to the Windows page file. However, if the paging policy is changed to 0, this disables paging. If the required memory hits the limit with this configuration, queries to the instance may fail and out-of-memory errors will be experienced.
Be aware, a known bug in SSAS can prevent these counters from returning values on SQL Server 2012 SP1.
The final Advisory Condition for tabular I wanted to share is:
This condition is specific to SSAS Tabular mode only.
Total VertiPaq Memory Exceeds VertiPaq Limit (SSAS 2012).condition
Total VertiPaq Memory Exceeds VertiPaq Limit (SSAS 2014).condition
Total VertiPaq Memory Exceeds VertiPaq Limit (SSAS 2016).condition
With the default paging policy for SSAS Tabular set to 1, paging is allowed. This means that if the memory required for the VertiPaq storage engine exceeds the configured VertiPaq memory limit, data can be paged to the Windows page file. Similarly, a setting of 2 allows utilizing a memory-mapped file. While these files may take advantage of operating system file cache, it is still less efficient than storing the data as nonpaged VertiPaq memory. Increasing the VertiPaq Memory Limit setting in your Analysis Server properties is recommended if this issue persists.
To determine a proper limit, detect the largest consumers of memory, then allow another 2 - 3x for processing overhead, etc. Be sure to consider the other Low and Total SSAS memory limit settings, which apply to both Tabular and Multidimentional modes.
Again, be aware, a known bug in SSAS can prevent these counters from returning values on SQL Server 2012 SP1.
What's Next?
Hopefully the information provided here will enhance your monitoring and troubleshooting experience for SSAS with BI Sentry. In the near future, we will be enhancing our monitoring of SSAS Tabular to include more visibility into the xVelocity in-memory engine, also known as VertiPaq, and DAX query analysis. We’ll also add the above conditions to the downloadable conditions pack to simplify and streamline their implementation into any SentryOne installation.
In the meantime, feel free to contact me or SentryOne support with any questions, and we’ll be happy to help you get things going.
Facebook
Linkedin
Twitter
Comments