TechNet
Export (0) Print
Expand All

Performance Counters (SSAS)

 

Applies To: SQL Server 2016

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.

GroupDescription
CacheStatistics related to the Analysis Services aggregation cache.
ConnectionStatistics related to Microsoft Analysis Services connections.
Data Mining PredictionStatistics related to processing data mining models processing.
Data Mining Model ProcessingStatistics related to creating predictions from data mining models.
LocksStatistics related to Microsoft Analysis Services internal server locks.
MDXStatistics related to Microsoft Analysis Services MDX calculations.
MemoryStatistics related to Microsoft Analysis Services internal server memory.
Proactive CachingStatistics related to Microsoft Analysis Services Proactive Caching.
Processing AggregationsStatistics related to processing of aggregations in MOLAP data files.
Processing IndexesStatistics related to processing of indexes for MOLAP data files.
ProcessingStatistics related to processing of data.
Storage Engine QueryStatistics related to Microsoft Analysis Services storage engine queries.
ThreadsStatistics related to Microsoft Analysis Services threads.

Cache

Statistics related to the Microsoft Analysis Services aggregation cache.

CounterDescription
Current KBCurrent memory used by the aggregation cache, in KB.
KB added/secRate of memory added to the cache, KB/sec.
Current entriesCurrent number of cache entries.
Inserts/secRate of insertions into the cache. The rate is tracked per partition per cube per database.
Evictions/secRate of evictions from the cache. This is per partition per cube per database. Evictions are typically due to background cleaner.
Total insertsInsertions into the cache. The rate is tracked per partition per cube per database.
Total evictionsEvictions from the cache. Evictions are tracked per partition per cube per database. Evictions are typically due to background cleaner.
Direct hits/secRate of cache direct hits. A cache hit indicates that queries were answered from an existing cache entry.
Misses/secRate of cache misses.
Lookups/secRate of cache lookups.
Total direct hitsTotal count of direct cache hits. A direct cache hit indicates that queries were answered from existing cache entries.
Total missesTotal count of cache misses.
Total lookupsTotal number of lookups into the cache.
Direct hit ratioRatio of cache direct hits to cache lookups, for the period between counter values.
Total filtered iterator cache hitsTotal number of cache hits that returned an indexed iterator over the filtered results.
Total filtered iterator cache missesTotal 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.

CounterDescription
Current connectionsCurrent number of client connections established.
Requests/secRate of connection requests. These are arrivals.
Total requestsTotal connection requests. These are arrivals.
Successes/secRate of successful connection completions.
Total successesTotal successful connections.
Failures/secRate of connection failures.
Total failuresTotal failed connection attempts.
Current user sessionsCurrent number of user sessions established.

Data Mining Model Processing

Statistics related to Microsoft Analysis Services Data Mining model processing.

CounterDescription
Cases/secRate at which cases are processed.
Current models processingCurrent number of models being processed.

Data Mining Prediction

Statistics related to Microsoft Analysis Services Data Mining prediction.

CounterDescription
Concurrent DM queriesCurrent number of data mining queries being actively worked on.
Predictions/secNumber of predictions generated in data mining queries
Rows/secNumber of rows handled during a data mining prediction query.
Queries/secNumber of data mining queries that were handled.
Total QueriesTotal data mining queries received by the server.
Total RowsTotal rows returned by data mining queries.
Total PredictionsTotal data mining prediction queries received by the server.

Locks

Statistics related to Microsoft Analysis Services internal server locks.

CounterDescription
Current latch waitsCurrent 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/secRate of latch requests that could not be granted immediately and had to wait before being granted.
Current locksCurrent number of locked objects.
Current lock waitsCurrent number of clients waiting for a lock.
Lock requests/secNumber of lock requests per second.
Lock grants/secNumber of lock grants per second.
Lock waits/secNumber 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/secRate of lock denials.
Unlock requests/secNumber of unlock requests per second.
Total deadlocks detectedTotal number of deadlocks detected.

MDX

