Performance Counters (SSAS)

Article
05/20/2012

Using Performance Monitor, you can monitor the performance of a Microsoft SQL Server Analysis Services (SSAS) instance by using performance counters.

Performance Monitor is a Microsoft Management Control (MMC) snap-in that tracks resource usage. You can start this MMC snap-in by typing in PerfMon at the command prompt or from Control Panel by clicking Administrative Tools, then Performance Monitor. Performance Monitor lets you track server and process performance and activity by using predefined objects and counters, and monitor events by using user-defined counters. Performance Monitor collects counts instead of data about the events, for example, memory usage, number of active transactions, or CPU activity. You can also set thresholds on specific counters to generate alerts that notify operators.

Performance Monitor can monitor remote and local instances of Analysis Services or SQL Server. For more information, see Using Performance Monitor.

To see the description of any counter that can be used with SQL Server Analysis Services, in Performance Monitor, open the Add Counters dialog box, select a performance object, and then click Show Description. The most important counters are CPU usage, memory usage, disk IO rate. It is recommended you start with these important counters, and move to more detailed counters when you have a better idea of what else could be improved through monitoring. For more information about which counters to include, see the SQL Server 2008 R2 Operations Guide.

Counters are grouped so you can more easily find related counters.

Counters by Groups

Group	Description
Cache	Statistics related to the Analysis Services aggregation cache.
Connection	Statistics related to Microsoft Analysis Services connections.
Data Mining Model Processing	Statistics related to processing data mining models processing.
Data Mining Prediction	Statistics related to creating predictions from data mining models.
Locks	Statistics related to Microsoft Analysis Services internal server locks.
MDX	Statistics related to Microsoft Analysis Services MDX calculations.
Memory	Statistics related to Microsoft Analysis Services internal server memory.
Proactive Caching	Statistics related to Microsoft Analysis Services Proactive Caching.
Processing Aggregations	Statistics related to processing of aggregations in MOLAP data files.
Processing Indexes	Statistics related to processing of indexes for MOLAP data files.
Processing	Statistics related to processing of data.
Storage Engine Query	Statistics related to Microsoft Analysis Services storage engine queries.
Threads	Statistics related to Microsoft Analysis Services threads.

Cache

Statistics related to the Microsoft Analysis Services aggregation cache.

Counter	Description
Current KB	Current memory used by the aggregation cache, in KB.
KB added/sec	Rate of memory added to the cache, KB/sec.
Current entries	Current number of cache entries.
Inserts/sec	Rate of insertions into the cache. The rate is tracked per partition per cube per database.
Evictions/sec	Rate of evictions from the cache. This is per partition per cube per database. Evictions are typically due to background cleaner.
Total inserts	Insertions into the cache. The rate is tracked per partition per cube per database.
Total evictions	Evictions from the cache. Evictions are tracked per partition per cube per database. Evictions are typically due to background cleaner.
Direct hits/sec	Rate of cache direct hits. A cache hit indicates that queries were answered from an existing cache entry.
Misses/sec	Rate of cache misses.
Lookups/sec	Rate of cache lookups.
Total direct hits	Total count of direct cache hits. A direct cache hit indicates that queries were answered from existing cache entries.
Total misses	Total count of cache misses.
Total lookups	Total number of lookups into the cache.
Direct hit ratio	Ratio of cache direct hits to cache lookups, for the period between counter values.
Total filtered iterator cache hits	Total number of cache hits that returned an indexed iterator over the filtered results.
Total filtered iterator cache misses	Total number of cache hits that were unable to build an indexed iterator over the filtered results and had to build a new cache with the filtered results.

Connection

Statistics related to Microsoft Analysis Services connections.

Counter	Description
Current connections	Current number of client connections established.
Requests/sec	Rate of connection requests. These are arrivals.
Total requests	Total connection requests. These are arrivals.
Successes/sec	Rate of successful connection completions.
Total successes	Total successful connections.
Failures/sec	Rate of connection failures.
Total failures	Total failed connection attempts.
Current user sessions	Current number of user sessions established.

Data Mining Model Processing

Statistics related to Microsoft Analysis Services Data Mining model processing.

