For years, BI Sentry has been a popular and successful solution for monitoring, diagnosing, and optimizing SQL Server Analysis Services performance. With our real-time and historical performance dashboard, detailed XMLA, MDX, and DMX query analysis, baselining and alerting functionality, and attribute, aggregation, and partition usage totals, we have been providing BI professionals unparalleled insight into Multidimensional SSAS.
While BI Sentry has also supported monitoring SSAS in Tabular mode, we knew there was more we could offer those environments. Tabular has gained in popularity to the point that the default installation option for Analysis Services in SQL Server 2017 is now Tabular mode. So, it is with great pride and excitement that I can announce BI Sentry’s latest enhancements in support of SSAS Tabular.
Updated Performance Dashboard
First, you’ll likely notice some modifications to our performance dashboard when monitoring a Tabular instance. In the past, when monitoring a Tabular instance, there were three charts in the dashboard that only provided data for multidimensional SSAS, and would simply be blank. We’ve reorganized and consolidated some charts to account for that, while at the same time added some new metrics around VertiPaq memory, exclusive to Tabular mode.
In the Mem Usage (MB) chart you’ll see two new memory counters, plus the VertPaq Memory Limit. These metrics are valuable, along with correlation with other metrics on the dashboard, in identifying issues where memory pressure may be the root cause of your performance problems. We also display the VertiPaq paging policy in the tooltip, which is important in properly interpreting these metrics. I’ll provide more details on that in a future post.
Enhanced DAX Query Analysis
Next, and possibly the most requested feature, is our enhanced DAX query analysis. Just as we have provided in-depth information about MDX queries on multidimensional servers, we now provide internal details for DAX, as well as MDX, queries on Tabular. You can now see Storage Engine vs. Formula Engine time per query, internal VertiPaq cache matches and scans, and quickly identify callbacks to the formula engine, allowing you to pinpoint your Tabular query bottlenecks.
Object Memory Usage
The final feature I’d like to point out can be found in the SSAS Usage Totals tab. Because SSAS Tabular’s Storage Engine is entirely in memory, it’s important to have a data model that is as memory efficient as possible. The VertiPaq compression engine can often compress objects up to 10 times their size on disk, but that is dependent on certain factors.
To help understand how your memory is being utilized, we’ve added the Object Memory Usage subtab. Tabular’s in-memory storage of your model is columnar. This view shows you your model’s objects by size, along with row counts and cardinality. This makes it easy, at a glance, to see what objects are using the most memory, as well as what objects may not be compressing well due to high cardinality. Also note, you can expand each row and see that object’s size, and counts over time to get an idea of how things might be trending.
I’ll be following up in future posts with some deeper dives into the benefits of these exciting new features. Hopefully this introduction has piqued your interest. The latest release of BI Sentry with all of this new functionality is available now for download, so give it a try and let me know what you think!
Get a free 15 day trial of SentryOne BI Sentry today if you want to see how to optimize your SQL Server Analysis Services implementation. Visit our News Room if you would like to read the press release “SentryOne Releases Version 18.1 with Enhanced Support of SSAS Tabular.”
