SSAS: Performance monitoring: PASS (Shabnam Watson)

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
MD Query Processing
Query processing for Multi-dimensional model
  • Below are the SSAS memory settings involved in MD
Memory Settings
This is the setting which is responsible to make decision in deciding what remains in memory and what not, in MD
Cleaner Thread MD
The cleaner thread working with the memory limits
  • 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
Tabular Query Processing
Query processing for Tabular model

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.

Tabular additional settings
These are extra to the ones used in MD model
Cleaner Thread for 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:
Perfmon counters

These perfmon counters are:
Memory-
Available KBytes
Pages/sec
SSAS:Connection-
Current Connections
Current User sessions
Failures/sec
SSAS:Memory-
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-
Avg time/query
Queries answered/sec
Process-
% Processor Time
Creating Process ID
ID Process
PrivateBytes
Virtual Bytes
Working Set
Working Set – Private
Processor
% Processor Time

  • The DMV’s that make most sense for performance capture

$SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
$SYSTEM.DISCOVER_SESSIONS
$SYSTEM.DISCOVER_TRACES

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s