Performance Counters for SSAS 2008

Vidas Matelis posted an overview of the SSAS performance counters at ssas-info.com. You can find the complete list here:

http://www.ssas-info.com/analysis-services-articles/58-ssas-2008/1113-analysis-services-2008-performance-counters

 

As with other categories of performance counters you can easily get lost with the "countless counters".

The Analysis Services Performance Guide names these counters to start your performance analysis with:

 

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 pool

 

I completely left out the counters for Caches, Datamining, Locks, Indexes and Proactive Caching here but these are important too and after investigating the above counters you will want to look at the more detailed counters as well. Just check Vidas’ blog post for the complete list.

Advertisements
This entry was posted in Allgemein. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s