I had to support and fix the SSAS memory utilization issues, I was finding it difficult to understand the difference in memory utilization by Multidimensional and Tabular cubes.
What is the best way to learn anything you don’t know? Learning from its expert. And when it comes to experts in SQL Server I always go to SQLBits and PASS session recordings. This time it was the session from Shabnam Watson named “Build a Performance Monitoring Toolset for SSAS Using Power BI“.
Below are my notes from the session, its being a ~1hour session this is going to be a qick recap material for me.
- SSAS service run as msmdsrv.exe
- The SSAS query processing in case of MD model
- Below are the SSAS memory settings involved in MD
- This is how the limits come into picture:
- Whenever the query is hit, it gains some income and, whenever cleaner thread comes, the query has to pay a specific amount to remain in the memory.
- Till Lowmemory limit, the price to stay in the memory is 2, but as soon as it goes beyond LML the price increase.
- Once the memory utilization reaches Totalmemorylimit the price to stay in memory becomes 1000
- If somehow the cleaner thread is not able to work and the memory increase beyond Hardmemorylimit, its then all the connections are closed. SSAS aggressively closes everything.
To summarize it works on the principal of least recently used + the frequency of it being used.
- The SSAS Tabular model Query processing
The difference in Tabular and MD is that the Storage engine part of the query is also in the memory in case of Tabular. When the size of Tabular cube becomes more than what can retain in the memory it is paged out into the pagefile.sys
The memory setting involved in Tabular.
HardMemorylimit when set 0 is (TotalServermemory (physical) – Totalmemorylimit) /2
If the number is set more than 100 for any of these setting then its in bytes otherwise it’s percentage. So 90 is 90% but 101 is 101 bytes.
Cleaner memory only sees the memory as VertiPaqMemoryLimit + Any other process. So in case of 100 GB box with 50 GB pagefile the Cleaner will wake up only when it reaches the limit.
- The Perfmon counters for SSAS:
These perfmon counters are:
Current User sessions
Cleaner Current Price
Cleaner Memory shrunk KB/sec
Memory Limit Hard KB
Memory Limit High KB
Memory Limit Low KB
Memory Limit VertiPaq KB
Memory Usage KB
VertiPaq Nonpaged KB
VertiPaq Paged KB
SSAS: Storage Engine Query-
% Processor Time
Creating Process ID
Working Set – Private
% Processor Time
- The DMV’s that make most sense for performance capture