Counter	Description
Cases/sec	Rate at which cases are processed.
Current models processing	Current number of models being processed.

Data Mining Prediction

Statistics related to Microsoft Analysis Services Data Mining prediction.

Counter	Description
Concurrent DM queries	Current number of data mining queries being actively worked on.
Predictions/sec	Number of predictions generated in data mining queries
Rows/sec	Number of rows handled during a data mining prediction query.
Queries/sec	Number of data mining queries that were handled.
Total Queries	Total data mining queries received by the server.
Total Rows	Total rows returned by data mining queries.
Total Predictions	Total data mining prediction queries received by the server.

Locks

Statistics related to Microsoft Analysis Services internal server locks.

Counter	Description
Current latch waits	Current number of threads waiting for a latch. These are latch requests that could not be given immediate grants and are in a wait state.
Latch waits/sec	Rate of latch requests that could not be granted immediately and had to wait before being granted.
Current locks	Current number of locked objects.
Current lock waits	Current number of clients waiting for a lock.
Lock requests/sec	Number of lock requests per second.
Lock grants/sec	Number of lock grants per second.
Lock waits/sec	Number of lock waits per second. These are lock requests that could not be given immediate lock grants and were put in a wait state.
Lock denials/sec	Rate of lock denials.
Unlock requests/sec	Number of unlock requests per second.
Total deadlocks detected	Total number of deadlocks detected.

MDX

Statistics related to Microsoft Analysis Services MDX Calculations.

Counter	Description
Number of calculation covers	Total number of evaluation nodes built by MDX execution plans, including active and cached.
Current number of evaluation nodes	Current (approximate) number of evaluation nodes built by MDX execution plans, including active and cached.
Number of Storage Engine evaluation nodes	Total number of Storage Engine evaluation nodes built by MDX execution plans.
Number of cell-by-cell evaluation nodes	Total number of cell-by-cell evaluation nodes built by MDX execution plans.
Number of bulk-mode evaluation nodes	Total number of bulk-mode evaluation nodes built by MDX execution plans.
Number of evaluation nodes that covered a single cell	Total number of evaluation nodes built by MDX execution plans that covered only one cell.
Number of evaluation nodes with calculations at the same granularity	Total number of evaluation nodes built by MDX execution plans for which the calculations were at the same granularity as the evaluation node.
Current number of cached evaluation nodes	Current (approximate) number of cached evaluation nodes built by MDX execution plans.
Number of cached Storage Engine evaluation nodes	Total number of cached Storage Engine evaluation nodes built by MDX execution plans
Number of cached bulk-mode evaluation nodes	Total number of cached bulk-mode evaluation nodes built by MDX execution plans.
Number of cached 'other' evaluation nodes	Total number of cached evaluation nodes built by MDX execution plans that are neither Storage Engine nor Bulk-mode.
Number of evictions of evaluation nodes	Total number of cache evictions of evaluation nodes due to collisions.
Number of hash index hits in the cache of evaluation nodes	Total number of hits in the cache of evaluation nodes that were satisfied by the hash index.
Number of cell-by-cell hits in the cache of evaluation nodes	Total number of cell-by-cell hits in the cache of evaluation nodes.
Number of cell-by-cell misses in the cache of evaluation nodes	Total number of cell-by-cell misses in the cache of evaluation nodes.
Number of subcube hits in the cache of evaluation nodes	Total number of subcube hits in the cache of evaluation nodes.
Number of subcube misses in the cache of evaluation nodes	Total number of subcube misses in the cache of evaluation nodes.
Total Sonar subcubes	Total number of subcubes that the query optimizer generated.
Total cells calculated	Total number of cell properties calculated.
Total recomputes	Total number of cells recomputed due to error.
Total flat cache inserts	Total number of cell values inserted into flat calculation cache.
Total calculation cache registered	Total number of calculation caches registered.
Total NON EMPTY	Total number of times a NON EMPTY algorithm was used.
Total NON EMPTY unoptimized	Total number of times an unoptimized NON EMPTY algorithm was used.
Total NON EMPTY for calculated members	Total number of times a NON EMPTY algorithm looped over calculated members.
Total Autoexist	Total number of times Autoexist was performed.
Total EXISTING	Total number of times an EXISTING set operation was performed.

