Daniel Tikvicki

Introduction to SQL Server Analysis Services (SSAS) monitoring

December 15, 2016 by

When it comes to monitoring of SQL Server Analysis Services (SSAS) performance, as it relates to the database engine, there are several categories that should be mentioned and compared.

Database Engine Analysis Services
Applied data model Relational data model (slower performance) Multi-purpose semantic data model (faster performance)

High scalability and availability

Potential issues and bottlenecks can arise if internal cache or system memory is overwhelmed Both Multidimensional and Tabular models have high availability and scalability.
Performance of queries If multiple queries run in a sequence, it will significantly slow down their performance Faster, because of multi-threaded architecture of SSAS engine
Query optimization SQL, as a language, has simpler syntax than MDX and DAX, but it lacks optimization for multiple query processing (slower performance) MDX and DAX have more complicated syntax, but the query code can be efficiently optimized with specific built-in functions, which shorten the code and provides more efficiency (faster performance)
Caching Mostly internal, with direct utilization of physical disk IO if the internal cache is overwhelmed Combining internal cache with Windows file system cache, in order to optimize processing

Analysis Services is part of SQL Server, but it operates independently through its own service (msmdsrv.exe).

In terms of the SSAS itself, there are two basic semantic SSAS models that can be deployed and used as an analytical engine which corresponds with SQL Server:

  • Multidimensional model – it uses OLAP modeling constructs like dimensions, measure groups and other, placed within the Multidimensional cube. This model is widely used in SQL-Server-based large-scaled production servers.
  • Tabular model – this model uses relational modeling constructs such as tables and relationships for modeling data in the Tabular cube and using the in-memory analytics engine for storing and calculating data.

For a detailed comparison between the two mentioned models, see Comparing Tabular and Multidimensional Solutions (SSAS).

Working components and corresponding activities of the mentioned SSAS models are:

  • Formula Engine – it provides MDX (or DAX) querying mechanism (parsing and processing), data retrieval from Storage Engine (as a part of query processing), and performs necessary calculations to prepare raw cube data for further aggregation or additional calculation
  • Storage Engine – it manages cube data reader/writer activities, in order to reach specific data granularity – in other words, to fulfill the specific purpose of analysis process.

The basic areas of SSAS performance monitoring are main engine construction, regarding actions (conducted by Formula Engine and Storage Engine), and performance counters measurements, which provide insights on how SSAS is performing.

Querying and cube processing workflow

The diagram below represents a visual interpretation of the SSAS service workflow (both Multidimensional and Tabular models), with the corresponding engine and elements of that engine, described further:

Executing particular MDX or DAX input (it depends on SSAS instance model) is a good starting point for SSAS performance monitoring. If the query is valid (there is no syntax errors, e.g.), it is accepted and iterates through the query parser and the actual query processor, which combined are usually called the Formula Engine (as previously mentioned). Functioning as an XMLA– based auditor, the query parser fetches requests and parameters from a query, retrieves raw data from the Storage Engine and includes it in process (to make response to query), and further, generate result assets. After that, the query parser passes that data to the query processor, which automatically creates the execution plan, caches the previous requests for further reuse.

At the moment the query is fully processed, the cube data already filtered by the query, or the subcube data request is sent to the Storage Engine, in order to retrieve and finish the processing (reading) and writing of actual cube data. The Storage Engine has multiple working threads, which include dimension and measure group data (including other related objects like dimensions attribute store, aggregations e.g.), because it reads/writes from the internal (SSAS-based) cache, and also partially uses Windows file system cache, in order to fully optimize performance of the cubes.

As stated before, the mentioned workflow applies on both SSAS models, but there are some minor differences between them, due to their construction.

Differences between Tabular and Multidimensional models’ workflow

There are several differences between SSAS Tabular and Multidimensional models, which should be mentioned when it comes to their workflow.

First, regarding the basic flow of querying process (Formula Engine), there are syntax differences between MDX and DAX query languages. An interesting fact is that the Tabular model can be queried with DAX and MDX, but the Multidimensional model can be queried only with MDX, which makes Tabular model more progressive.

Due to different approaches to the processing and caching, overall, managing the data, the SSAS Tabular model utilizes in-memory processing (regarding caching and keeping the integrity of processed data using xVelocity engine), which is faster, compared to plain cube processing and pre-defined aggregations in the Multidimensional model. The diagram below represents the workflow of SSAS Tabular model:

