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.

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

• Author
• Recent Posts

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.

Latest posts by Daniel Tikvicki (see all)

• How to monitor the SQL Server tempdb database - July 6, 2017
• How to monitor total SQL Server indexes size - June 12, 2017
• How to set and use encrypted SQL Server connections - May 8, 2017