Memory

Statistics related to Microsoft Analysis Services internal server memory.

Counter	Description
Page Pool 64 Alloc KB	Memory borrowed from system, in KB. This memory is given away to other parts of the server.
Page Pool 64 Lookaside KB	Current memory in 64KB lookaside list, in KB. (Memory pages ready to be used.)
Page Pool 8 Alloc KB	Memory borrowed from 64KB page pool, in KB. This memory is given away to other parts of the server.
Page Pool 8 Lookaside KB	Current memory in 8KB lookaside list, in KB. (Memory pages ready to be used.)
Page Pool 1 Alloc KB	Memory borrowed from 64KB page pool, in KB. This memory is given away to other parts of the server.
Page Pool 1 Lookaside KB	Current memory in 8KB lookaside list, in KB. (Memory pages ready to be used.)
Cleaner Current Price	Current price of memory, $/byte/time, normalized to 1000.
Cleaner Balance/sec	Rate of balance+shrink operations.
Cleaner Memory shrunk KB/sec	Rate of shrinking, in KB/sec.
Cleaner Memory shrinkable KB	Amount of memory, in KB, subject to purging by the background cleaner.
Cleaner Memory nonshrinkable KB	Amount of memory, in KB, not subject to purging by the background cleaner.
Cleaner Memory KB	Amount of memory, in KB, known to the background cleaner. (Cleaner memory shrinkable + Cleaner memory nonshrinkable.)
Memory Usage KB	Memory usage of the server process as used in calculating cleaner memory price. Equal to counter Process\PrivateBytes plus the size of memory-mapped data, ignoring any memory which was mapped or allocated by the xVelocity in-memory analytics engine (VertiPaq) in excess of the xVelocity engine Memory Limit.
Memory Limit Low KB	Low memory limit, from configuration file.
Memory Limit High KB	High memory limit, from configuration file.
AggCacheKB	Current memory allocated to aggregation cache, in KB.
Quota KB	Current memory quota, in KB. Memory quota is also known as a memory grant or memory reservation.
Quota Blocked	Current number of quota requests that are blocked until other memory quotas are freed.
Filestore KB	Current memory allocated to filestore (file cache), in KB.
Filestore Page Faults/sec	Filestore page fault rate.
Filestore Reads/sec	Filestore pages read/sec.
Filestore KB Reads/sec	Filestore KB read/sec.
Filestore Writes/sec	Filestore pages written/sec. The writes are asynchronous.
Filestore KB Write/sec	Filestore KB written/sec. The writes are asynchronous.
Filestore IO Errors/sec	Filestore IO Error rate.
Filestore IO Errors	Filestore IO Errors total.
Filestore Clock Pages Examined/sec	Rate of background cleaner examining pages for eviction consideration.
Filestore Clock Pages HaveRef/sec	Rate of background cleaner examining pages that have a current reference count (are currently in use).
Filestore Clock Pages Valid/sec	Rate of background cleaner examining pages that are valid candidates for eviction.
Filestore Memory Pinned KB	Current filestore memory pinned, in KB.
In-memory Dimension Property File KB	Current size of in-memory dimension property file, in KB.
In-memory Dimension Property File KB/sec	Rate of writes to in-memory dimension property file, in KB.
Potential In-memory Dimension Property File KB	Potential size of in-memory dimension property file, in KB.
Dimension Property Files	Number of dimension property files.
In-memory Dimension Index (Hash) File KB	Size of current in-memory dimension index (hash) file, in KB.
In-memory Dimension Index (Hash) File KB/sec	Rate of writes to in-memory dimension index (hash) file, in KB.
Potential In-memory Dimension Index (Hash) File KB	Potential size of in-memory dimension index (hash) file, in KB.
Dimension Index (Hash) Files	Number of dimension index (hash) files.
In-memory Dimension String File KB	Current size of in-memory dimension string file, in KB.
In-memory Dimension String File KB/sec	Rate of writes to in-memory dimension string file, in KB.
Potential In-memory Dimension String File KB	Potential size of in-memory dimension string file, in KB.
Dimension String Files	Number of dimension string files.
In-memory Map File KB	Current size of in-memory map file, in KB.
In-memory Map File KB/sec	Rate of writes to in-memory map file, in KB.
Potential In-memory Map File KB	Potential size of in-memory map file, in KB.
Map Files	Number of map files.
In-memory Aggregation Map File KB	Current size of in-memory aggregation map file, in KB.
In-memory Aggregation Map File KB/sec	Rate of writes to in-memory aggregation map file, in KB.
Potential In-memory Aggregation Map File KB	Size of potential in-memory aggregation map file, in KB.
Aggregation Map Files	Number of aggregation map files.
In-memory Fact Data File KB	Size of current in-memory fact data file, in KB.
In-memory Fact Data File KB/sec	Rates of writes to in-memory fact data file KB rate.
Potential In-memory Fact Data File KB	Size of potential in-memory fact data file, in KB.
Fact Data Files	Number of fact data files.
In-memory Fact String File KB	Size of current in-memory fact string file, in KB.
In-memory Fact String File KB/sec	Rate of writes to in-memory fact string file, in KB.
Potential In-memory Fact String File KB	Size of potential in-memory fact string file, in KB.
Fact String Files	Number of fact string files.
In-memory Fact Aggregation File KB	Current size of in-memory fact aggregation file, in KB.
In-memory Fact Aggregation File KB/sec	Rate of writes to in-memory fact aggregation file, in KB.
Potential In-memory Fact Aggregation File KB	Size of potential in-memory fact aggregation file, in KB.
Fact Aggregation Files	Number of fact aggregation files.
In-memory Other File KB	Size of current in-memory other file, in KB.
In-memory Other File KB/sec	Rate of writes to in-memory other file, in KB.
Potential In-memory Other File KB	Size of potential in-memory other file, in KB.
Other Files	Number of other files.
VertiPaq Paged KB	Kilobytes of paged memory in use for in-memory data.
VertiPaq Nonpaged KB	Kilobytes of memory locked in the working set for use by the in-memory engine.
VertiPaq Memory-Mapped KB	Kilobytes of pageable memory in use for in-memory data.
Memory Limit Hard KB	Hard memory limit, from configuration file.
Memory Limit VertiPaq KB	In-memory limit, from configuration file.

