Perfmon Counters I Collect For SSAS Monitoring

September 22, 2018

The counters can be grouped in general categories; OS, Processing, and Query.  SSAS is a service running on a Windows OS.  Therefore, it’s important to monitor some general OS counters.  These counters relate to the disk, memory, processor, and the SSAS process itself.  The other categories capture counters related to SSAS processing and SSAS queries.  This makes sense since SSAS has two workloads; processing and querying.

OS

  • Logical and Physical Disk
    • Disk Read Bytes/sec – The Bytes read in one second. If the perfmon capture interval is greater than one second the value is averaged. This counter is useful for comparing the actual speed vs. the theoretical limits of the sub system.
    • Disk Write Bytes/sec – The Bytes written in one second. If the perfmon capture interval is greater than one second the value is averaged. This counter is useful for comparing the actual speed vs. the theoretical limits of the sub system.
    • Current Disk Queue Length – The number of request queued. If this is above the number of spindles + 2 the disk subsystem could be improved by adding spindles or faster disk (RAID 0/10).
    • % Disk Time – The Average Queue Length divided by 100. If the Average Queue Length is greater than 1 then % Disk Time will be greater than 100%.
  • Memory
    • Pages/sec – The number of pages read from and written to disk to resolve page faults. This happens when a page of virtual memory has been paged to disk. This is caused by insufficient physical RAM.
    • Committed Bytes – Total virtual memory requested by all processes. If this is higher than the amount of physical RAM data has been paged to disk. However, if Pages/sec is low or null this might not be an issue as the paged data is not used very.
  • MSASxx:Threads
    • Processing pool I/O job queue length – The number of jobs waiting for a thread from the processing pool. Processing pool activity occurs in the storage engine.
    • Query Pool Job queue length – The number of jobs waiting for a thread from the query pool. The query pool activity occurs in the formula engine. If this value is greater than 1 it indicates there’s more queries than query threads configured in the msmdsrv.ini config.
  • Process
    • Working Set – Private – Total amount of physical RAM used by the SSAS service that cannot be shared with other processes.
    • Working Set – Total amount of physical RAM used by the SSAS service.
    • Working Set Peak –   Max value reached by Working Set since the start of the process execution.
    • Page File Bytes – Total virtual memory (physical & paged) requested by a process. Corresponds to the Commit Size in Task Manager. Commit Size is the total amount of virtual memory requested by a process.
    • Private Bytes – Same as Page File Bytes
    • Page File Bytes Peak – Max value reached by Page File Bytes since the start of the process execution.
    • I/O Data Bytes/sec – Amount of data transferred in I/O operations for SSAS reads, writes and communication between client tools. This in conjunction with lack of CPU during long running queries could indicated an I/O bottleneck.
    • I/O Read Bytes/sec – Data transferred for read operations.
    • I/O Write Bytes/sec – Data transferred for write operations.
  • Processor
    • % Processor Time – The percentage of elapsed time that all of process threads used the processor to execution instructions. An instruction is the basic unit of execution in a computer, a thread is the object that executes instructions, and a process is the object created when a program is run. Code executed to handle some hardware interrupts and trap conditions are included in this count.
    • Processor Queue Length – The number of threads that are in the server’s processor queue, waiting to be executed by the CPU.

Processing

  • MSASxx:Processing
    • Rows converted/sec – Rate of rows converted during processing.
    • Rows written/sec – Rate of rows written during processing.
    • Rows read/sec – Rate of rows read from all relational databases during processing.
  • MSASxx:Threads
    • Processing pool busy non-I/O threads – The number of threads running non-I/O jobs in the processing thread pool.
    • Command Pool Busy Threads – The number of threads running non-I/O jobs in the command thread pool.
    • Processing Pool Job Queue Length – The number of non-I/O jobs in the processing thread pool queue. 
  • MSASxx:Memory
    • Cleaner Current Price – Indicates the rate at which the cleaner is working. Current price of memory, $/byte/time, normalized to 1000.

Query

  • MSASxx:Cache
    • Total Direct Hits – The number of subcube queries answered from existing cache entries. Compare this to Total Misses to evaluate the percentage of subcube query’s that are resolved using cache.
    • Total Misses – The number of subcube queries not answered from cache. Starts at 0 when cache cleared. This counter will increases until the cache is warm. If the value continues to grow it might be an indication of memory pressure.
    • Current Entries – Number of entries in the cache.
  • MSASxx:Connection
    • Current User Sessions – The number of user sessions currently established.
    • Current Connections – The number of connections currently established.
  • MSASxx:MDX
    • Total NON EMPTY optimized – The number of un-optimized queries. Same as Calculate Non Empty Begin (72) / Calculate Non Empty End (74) trace.
  • MSASxx:Storage Engine Query
    • Measure Group Cache hits/sec– The rate of measure group cache hits.
    • Measure Group Cache lookups/sec – The rate of measure group lookups.
    • Dimension Cache hits/sec – The rate of dimension cache hits.
    • Dimension Cache lookups/sec The rate of dimension cache lookups.
    • Flat cache hits/sec – Rate of flat cache hits.   Includes the global, session, and query scopes.
    • Flat cache Lookups/sec – Rate of flat cache lookups. Includes the global, session, and query scopes.
    • Calculation cache hits/sec – The rate of calculation cache hits. . Includes the global, session, and query scopes.
    • Calculation cache lookups/sec – The rate of calculation cache lookups. Includes the global, session, and query scopes.
    • Total queries answered – The total number of queries answered.
  •  MSASxx:Threads
    • Processing pool busy I/O job threads – The Number of threads running I/O jobs in the processing thread pool
    • Query pool busy threads – The number of busy threads in the query thread pool.Query and Processing
  •  MSASxx:Memory
    • Memory Limit High KB – The current value for Memory\TotalMemoryLimit property. By default this 80% of the server RAM. This counter will show the actual value in KB.
    • Memory Limit Low KB – The current value for Memory\LowMemoryLimit property. By default this 65% of the server RAM. This counter will show the actual value in KB.
    • Cleaner Memory nonshrinkable KB – Memory that cannot be purged by the memory manager.
    • Cleaner Memory shrinkable KB – Memory that can be purged by the memory manager.
    • Cleaner Memory KB – The total amount of memory controlled by the memory manager. This is the sum of shrinkable and nonshrinkable memory.
    • Memory Usage KB – The amount of private virtual memory allocated by SSAS.
    • Cleaner Current Price – Indicates how aggressive the memory manager is working to clear shrinkable memory. ($/byte/time, normalized to 1000)
    • Memory Limit Hard KB – The maximum amount of memory SSAS can ever allocate.