SSAS Performance Metrics

Overview

This article covers the various SSAS performance metrics displayed by the Performance Analysis Dashboard and Performance Analysis Overview, and how to interpret different metric values and combinations of values across different metrics for SQL Sentry.

Note:  For Mode: S = Sample and H = History.

SSAS Metrics for Tabular and Multidimensional Modes

SectionMetricDescription
SSAS ActivityRows Transferred: Query Rows Sent / SecThe rate of rows sent by the server to the clients.
Mode: S, H
Type:Average rows sent per sec
SSAS ActivityRows Transferred:
Proc rows read / Sec
The rate of rows read from all relational databases. If you are connected to SQL Server, you should be reading at least 80,000 rows per sec per table being processed.
Mode: S, H
Type:Average rows read per sec
SSAS ActivityThreads: Formula Engine: BusyThe Processor performance object consists of counters that measure aspects of processor activity. The processor is the part of the computer that performs arithmetic and logical computations, initiates operations on peripherals, and runs the threads of processes.  A computer can have multiple processors.  The processor object represents each processor as an instance of the object.
Mode: S, H
Type:Last Value
SSAS ActivityThreads: Formula Engine: Job RateThe rate of jobs through the query thread pool.
Mode: S, H
Type:Last Value
SSAS ActivityThreads: Formula Engine: QueuedThe number of jobs in the queue of the query thread pool.
Mode: S, H
Type:Last Value
SSAS ActivityThreads: Storage Engine: BusyThe number of threads.
Mode: S, H
Type:Last Value
SSAS ActivityThreads: Storage Engine: Job RateThe rate of non IO jobs through the thread pool.
Mode: S, H
Type:Last Value
SSAS ActivityThreads: Storage Engine: QueuedThe number of jobs in the processing thread pool queue.
Mode: S, H
Type:Last Value
SSAS GeneralAverage Time (ms): Formula Engine The average amount of time for a query to be executing in the formula engine.  
Mode: S, H
Type:Avg/ms
Warning range:20 Sec
Critical range:One Min
SSAS GeneralAverage Time (ms): SE cachedThe average amount of time spent returning data from the storage engine cache.
Mode: S, H
Type:Avg/ms
Warning range:> One Sec
SSAS GeneralAverage Time (ms): SE non-cachedThe average amount of time spent returning non-cached data from the storage engine cache.
Mode: S, H
Type:Avg/ms
SSAS GeneralAverage Time (ms): SerializationThe average amount of time spent serializing data to disk ( as in data aggregations).
Mode: S, H
Type:Avg/ms
SSAS GeneralAverage Time (ms): SQL QueriesThe average amount of time spent executing SQL queries from the relational database.
Mode: S, H
Type:Avg/ms
SSAS GeneralAverage Time (ms): Processing

Displays the average processing time in milliseconds. Processing in SSAS Involves the SE, and can cause the Cache to become outdated and flushed. This causes a performance hit because when the query is run after processing completes, the SE has to retrieve the necessary data from the Windows file cache or from disk (cold cache). Non 0 values on the indicate that processing is taking place.

Processing can be improved by:

  • Re-scheduling processing when the least querying activity is going on.  This helps to avoid resource contention. 
  • Check the processing type that is being used; Use ProcessIndex instead of ProcessFull when possible. 
  • Break the measure groups into partitions to reduce the overall amount of work that is done.

Mode: S, H
Type:Avg/ms
SSAS GeneralMDX: Cells Calculated

The total number of cell properties calculated. High values for Cells calc’d while the query is executing indicates that the query is doing cell by cell calculations (this is like row by agonizing row in SQL Server) instead of Subspace (block by block) execution.

Mode: S, H
Type:Last Value/sec
SSAS GeneralMDX: Calculated Covers

The total number of evaluation nodes built by MDX execution plans including active, and cached. High values for Calc Covers while the query is executing indicates that the query is doing cell by cell calculations (this is like row by agonizing row in SQL Server) instead of Subspace (block by block) execution.

Mode: S, H
Type:Last Value/sec
SSAS GeneralMDX: Sonarsubcubes

