In my last post, I introduced BI Sentry’s latest round of enhancements for monitoring SSAS Tabular mode. In this post, I’ll dig a little deeper into what you can learn about SSAS in Tabular mode from the enhancements to the BI Sentry Dashboard.
To start, let’s cover some basic behaviors of VertiPaq as seen in the xVelocity in-memory analytics engine found in SSAS Tabular.
It’s important to understand that there are two supported paging policies for VertiPaq memory in SSAS. Mode 0, or “Locked”, disables paging. If there isn’t enough memory for the VertiPaq engine in this mode, processing will fail with an out-of-memory error.
Mode 1, or “Paged”, allows paging of VertiPaq memory by Windows to the operating system page file. This is the current default setting in Analysis Services. With this setting, you are less likely to see out-of-memory errors, as the server will attempt to page to disk if needed. However, you still may get errors if you don’t allow enough memory for the process itself or the data dictionaries, which still must be locked in memory.
The above behaviors are typically impacted by the VertiPaq Memory Limit, which defaults to 60 percent of total system memory. All of this is detailed further in the Microsoft Docs.
So, by now you might be asking the question, “This is great, Steve, but how do I see all this in BI Sentry?”
I’m glad you asked.
In the above screen shot, notice an additional memory limit line appears if you are monitoring a Tabular instance. It’s the yellow line with the “V” indicator. If you mouse over, the tool tip provides the actual limit in MB, and the Paging Policy, either “Locked” or “Paged”. You can also see two new counters appear as subsets of the memory indicated, “VertiPaq paged”, and “VertiPaq nonpaged”.
If your paging policy is locked, the behavior is pretty straight forward. With a locked paging policy, you will only get nonpaged memory. Once it hits the VertiPaq Memory Limit, you’ll start getting those errors I mentioned earlier.
But if you are using the default paged mode, things get a little trickier. First, it’s important to understand that VertiPaq paged memory is memory that can be paged. It does not mean it has been paged. Unfortunately, there is no direct indicator that tells you exactly what and how much of any of this is paged to disk.
Furthermore, simply exceeding the VertiPaq memory limit doesn’t mean that paging is occurring. As it was explained to me by some experts at Microsoft, the cleaner process in SSAS pretends like the VertiPaq allocations don’t exist in this mode. Essentially, the SSAS memory cleaner starts cleaning around the point equaling the Low Memory Limit + the VertiPaq Memory limit. It will explicitly tell Windows to page out some of the VertiPaq memory at that point.
You may now be asking yourself, “So Steve, am I out of luck? Is there no way to easily know if paging in an issue on my server?”
I’m glad you asked.
This is exactly where a product like BI Sentry, providing that single pane of glass view of your pertinent SSAS and Windows level metrics, makes it easy to answer these types of questions. At the time I ran a large query, I can see a spike in SSAS memory utilization. At the same time, I can see a correlated spike in Windows page writes, SSAS memory shrunk, and physical disk writes. This provides a pretty strong indicator of memory pressure in SSAS leading to paging of objects in memory to disk.
From here I have a couple options. If this is likely to be a common occurrence on this server, I’ll want to give this instance more memory. I can see in the System Memory chart on the left that I have a couple GB typically free, so I may be able to raise my SSAS memory limits, but I don’t want to leave too little to Windows and/or other processes. I can also see from the multiple colored tiers to that System Memory chart that I have other monitored instances sharing these resources. This may be a good candidate instance to move to its own server to avoid future memory contention if I can’t add more memory to this system.
Remember, a good rule of thumb when estimating memory requirements for SSAS Tabular is at least two to three times the size of your data model in memory to allow for processing and other query related overhead.
Now that you’re an expert using BI Sentry’s dashboard to diagnose memory pressure in SSAS Tabular mode, stay tuned for my next post where I’ll show you how to get the most out of another feature set of the product.
And if you haven’t tried BI Sentry before, give it a try and download your own free evaluation copy.