Proactive Caching

Statistics related to Microsoft Analysis Services Proactive Caching.

Counter	Description
Notifications/sec	Rate of notifications from relational database.
Processing Cancellations/sec	Rate of processing cancellations caused by notifications.
Proactive Caching Begin/sec	Rate of proactive caching begin.
Proactive Caching Completion/sec	Rate of proactive caching completion.

Processing Aggregations

Statistics related to Microsoft Analysis Services processing of aggregations in MOLAP data files.

Counter	Description
Current partitions	Current number of partitions being processed.
Total partitions	Total number of partitions processed (successfully or otherwise).
Memory size rows	Size of current aggregations in memory. This count is an estimate.
Memory size bytes	Size of current aggregations in memory. This count is an estimate.
Rows merged/sec	Rate of rows merged or inserted into an aggregation.
Rows created/sec	Rate of aggregation rows created.
Temp file rows written/sec	Rate of writing rows to a temporary file. Temporary files are written when aggregations exceed memory limits.
Temp file bytes written/sec	Rate of writing bytes to a temporary file. Temporary files are written when aggregations exceed memory limits.

Processing Indexes

Statistics related to Microsoft Analysis Services processing of indexes for MOLAP data files.

Counter	Description
Current partitions	Current number of partitions being processed.
Total partitions	Total number of partitions processed (successfully or otherwise).
Rows/sec	Rate of rows from MOLAP files used to create indexes.
Total rows	Total rows from MOLAP files used to create indexes.

Processing

Statistics related to Microsoft Analysis Services processing of data.

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

Storage Engine Query

Statistics related to Microsoft Analysis Services storage engine queries.

