cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

SQL Server 2017 Analysis Services.apm-template

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

https://support.microsoft.com/en-us/help/4056328/performance-counters-are-missing-after-the-installa...

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

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.

Attachments
Version history
Revision #:
1 of 1
Last update:
‎08-09-2018 01:10 PM
Updated by: