Export (0) Print
Expand All

Performance Counters (SSAS)

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.

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.

Community Additions

ADD
Show:
© 2014 Microsoft