Statistics related to Microsoft Analysis Services MDX Calculations.

CounterDescription
Number of calculation coversTotal number of evaluation nodes built by MDX execution plans, including active and cached.
Current number of evaluation nodesCurrent (approximate) number of evaluation nodes built by MDX execution plans, including active and cached.
Number of Storage Engine evaluation nodesTotal number of Storage Engine evaluation nodes built by MDX execution plans.
Number of cell-by-cell evaluation nodesTotal number of cell-by-cell evaluation nodes built by MDX execution plans.
Number of bulk-mode evaluation nodesTotal number of bulk-mode evaluation nodes built by MDX execution plans.
Number of evaluation nodes that covered a single cellTotal number of evaluation nodes built by MDX execution plans that covered only one cell.
Number of evaluation nodes with calculations at the same granularityTotal 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 nodesCurrent (approximate) number of cached evaluation nodes built by MDX execution plans.
Number of cached Storage Engine evaluation nodesTotal number of cached Storage Engine evaluation nodes built by MDX execution plans
Number of cached bulk-mode evaluation nodesTotal number of cached bulk-mode evaluation nodes built by MDX execution plans.
Number of cached 'other' evaluation nodesTotal number of cached evaluation nodes built by MDX execution plans that are neither Storage Engine nor Bulk-mode.
Number of evictions of evaluation nodesTotal number of cache evictions of evaluation nodes due to collisions.
Number of hash index hits in the cache of evaluation nodesTotal 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 nodesTotal number of cell-by-cell hits in the cache of evaluation nodes.
Number of cell-by-cell misses in the cache of evaluation nodesTotal number of cell-by-cell misses in the cache of evaluation nodes.
Number of subcube hits in the cache of evaluation nodesTotal number of subcube hits in the cache of evaluation nodes.
Number of subcube misses in the cache of evaluation nodesTotal number of subcube misses in the cache of evaluation nodes.
Total Sonar subcubesTotal number of subcubes that the query optimizer generated.
Total cells calculatedTotal number of cell properties calculated.
Total recomputesTotal number of cells recomputed due to error.
Total flat cache insertsTotal number of cell values inserted into flat calculation cache.
Total calculation cache registeredTotal number of calculation caches registered.
Total NON EMPTYTotal number of times a NON EMPTY algorithm was used.
Total NON EMPTY unoptimizedTotal number of times an unoptimized NON EMPTY algorithm was used.
Total NON EMPTY for calculated membersTotal number of times a NON EMPTY algorithm looped over calculated members.
Total AutoexistTotal number of times Autoexist was performed.
Total EXISTINGTotal number of times an EXISTING set operation was performed.

Memory

Statistics related to Microsoft Analysis Services internal server memory.

CounterDescription
Page Pool 64 Alloc KBMemory borrowed from system, in KB. This memory is given away to other parts of the server.
Page Pool 64 Lookaside KBCurrent memory in 64KB lookaside list, in KB. (Memory pages ready to be used.)
Page Pool 8 Alloc KBMemory borrowed from 64KB page pool, in KB. This memory is given away to other parts of the server.
Page Pool 8 Lookaside KBCurrent memory in 8KB lookaside list, in KB. (Memory pages ready to be used.)
Page Pool 1 Alloc KBMemory borrowed from 64KB page pool, in KB. This memory is given away to other parts of the server.
Page Pool 1 Lookaside KBCurrent memory in 8KB lookaside list, in KB. (Memory pages ready to be used.)
Cleaner Current PriceCurrent price of memory, $/byte/time, normalized to 1000.
Cleaner Balance/secRate of balance+shrink operations.
Cleaner Memory shrunk KB/secRate of shrinking, in KB/sec.
Cleaner Memory shrinkable KBAmount of memory, in KB, subject to purging by the background cleaner.
Cleaner Memory nonshrinkable KBAmount of memory, in KB, not subject to purging by the background cleaner.
Cleaner Memory KBAmount of memory, in KB, known to the background cleaner. (Cleaner memory shrinkable + Cleaner memory nonshrinkable.)
Memory Usage KBMemory 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 KBLow memory limit, from configuration file.
Memory Limit High KBHigh memory limit, from configuration file.
AggCacheKBCurrent memory allocated to aggregation cache, in KB.
Quota KBCurrent memory quota, in KB. Memory quota is also known as a memory grant or memory reservation.
Quota BlockedCurrent number of quota requests that are blocked until other memory quotas are freed.
Filestore KBCurrent memory allocated to filestore (file cache), in KB.
Filestore Page Faults/secFilestore page fault rate.
Filestore Reads/secFilestore pages read/sec.
Filestore KB Reads/secFilestore KB read/sec.
Filestore Writes/secFilestore pages written/sec. The writes are asynchronous.
Filestore KB Write/secFilestore KB written/sec. The writes are asynchronous.
Filestore IO Errors/secFilestore IO Error rate.
Filestore IO ErrorsFilestore IO Errors total.
Filestore Clock Pages Examined/secRate of background cleaner examining pages for eviction consideration.
Filestore Clock Pages HaveRef/secRate of background cleaner examining pages that have a current reference count (are currently in use).
Filestore Clock Pages Valid/secRate of background cleaner examining pages that are valid candidates for eviction.
Filestore Memory Pinned KBCurrent filestore memory pinned, in KB.
In-memory Dimension Property File KBCurrent size of in-memory dimension property file, in KB.
In-memory Dimension Property File KB/secRate of writes to in-memory dimension property file, in KB.
Potential In-memory Dimension Property File KBPotential size of in-memory dimension property file, in KB.
Dimension Property FilesNumber of dimension property files.
In-memory Dimension Index (Hash) File KBSize of current in-memory dimension index (hash) file, in KB.
In-memory Dimension Index (Hash) File KB/secRate of writes to in-memory dimension index (hash) file, in KB.
Potential In-memory Dimension Index (Hash) File KBPotential size of in-memory dimension index (hash) file, in KB.
Dimension Index (Hash) FilesNumber of dimension index (hash) files.
In-memory Dimension String File KBCurrent size of in-memory dimension string file, in KB.
In-memory Dimension String File KB/secRate of writes to in-memory dimension string file, in KB.
Potential In-memory Dimension String File KBPotential size of in-memory dimension string file, in KB.
Dimension String FilesNumber of dimension string files.
In-memory Map File KBCurrent size of in-memory map file, in KB.
In-memory Map File KB/secRate of writes to in-memory map file, in KB.
Potential In-memory Map File KBPotential size of in-memory map file, in KB.
Map FilesNumber of map files.
In-memory Aggregation Map File KBCurrent size of in-memory aggregation map file, in KB.
In-memory Aggregation Map File KB/secRate of writes to in-memory aggregation map file, in KB.
Potential In-memory Aggregation Map File KBSize of potential in-memory aggregation map file, in KB.
Aggregation Map FilesNumber of aggregation map files.
In-memory Fact Data File KBSize of current in-memory fact data file, in KB.
In-memory Fact Data File KB/secRates of writes to in-memory fact data file KB rate.
Potential In-memory Fact Data File KBSize of potential in-memory fact data file, in KB.
Fact Data FilesNumber of fact data files.
In-memory Fact String File KBSize of current in-memory fact string file, in KB.
In-memory Fact String File KB/secRate of writes to in-memory fact string file, in KB.
Potential In-memory Fact String File KBSize of potential in-memory fact string file, in KB.
Fact String FilesNumber of fact string files.
In-memory Fact Aggregation File KBCurrent size of in-memory fact aggregation file, in KB.
In-memory Fact Aggregation File KB/secRate of writes to in-memory fact aggregation file, in KB.
Potential In-memory Fact Aggregation File KBSize of potential in-memory fact aggregation file, in KB.
Fact Aggregation FilesNumber of fact aggregation files.
In-memory Other File KBSize of current in-memory other file, in KB.
In-memory Other File KB/secRate of writes to in-memory other file, in KB.
Potential In-memory Other File KBSize of potential in-memory other file, in KB.
Other FilesNumber of other files.
VertiPaq Paged KBKilobytes of paged memory in use for in-memory data.
VertiPaq Nonpaged KBKilobytes of memory locked in the working set for use by the in-memory engine.
VertiPaq Memory-Mapped KBKilobytes of pageable memory in use for in-memory data.
Memory Limit Hard KBHard memory limit, from configuration file.
Memory Limit VertiPaq KBIn-memory limit, from configuration file.