Counter	Description
Current measure group queries	Current number of measure group queries being actively worked on.
Measure group queries/sec	Rate of measure group queries
Total measure group queries	Total number of queries to measure group.
Current dimension queries	Current number of dimension queries being actively worked on.
Dimension queries/sec	Rate of dimension queries
Total dimension queries.	Total number of dimension queries.
Queries answered/sec	Rate of queries answered.
Total queries answered	Total number of queries answered.
Bytes sent/sec	Rate of bytes sent by server to clients, in response to queries.
Total bytes sent	Total bytes sent by server to clients, in response to queries.
Rows sent/sec	Rate of rows sent by server to clients.
Total rows sent	Total rows sent by server to clients.
Queries from cache direct/sec	Rate of queries answered from cache directly.
Queries from cache filtered/sec	Rate of queries answered by filtering existing cache entry.
Queries from file/sec	Rate of queries answered from files.
Total queries from cache direct	Total number of queries derived directly from cache. Note that this is per partition.
Total queries from cache filtered	Total queries answered by filtering existing cache entries.
Total queries from file	Total number of queries answered from files.
Map reads/sec	Number of logical read operations using the Map file.
Map bytes/sec	Bytes read from the Map file.
Data reads/sec	Number of logical read operations using the Data file.
Data bytes/sec	Bytes read from the Data file.
Avg time/query	Average time per query, in milliseconds. Response time based on queries answered since the last counter measurement.
Network round trips/sec	Rate of network round trips. This includes all client/server communication.
Total network round trips	Total network round trips. This includes all client/server communication.
Flat cache lookups/sec	Rate of flat cache lookups. This includes global, session, and query scope flat caches.
Flat cache hits/sec	Rate of flat cache hits. This includes global, session, and query scope flat caches.
Calculation cache lookups/sec	Rate of calculation cache lookups. This includes global, session, and query scope calculation caches.
Calculation cache hits/sec	Rate of calculation cache hits. This includes global, session, and query scope calculation caches.
Persisted cache lookups/sec	Rate of persisted cache lookups. Persisted caches are created by the MDX script CACHE statement.
Persisted cache hits/sec	Rate of persisted cache hits. Persisted caches are created by the MDX script CACHE statement.
Dimension cache lookups/sec	Rate of dimension cache lookups.
Dimension cache hits/sec	Rate of dimension cache hits.
Measure group cache lookups/sec	Rate of measure group cache lookups.
Measure group cache hits/sec	Rate of measure group cache hits.
Aggregation lookups/sec	Rate of aggregation lookups.
Aggregation hits/sec	Rate of aggregation hits.

Threads

Statistics related to Microsoft Analysis Services threads.

Counter	Description
Short parsing idle threads	Number of idle threads in the short parsing thread pool.
Short parsing busy threads	Number of busy threads in the short parsing thread pool.
Short parsing job queue length	Number of jobs in the queue of the short parsing thread pool.
Short parsing job rate	Rate of jobs through the short parsing thread pool.
Long parsing idle threads	Number of idle threads in the long parsing thread pool.
Long parsing busy threads	Number of busy threads in the long parsing thread pool.
Long parsing job queue length	Number of jobs in the queue of the long parsing thread pool.
Long parsing job rate	Rate of jobs through the long parsing thread pool.
Query pool idle threads	Number of idle threads in the query thread pool.
Query pool busy threads	Number of busy threads in the query thread pool.
Query pool job queue length	Number of jobs in the queue of the query thread pool.
Query pool job rate	Rate of jobs through the query thread pool.
Processing pool idle non-I/O threads	Number of idle threads in the processing thread pool dedicated to non-I/O jobs.
Processing pool busy non-I/O threads	Number of threads running non-I/O jobs in the processing thread pool.
Processing pool job queue length	Number of non-I/O jobs in the queue of the processing thread pool.
Processing pool job rate	Rate of non-I/O jobs through the processing thread pool.
Processing pool idle I/O job threads	Number of idle threads for I/O jobs in the processing thread pool.
Processing pool busy I/O job threads	Number of threads running I/O jobs in the processing thread pool.
Processing pool I/O job queue length	Number of I/O jobs in the queue of the processing thread pool.
Processing pool I/O job completion rate	Rate of I/O jobs through the processing thread pool.