SQL Server 2008 Analysis Services

Version 2

    This template uses Windows performance counters to assess the status and performance of Microsoft SQL Server 2008 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.


    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.

     

    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.

     

    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.

     

    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.

     

    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.

     

    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 threads

         This monitor returns the number of idle threads in the processing thread pool.

     

    Threads: Processing pool busy threads

         This monitor returns the number of busy threads 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.

     

    Portions of this document were originally created by and are excerpted from the following sources:
    SAAS Corporation, Copyright © 2012 SAAS Corporation.  All rights reserved. Available at:
    http://www.ssas-info.com/analysis-services-articles/58-ssas-2008/1113-analysis-services-2008-performance-counters,
    http://allcomputers.us/windows_server/microsoft-sql-server-2008-analysis-services---monitoring-usage.aspx,
    http://allcomputers.us/windows_server/Microsoft-SQL-Server-2008-Analysis-Services---Monitoring-query-performance.aspx,
    http://www.grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20SSAS.htm,
    http://allcomputers.us/windows_server/Microsoft-SQL-Server-2008-Analysis-Services---Monitoring-processing-performance.aspx
    http://msdn.microsoft.com/en-us/library/hh230807.aspx

    http://www.ssas-info.com/analysis-services-articles/58-ssas-2008/1113-analysis-services-2008-performance-counters,
    http://allcomputers.us/windows_server/microsoft-sql-server-2008-analysis-services---monitoring-usage.aspxhttp://allcomputers.us/windows_server/Microsoft-SQL-Server-2008-Analysis-Services---Monitoring-query-performance.aspx,
    http://www.grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20SSAS.htm,
    http://allcomputers.us/windows_server/Microsoft-SQL-Server-2008-Analysis-Services---Monitoring-processing-performance.aspx http://www.ssas-info.com/analysis-services-articles/58-ssas-2008/1113-analysis-services-2008-performance-counters,
    http://allcomputers.us/windows_server/microsoft-sql-server-2008-analysis-services---monitoring-usage.aspx,
    http://allcomputers.us/windows_server/Microsoft-SQL-Server-2008-Analysis-Services---Monitoring-query-performance.aspx,
    http://www.grumpyolddba.co.uk/monitoring/Performance%20Counter%20Guidance%20-%20SSAS.htm,
    http://allcomputers.us/windows_server/Microsoft-SQL-Server-2008-Analysis-Services---Monitoring-processing-performance.aspx