Analysis Services Performance Guide

MSOLAP: Processing

• Rows read/sec

MSOLAP: Proc Aggregations

• Temp File Bytes Written/sec
• Rows created/Sec
• Current Partitions

MSOLAP: Threads

• Processing pool idle threads (I sometimes find "query pool idle threads" more significant, maybe you want to monitor both)
• Processing pool job queue length (I sometimes find "query pool job queue length" more significant, maybe you want to monitor both)
• Processing pool busy threads (I sometimes find "query pool busy threads" more significant, maybe you want to monitor both)

SQL-Server: Memory Manager

• Total Server Memory
• Target Server Memory

Process

• Virtual Bytes – msmdsrv.exe
• Working Set – msmdsrv.exe
• Private Bytes – msmdsrv.exe
• % Processor Time – msmdsrv.exe and sqlservr.exe

Logical Disk:

• Avg. Disk sec/Transfer – All Instances

Processor:

• % Processor Time – Total

System:

• Context Switches / sec

So it should be clear that you need to monitor the server machine comprehensively and not only the Analysis Services Process.

However, I picked some SSAS counters from the list that are a good one to start your exploration when focusing on the SSAS processes.

Storage Engine Query

I think that this is a really interesting category of counters to start with. Here you find information about the number of queries processed per second, the caching rate of the queries, the average time per query etc. There are really a lot of counters. So this category gives you a good overview of the servers' workload. For the beginning you might want to look at the following counters:

Object	Counter	Description
MSAS 2008:Storage Engine Query	Current measure group queries	Current number of measure group queries being actively worked on.
MSAS 2008:Storage Engine Query	Measure group queries/sec	Rate of measure group queries
MSAS 2008:Storage Engine Query	Queries answered/sec	Rate of queries answered.
MSAS 2008:Storage Engine Query	Bytes sent/sec	Rate of bytes sent by server to clients, in response to queries.
MSAS 2008:Storage Engine Query	Queries from cache direct/sec	Rate of queries answered from cache directly.
MSAS 2008:Storage Engine Query	Queries from cache filtered/sec	Rate of queries answered by filtering existing cache entry.
MSAS 2008:Storage Engine Query	Queries from file/sec	Rate of queries answered from files.
MSAS 2008:Storage Engine Query	Avg time/query	Average time per query, in milliseconds. Response time based on queries answered since the last counter measurement.
MSAS 2008:Storage Engine Query	Dimension cache lookups/sec	Rate of dimension cache lookups.
MSAS 2008:Storage Engine Query	Dimension cache hits/sec	Rate of dimension cache hits.
MSAS 2008:Storage Engine Query	Measure group cache lookups/sec	Rate of measure group cache lookups.
MSAS 2008:Storage Engine Query	Measure group cache hits/sec	Rate of measure group cache hits.
MSAS 2008:Storage Engine Query	Aggregation lookups/sec	Rate of aggregation lookups.
MSAS 2008:Storage Engine Query	Aggregation hits/sec	Rate of aggregation hits.

Connections

This category gives information about the number of connections, sessions and request which is also important to understand the workload and to see when bottlenecks occur.

Object	Counter	Description
MSAS 2008:Connection	Current connections	Current number of client connections established.
MSAS 2008:Connection	Requests/sec	Rate of connection requests. These are arrivals.
MSAS 2008:Connection	Current user sessions	Current number of user sessions established.

MDX

There are really a lot of counters about MDX. Just to name a few of them:

Object	Counter	Description
MSAS 2008:MDX	Number of cell-by-cell evaluation nodes	Total number of cell-by-cell evaluation nodes built by MDX execution plans
MSAS 2008:MDX	Number of bulk-mode evaluation nodes	Total number of bulk-mode evaluation nodes built by MDX execution plans
MSAS 2008:MDX	Total cells calculated	Total number of cell properties calculated

Memory

Memory is always important. Here you can also query the amount of memory being allocated by the aggregation cache.

Object	Counter	Description
MSAS 2008:Memory	Memory Usage KB	Memory usage of the server process. Same as perfmon counter for Process\PrivateBytes.
MSAS 2008:Memory	AggCacheKB	Current memory allocated to aggregation cache, in KB.
MSAS 2008:Memory	Quota KB	Current memory quota, in KB. Memory quota is also known as a memory grant or memory reservation.
MSAS 2008:Memory	Quota Blocked	Current number of quota requests that are blocked until other memory quotas are freed.

Aggregations

If your cubes rely on aggregations it might be interesting to know if they can be held in memory or if the are written to a temporary file. So you might also want to look at the following aggregation counters:

Object	Counter	Description
MSAS 2008:Proc Aggregations	Temp File Bytes Writes/sec	Usage of temporary file
MSAS 2008:Proc Aggregations	Current partitions	Current number of partitions being processed.
MSAS 2008:Proc Aggregations	Memory size bytes	Size of current aggregations in memory. This count is an estimate.
MSAS 2008:Proc Aggregations	Temp file bytes written/sec	Rate of writing bytes to a temporary file. Temporary files are written when aggregations exceed memory limits.

Processing

Processing time is also very important when considering performance especially when you're processing your cube regularly over the day while users are also making their queries. Knowing about the processings may also make the exploration of performance issues more easy. If you're just looking at the average query time for instance, you should also check what the server does in the meantime.

Object	Counter	Description
MSAS 2008:Processing	Rows read/sec	Rate of rows read from all relational databases.
MSAS 2008:Processing	Total rows read	Count of rows read from all relational databases.
MSAS 2008:Processing	Rows converted/sec	Rate of rows converted during processing.
MSAS 2008:Processing	Total rows converted	Count of rows converted during processing.
MSAS 2008:Processing	Rows written/sec	Rate of rows written during processing.
MSAS 2008:Processing	Total rows written	Count of rows written during processing.

Threads

Object	Counter	Description
MSAS 2008:Threads	Query pool job queue length	Nonzero values means that there are more queries than query threads. You may increase the number of threads (but only if CPU utilization is not too high because otherwise this would only result in more context switches and degrade performance)
MSAS 2008:Threads	Query pool busy threads	The number of busy threads in the query thread pool
MSAS 2008:Threads	Query pool idle threads	The number of idle threads in the query thread