This template uses Windows performance counters to assess the status and performance of Microsoft SQL Server 2017 Analysis Services.
Prerequisites: WMI access to the target server.
Credentials: Windows Administrator on the target server.
Note: Non-default instances require you to update the performance counter component monitors with the instance name.
With new version of SQL Server 2017, you may notice that the performance counters are missing in the Performance Monitor. Use Cumulative Update for Microsoft SQL Server 2017
Portions of this document were originally created by and are excerpted from the following sources:
https://docs.microsoft.com/en-us/sql/analysis-services/instances/performance-counters-ssas?view=sql-server-2017
MONITORED COMPONENTS
Service: SQL Server Analysis Services
This monitor returns CPU and memory usage of SQL Server Analysis Services. This service supplies online analytical processing (OLAP) and data mining functionality for business intelligence applications.
Cache: Direct hits/sec
This monitor returns the rate of cache direct hits. Queries were answered from an existing cache entry.
Cache: Lookups/sec
This monitor returns the rate of cache lookups.
Cache: Direct hit ratio
This monitor returns the ratio of cache direct hits to cache lookups, for the period between obtaining counter values.
Cache: Current entries
This monitor returns the current number of cache entries.
Cache: Current KB
This monitor returns the current memory used by the aggregation cache, in KB.
Cache: Inserts/sec
This monitor returns the rate of insertions into the cache. This is per partition per cube per database.
Cache: Evictions/sec
This monitor returns the rate of evictions from the cache. This is per partition per cube per database. Typically due to background cleaner.
Cache: Misses/sec
This monitor returns the rate of cache misses.
Cache: KB added/sec
Rate of memory added to the cache, KB/sec.
Cache: Total direct hits
Total count of direct cache hits. A direct cache hit indicates that queries were answered from existing cache entries.
Cache: Total evictions
Evictions from the cache. Evictions are tracked per partition per cube per database. Evictions are typically due to background cleaner.
Cache: Total filtered iterator cache hits
Total number of cache hits that returned an indexed iterator over the filtered results.
Cache: 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.
Cache: Total inserts
Insertions into the cache. The rate is tracked per partition per cube per database.
Cache: Total lookups
Total number of lookups into the cache.
Cache: Total misses
Total count of cache misses.
Connection: Current connections
This monitor returns the current number of client connections established.
Connection: Current user sessions
This monitor returns the current number of user sessions established. A connection is always tied to a session, but there could be sessions without active user connections.
Connection: Requests/sec
This monitor returns the rate of connection requests (arrivals).
Connection: Failures/sec
This monitor returns the rate of connection failures.
Connection: Successes/sec
This monitor returns the rate of successful connection completions.
Connection: Total failures
Total failed connection attempts.
Connection: Total requests
Total connection requests. These are arrivals.
Connection: Total successes
Total successful connections.
Data Mining Prediction: Queries/sec
This monitor returns the rate of Data Mining queries.
Data Mining Prediction: Predictions/sec
This monitor returns the rate of Data Mining prediction queries.
Locks: Current latch waits
This monitor returns the 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.
Locks: Current lock waits
This monitor returns the current number of clients waiting for a lock.
Locks: Current locks
This monitor returns the current number of locked objects.
Locks: Lock waits/sec
This monitor returns the 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.
Locks: Total deadlocks detected
This monitor returns the total number of deadlocks detected.
Note: This counter has the Count statistic as difference option enabled and will show the statistical difference from the last polling cycle.
Locks: Latch waits/sec
Rate of latch requests that could not be granted immediately and had to wait before being granted.
Locks: Lock denials/sec
Rate of lock denials.
Locks: Lock grants/sec
Number of lock grants per second.
Locks: Lock requests/sec
Number of lock requests per second.
Locks: Unlock requests/sec
Number of unlock requests per second.
MDX: Total NON EMPTY unoptimized
This monitor returns the total number of times unoptimized a non empty algorithm is used. Total non empty un-optimized is the number of Calculate Non Empty operations that are using an un-optimized algorithm. If this number continues to grow, there may be MDX queries that are running slowly for this reason and we might want to find them and optimize them.
Note: This counter has the Count statistic as difference option enabled and will show the statistical difference from the last polling cycle.
MDX: Total recomputes
This monitor returns the total number of cells recomputed due to error
Note: This counter has the Count statistic as difference option enabled and will show the statistical difference from the last polling cycle.
MDX: Total Sonar subcubes
This monitor returns the total number of subcubes that query optimizer generated
Note: This counter has the Count statistic as difference option enabled and will show the statistical difference from the last polling cycle.
Memory: Cleaner Memory shrinkable KB
This monitor returns the amount of memory, in KB, subject to purging by the background cleaner.
Memory: Cleaner Memory nonshrinkable KB
This monitor returns the amount of memory, in KB, not subject to purging by the background cleaner.
Memory: Cleaner Memory KB
This monitor returns the amount of memory, in KB, known to the background cleaner. (Cleaner memory shrinkable + Cleaner memory nonshrinkable.) Note that this counter is calculated from internal accounting information so there may be some small deviation from the memory reported by the operating system.
Memory: Cleaner Balance/sec
This monitor returns the rate of balance+shrink operations. Shows how many times the current memory usage is compared against the settings. Memory usage is checked every 500ms, so the counter will trend towards 2 with slight deviations when the system is under high stress. Preferred Value 2
Memory: Filestore KB
This monitor returns the current memory allocated to filestore (file cache), in KB.
Memory: Filestore Writes/sec
This monitor returns the rate of filestore written pages (asynchronous writes).
Memory: Filestore IO Errors/sec
This monitor returns the rate of filestore IO Errors.
Memory: Quota Blocked
This monitor returns the current number of quota requests that are blocked until other memory quotas are freed.
Memory: Filestore Reads/sec
This monitor returns the filestore pages read/sec.
Proactive Caching: Notifications/sec
This monitor returns the rate of notifications from relational database.
Proactive Caching: Processing Cancellations/sec
This monitor returns the rate of processing cancellations caused by notifications.
Proc Aggregations: Temp file bytes written/sec
This monitor returns the rate of writing bytes to a temporary file. Temporary files are written when aggregations exceed memory limits.
Proc Aggregations: Current partitions
Current number of partitions being processed.
Proc Aggregations: Total partitions
Total number of partitions processed (successfully or otherwise).
Proc Aggregations: Memory size rows
Size of current aggregations in memory. This count is an estimate.
Proc Aggregations: Memory size bytes
Size of current aggregations in memory. This count is an estimate.
Proc Aggregations: Rows merged/sec
Rate of rows merged or inserted into an aggregation.
Proc Aggregations: Rows created/sec
Rate of aggregation rows created.
Proc Aggregations: Temp file rows written/sec
Rate of writing rows to a temporary file. Temporary files are written when aggregations exceed memory limits.
Processing: Rows read/sec
This monitor returns the rate of rows read from all relational databases.
Processing: Rows written/sec
This monitor returns the rate of rows written during processing.
Processing: Total rows read
Count of rows read from all relational databases.
Processing: Rows converted/sec
Rate of rows converted during processing.
Processing: Total rows converted
Count of rows converted during processing.
Processing: Total rows written
Count of rows written during processing.
Storage Engine Query: Queries from cache direct/sec
This monitor returns the rate of queries answered from cache directly. Rate of queries answered from cache directly.
Storage Engine Query: Queries from cache filtered/sec
This monitor returns the rate of queries answered by filtering existing cache entry. Rate of queries answered by filtering existing cache entry.
Storage Engine Query: Queries from file/sec
This monitor returns the rate of queries answered from files. Rate of queries answered from files.
Storage Engine Query: Avg time/query
This monitor returns the average time per query, in milliseconds. Response time based on queries answered since the last counter measurement.
Storage Engine Query: Measure group queries/sec
This monitor returns the rate of measure group queries
Storage Engine Query: Dimension queries/sec
This monitor returns the rate of dimension queries
Threads: Processing pool idle I/O job threads
This monitor returns the number of idle threads for I/O jobs in the processing thread pool.
Threads: Processing pool busy I/O job threads
This monitor returns the number of threads running I/O jobs in the processing thread pool.
Threads: Processing pool job queue length
This monitor returns the number of jobs in the queue of the processing thread pool.
Threads: Processing pool job rate
This monitor returns the rate of jobs through the processing thread pool.