The total number of subcubes that the query optimizer generated. High values for Sonarsubcubes while the query is executing indicates that the query is doing cell by cell calculations (this is like row by agonizing row in SQL Server) instead of Subspace (block by block) execution.

Mode: S, H
Type:Last Value/sec
SSAS GeneralMDX: Recomputes

The total number of cells recomputed due to error. Recomputes indicate that there were errors in the calculations during query execution. Non 0 values indicate where unnecessary recalculations are taking place and can fall back to cell by cell executions.

Mode: S, H
Type:Last Value/sec
SSAS GeneralMDX: NE unopt

The total number of times unoptimized the NON EMPTY algorithm is used. NE unopt helps determine if performance degradation is occurring due to a slower code path. Using a non-empty algorithm can speed up performance.

Mode: S, H
Type:Last Value/sec
SSAS GeneralMDX: NE calc memb

The total number of times the NON EMPTY algorithm was looping over calculated members. NE calc memb help determine if performance degradation is occurring due to a slower code path. Using a non-empty algorithm can speed up performance.

Mode: S, H
Type:Last Value/sec
SSAS MemoryMemory Usage (MB): ShrinkableSSAS has two general categories of memory, shrinkable and nonshrinkable. Shrinkable memory can be easily reduced and returned back to the OS. Shrinkable memory is the amount of memory in KB that's subject to purging by the background cleaner.
Mode: S, H
Type:MB
SSAS MemoryMemory Usage (MB): NonshrinkableSSAS has two general categories of memory, shrinkable and nonshrinkable. Nonshrinkable memory is generally used for more essential system-related tasks such as memory allocators and metadata objects, and is not easily reduced. Nonshrinkable memory is the amount of memory in KB that's not subject to purging by the background cleaner.
Mode: S, H
Type:MB
SSAS MemoryCache Activity: Cache InsertsThe rate of inserts from the cache.
Mode: S, H
Type:Last Value/sec
SSAS MemoryCache Activity: Cache EvictionsThe rate of evictions from the cache. Note:  This is per partition per cube per database.
Mode: S, H
Type:Last Value/sec
May correlate: Background cleaner
SSAS MemoryCache Activity: KB addedThe rate of memory added to the cache (KB / Sec).
Mode: S, H
Type:KB/sec
SSAS MemoryCache Activity: KB ShrunkThe rate of shrinking in KB / Sec.
Mode: S, H
Type:KB/sec
SSAS StorageFile Reads KB: by category


Mode: S, H
Type:KB/sec
SSAS StorageFile Writes KB: by category
Mode: S, H
Type:KB/sec
SSAS ActivityConnectionsThe current number of client connections established to the Analysis Services server.
Mode: S
Type:Last Value
Warning range:> 50
SSAS ActivitySessionsThe current number of user sessions established to the Analysis Services server.
Mode: S
Type:Last Value
SSAS ActivityRequests/SecThe rate of incoming connection requests per second.
Mode: S
Type:Last Value/sec
SSAS ActivityFailures/SecThe rate of connection failures per second.
Mode: S
Type:Last Value/sec
SSAS GeneralCurrent LocksThe current number of locked objects.
Mode: S
Type:Last Value
SSAS GeneralCurrent Lock WaitsThe current number of clients waiting for a lock.
Mode: S
Type:Last Value
SSAS MemoryCache Hit ratio: Dim percentThe ratio of cache hits from the dimensions.
Mode: S
Type:Percentage
SSAS MemoryCache Hit ratio: MG percentThe ratio for measure groups.
Mode: S
Type:Percentage
SSAS MemoryCache Hit ratio: Calc percent
Mode: S
Type:Percentage
SSAS MemoryCache Hit ratio: Flat percent
Mode: S
Type:Percentage
SSAS StorageFile Cache Hit Ratio: Read percentThe percent reading to the storage engine cache.
Mode: S
Type:Percentage
SSAS StorageFile Cache Hit Ratio: Write percentThe percent writing to the storage engine cache.
Mode: S
Type:Percentage

