Hilmar Buchta

SQL Server 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.