Proactive Caching

Statistics related to Microsoft Analysis Services Proactive Caching.

CounterDescription
Notifications/secRate of notifications from relational database.
Processing Cancellations/secRate of processing cancellations caused by notifications.
Proactive Caching Begin/secRate of proactive caching begin.
Proactive Caching Completion/secRate of proactive caching completion.

Processing Aggregations

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

CounterDescription
Current partitionsCurrent number of partitions being processed.
Total partitionsTotal number of partitions processed (successfully or otherwise).
Memory size rowsSize of current aggregations in memory. This count is an estimate.
Memory size bytesSize of current aggregations in memory. This count is an estimate.
Rows merged/secRate of rows merged or inserted into an aggregation.
Rows created/secRate of aggregation rows created.
Temp file rows written/secRate of writing rows to a temporary file. Temporary files are written when aggregations exceed memory limits.
Temp file bytes written/secRate 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.

CounterDescription
Current partitionsCurrent number of partitions being processed.
Total partitionsTotal number of partitions processed (successfully or otherwise).
Rows/secRate of rows from MOLAP files used to create indexes.
Total rowsTotal rows from MOLAP files used to create indexes.

Processing

Statistics related to Microsoft Analysis Services processing of data.

CounterDescription
Rows read/secRate of rows read from all relational databases.
Total rows readCount of rows read from all relational databases.
Rows converted/secRate of rows converted during processing.
Total rows convertedCount of rows converted during processing.
Rows written/secRate of rows written during processing.
Total rows writtenCount of rows written during processing.

Storage Engine Query

Statistics related to Microsoft Analysis Services storage engine queries.

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

Threads

Statistics related to Microsoft Analysis Services threads.

CounterDescription
Short parsing idle threadsNumber of idle threads in the short parsing thread pool.
Short parsing busy threadsNumber of busy threads in the short parsing thread pool.
Short parsing job queue lengthNumber of jobs in the queue of the short parsing thread pool.
Short parsing job rateRate of jobs through the short parsing thread pool.
Long parsing idle threadsNumber of idle threads in the long parsing thread pool.
Long parsing busy threadsNumber of busy threads in the long parsing thread pool.
Long parsing job queue lengthNumber of jobs in the queue of the long parsing thread pool.
Long parsing job rateRate of jobs through the long parsing thread pool.
Query pool idle threadsNumber of idle threads in the query thread pool.
Query pool busy threadsNumber of busy threads in the query thread pool.
Query pool job queue lengthNumber of jobs in the queue of the query thread pool.
Query pool job rateRate of jobs through the query thread pool.
Processing pool idle non-I/O threadsNumber of idle threads in the processing thread pool dedicated to non-I/O jobs.
Processing pool busy non-I/O threadsNumber of threads running non-I/O jobs in the processing thread pool.
Processing pool job queue lengthNumber of non-I/O jobs in the queue of the processing thread pool.
Processing pool job rateRate of non-I/O jobs through the processing thread pool.
Processing pool idle I/O job threadsNumber of idle threads for I/O jobs in the processing thread pool.
Processing pool busy I/O job threadsNumber of threads running I/O jobs in the processing thread pool.
Processing pool I/O job queue lengthNumber of I/O jobs in the queue of the processing thread pool.
Processing pool I/O job completion rateRate of I/O jobs through the processing thread pool.

Community Additions

ADD
Show:
© 2016 Microsoft