SSAS Metrics for Tabular Mode Only

SectionMetricDescription
SSAS MemoryMem Usage (MB): VertiPaq paged
Mode: S, H
Type:MB
SSAS MemoryMem Usage (MB): VertiPaq nonpaged
Mode: S, H
Type:MB
SSAS MemoryCache Activity: Dim Cache Lookups
Mode: S, H
Type:Last Value/sec
SSAS MemoryCache Activity: Calc Cache Lookups
Mode: S, H
Type:Last Value/sec

SSAS Metrics for Multidimensional Only

SectionMetricDescription
SSAS General Processing: Proc rows Written / SecDisplays the rate of Processing rows written during processing. Processing in SSAS Involves the SE, and can cause the Cache to become outdated and flushed. This causes a performance hit because when the query is run after processing completes, the SE has to retrieve the necessary data from the Windows file cache or from disk (cold cache). Non 0 values on the indicate that processing is taking place.

These metrics can also help determine the effectiveness of any processing tuning efforts. If your tuning efforts are effective, you should see an increase in these values accordingly.

Processing can be improved by:

  • Re-scheduling processing when the least querying activity is going on.  This helps to avoid resource contention. 
  • Check the processing type that is being used; Use ProcessIndex instead of ProcessFull when possible. 
  • Break the measure groups into partitions to reduce the overall amount of work that is done
Mode: S, H
Type:Last Value/sec
SSAS GeneralProcessing: Index rows createdDisplays the rate of rows from MOLAP files used to create indexes. Processing in SSAS Involves the SE, and can cause the Cache to become outdated and flushed. This causes a performance hit because when the query is run after processing completes, the SE has to retrieve the necessary data from the Windows file cache or from disk (cold cache).Non 0 values on the indicate that processing is taking place. 

These metrics can also help determine the effectiveness of any processing tuning efforts. If your tuning efforts are effective, you should see an increase in these values accordingly.

Processing can be improved by:

  • Re-scheduling processing when the least querying activity is going on.  This helps to avoid resource contention. 
  • Check the processing type that is being used; Use ProcessIndex instead of ProcessFull when possible. 
  • Break the measure groups into partitions to reduce the overall amount of work that is done.
Mode: S, H
Type:Last Value/sec
 SSAS GeneralProcessing: Aggregate rows createdDisplays the rate Aggregate rows are created during processing. Processing in SSAS involves the SE, and can cause the Cache to become outdated and flushed. This causes a performance hit because when the query is run after processing completes, the SE has to retrieve the necessary data from the Windows file cache or from disk (cold cache).Non 0 values on the indicate that processing is taking place.

These metrics can also help determine the effectiveness of any processing tuning efforts. If your tuning efforts are effective, you should see an increase in these values accordingly.

Processing can be improved by:

  • Re-scheduling processing when the least querying activity is going on.  This helps to avoid resource contention. 
  • Check the processing type that is being used; Use ProcessIndex instead of ProcessFull when possible. 
  • Break the measure groups into partitions to reduce the overall amount of work that is done.
Mode: S, H
Type:Last Value/sec
SSAS MemoryCache Lookups: DimensionThe rate of cache lookups.
Mode: S, H
Type:Last Value/sec
SSAS MemoryCache Lookups: Measure grpThe rate of Measure group queries.
Mode: S, H
Type:Last Value/sec
SSAS MemoryCache Lookups: Calc
The rate of calculation cache lookups, including global, session, and query scope calculation caches.
Mode: S, H
Type:Last Value/sec
SSAS MemoryCache Lookups: Flat The rate of Flat Cache lookups, including global, session, and query scope flat caches.
Mode: S, H
Type:Last Value/sec
SSAS MemoryCache Activity: Flat Cache insertsThe rate of insertions into the cache. Note:  This is per partition per cube per database.
Mode: S, H
Type:Last Value/sec
SSAS StorageFile Queries by Category.
Mode: S, H
Type:Last Value/sec
SSAS StorageCurrent Partitions by CategoryThe amount of partitions during querying .
Mode: S, H
Type:Last Value/sec