While the Tabular models keeps the cache in-memory, the Multidimensional model manages cache differently.

Multidimensional SSAS Caching

The caching system of Multidimensional SSAS is divided between the Formula and Storage Engine cache subsystems (as in diagram above).

The Formula Engine cache subsystem stores flat data (unprocessed, actually), and data which is created after the calculation or multiple calculations. This data is getting parsed faster than other data involved in the process. Also, by default, it allocates nearly 10% of the TotalMemoryLimit property within the SSAS main engine.

The Dimension cache and a Measure Group cache are parts of the Storage Engine cache subsystem. The Performance of this cache subsystem and its maintenance is important in SSAS monitoring, because this engine cache has a more sophisticated and complex workload than the Formula Engine, and, as mentioned before, it also corresponds with the Windows file system cache in cube processing.

These cache subsystems are important because cached data plays a more important role in preserving data integrity during query (Formula Engine) and cube (Storage Engine) processing. Both of these engines retrieve data from dedicated cache subsystems, and if it is necessary, they additionally retrieve the present Windows file system cache data.

When processing the same Multidimensional cube with a different set of parameters is needed, the whole procedure can be easily repeated as updated, because of previously cached data in Formula and Storage Engine.

Important SSAS performance counters

Analysis Services performance counters mentioned here are most important to track and analyze issues. In almost all critical areas, there is a combination of system and SSAS counters, which leveraged and compared, can help isolate potential issues

Critical area Full performance counter name
CPU
[ Windows] Processor: % Processor Time
[ Windows] System: Processor Queue Length
[ SSAS] Threads: Query pool job queue length
[ SSAS] Threads: Processing pool job queue length
MDX
[ SSAS] MDX: Total cells calculated
[ SSAS] MDX: Number of calculation covers
[ SSAS] MDX: Total Sonar subcubes
[ SSAS] MDX: Total recomputes
[ SSAS] MDX: Total NON EMPTY unoptimized
[ SSAS] MDX: Total NON EMPTY for calculated members
Memory
[ SSAS] Memory: Memory Usage KB
[ SSAS] Memory: Cleaner Memory shrinkable KB
[ SSAS] Memory: Cleaner Memory nonshrinkable KB
[ SSAS] Memory: Memory Limit Low KB
[ SSAS] Memory: Memory Limit High KB
[ SSAS] Memory: Cleaner: Memory Shrunk KB/sec
[ SSAS] Cache: Inserts/sec
[ SSAS] Cache: Evictions/sec
[ SSAS] Cache: KB added/sec
[ SSAS] Other: Calculation cache lookups/sec
[ SSAS] Other: Calculation cache hits/sec
[ SSAS] Other: Flat cache lookups/sec
[ SSAS] Other: Flat cache hits/sec
[ SSAS] Other: Dimension cache lookups/sec
[ SSAS] Other: Dimension cache hits/sec
[ SSAS] Other: Measure group cache lookups/sec
[ SSAS] Other: Measure group cache hits/sec
Processing
[ SSAS] Processing: Rows written/sec
[ SSAS] Processing: Aggregations: Rows created/sec
[ SSAS] Processing: Indexes: Rows/sec
Disk I/O
[ Windows] LogicalDisk: Disk Read Bytes/sec
[ Windows] LogicalDisk: Avg. Disk sec/Read
[ Windows] LogicalDisk: Avg. Disk sec/Write
[ SSAS] Storage Engine Query: Queries from files/sec
[ SSAS] Storage Engine Query: Data bytes/sec
[ SSAS] Cache: Copy Reads/sec
Network
[ Windows] Network Interface: Bytes Received/sec
[ Windows] Network Interface: Bytes Sent/sec
[ SSAS] Processing: Rows read/sec
[ SSAS] Storage Engine Query: Rows sent/sec

Daniel Tikvicki
SSAS monitoring

About Daniel Tikvicki

Daniel is a librarian who ran into a vortex of IT world, where he is levitating and learning constantly. He likes books, merely all forms of artistic expression (being a musician/poet himself), and he is underground publisher (fanzines and experimental music). Also, the points of interest include theology, mythology and pseudoscience. Daniel is currently working for ApexSQL LLC as Software sales engineer where he specializes for the BI environment.

8,023 Views