SQL Analysis Store Engine Query Test

Analysis Services is made up of two engines:

  • Formula Engine (FE) – It is single-threaded. It processes the MDX queries, determines what data is needed to answer them, then requests that data from the Storage Engine, and then performs all calculations needed for the query. It does most of the analysis work and tries to keep cells in memory.

  • Storage Engine (SE) – It is multi-threaded. It handles all reading and writing of data: it fetches the data requested by the Formula Engine when a query is run and aggregates it to the required granularity. If cells are not in memory, it is the Storage Engine which gets the data from disk.

Analysis services can therefore cache two types of values:

  • Values returned by the storage engine;

    • Raw measure values, one cache per measure group;

    • Dimension data, one cache per dimension

  • Values returned by the formula engine

    • Numeric values only - strings can't be cached

All storage engine caches have the same structure, known as the data cache registry. Data in the data cache registry is held in subcubes - i.e., data at a common granularity. This data can also be aggregated to answer queries. The FE can also store values in data cache registry if calculations are evaluated in bulk mode.

There are three different scopes or lifetimes of an FE cache:

  • Query: For calculations defined in the WITH clause of a query, the FE values can only be cached for the lifetime of the query;

  • Session: For calculations defined for a session, using the CREATE MEMBER statement executed on the client, FE values can only be cached for the lifetime of a session;

  • Global: For calculations defined in the cube's MDX script, FE values can be cached either:

    • Any kind of cube processing takes place;

    • A ClearCache XMLA command is executed;

    • Writeback is committed

Query performance depends upon how the SE and FE caches are used. More cache hits reduce direct disk accesses, thereby significantly improving the engine's responsiveness to queries. Administrators need to track cache usage frequently, so they can quickly detect ineffective usage of the cache, ascertain why, and clear the cache bottleneck, so as to avert the adverse impact on query responsiveness. Besides caches, aggregations can also help reduce query execution time. An aggregation is a data structure that stores precalculated data. Aggregations prepare answers before questions are asked, and thus improve query response time. To ensure that queries are always serviced quickly, administrators should make sure adequate aggregations are defined and are frequently looked up.

To periodically measure query response time, and to diagnose the root cause of poor query performance, use the SQL Analysis Store Engine Query. This test tracks the rate at which requests are serviced by the calculation cache, dimension cache, and persistent cache. Poor cache hit ratios and their impact on query response time are thus brought to the attention of administrators. The frequency with which aggregations are accessed for servicing queries is also reported. Under- and over-utilization of aggregations is revealed in the process. With the help of the pointers provided by this test, administrators can fine-tune caches and aggregations, and improve overall query performance.

Target of the test : A Microsoft SQL Analysis Services Server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the SSAS server being monitored

Configurable parameters for the test
Parameters Description

Test Period

How often should the test be executed.

Host

The host for which the test is to be configured.

Port

The port at which the specified Host listens.

Instance

The name of a specific SQL Analysis Services Server instance to be monitored. The default value of this parameter is “default”. However, if the Microsoft SQL Server hosting SSAS uses named instances, then do the following:

  • Configure the Instance parameter with the name of the SQL Server instance that hosts SSAS.

  • Do not change the default value of the Port parameter

SSAS Version

Specify the SSAS version being monitored. By default, 11 is displayed here, indicating that version 11 of SSAS is monitored by default.

Measurements made by the test
Measurement Description Measurement Unit Interpretation

Query response time

Indicates the response time based on queries answered since the last measurement period.

Seconds

A low value is desired for this measure. A high value is indicative of poor query performance.

If query performance is poor, then check the cache hit ratios and aggregation lookups reported by this test to figure out whether ineffective cache/aggregation usage or improper cache/aggregation definition is impacting query responsiveness.

If the aforesaid factors appear to have no bearing on query performance, then try increasing the number of threads engaged in query processing. For this, edit the msmdsrv.ini file and increase the value of the CoordinatorQueryMaxThreads and CoordinatorExecutionMode parameters.

For the best query response time, set CoordinatorQueryMaxThreads parameter to 22, and the CoordinatorExecutionMode parameter to -6.

Data sent

Indicates the rate at which data is sent by server to clients, in response to queries.

KB/Sec

 

Aggregation hits

Indicates the rate at which queries accessed aggregations.

Hits/Sec

A high value is desired for this measure.

Aggregation lookups

Indicates the rate of aggregation lookups.

Hits/Sec

 

Aggregation hits ratio

Indicates the ratio of aggregation hits to the sum of aggregation hits and lookups.

Percent

A value over 80% is indicative of effective usage of aggregations. Low values indicate poor aggregation usage, which may be probably caused by the absence of enough aggregations to service queries. You need to define enough aggregations to reduce the number of records that the storage engine needs to scan from disk to satisfy a query. At the same time, avoid designing an excessive number of aggregations. Excessive aggregations reduce processing performance and may reduce query performance.

Calculation cache hits

Indicates the rate at which the calculation cache successfully serviced queries. This includes global, session, and query scope calculation caches.

Hits/Sec

 

Calculation cache lookups

Indicates the rate at which the calculation cache was looked up. This includes global, session, and query scope calculation caches.

Hits/Sec

 

Calculation cache hit ratio

Indicates the ratio of calculation cache hits to the sum of calculation cache lookups and cache hits.

Percent

A value over 80% is indicative of effective usage of the calculation cache. Low values indicate poor calculation cache usage, which may be probably because:

  • the cache is not sized right;

  • sufficient calculations were not stored in cache.

Current dimension queries

Indicates the number of dimension queries that are currently being processed.

Number

 

Data reads

Indicates the rate of logical read operations on the data file.

Reads/Sec

 

Dimension cache hits

Indicates the rate at which dimension caches were used to serve queries.

Hits/Sec

SSAS dimensions are groups of attributes based on columns from tables or views in a data source view. Dimensions exist independent of a cube, can be used in multiple cubes, can be used multiple times in a single cube, and can be linked between Analysis Services instances.

Dimension cache lookups

Indicates the rate at which dimension caches were looked up.

Hits/Sec

 

Dimension cache hits ratio

Indicates the ratio of dimension cache hits to the sum of dimension cache hits and lookups.

Percent

A value over 80% is indicative of effective usage of the dimension cache. Low values indicate poor dimension cache usage, which may be probably because:

  • the cache is not sized right;

  • sufficient data was not stored in cache.

Network round trips

Indicates the rate of network round trips.

Number/Sec

This includes all client/server communication.

Persistent cache hits

Indicates the rate at which the persistent cache serviced queries.

Hits/Sec

Persisted caches are created by the MDX script CACHE statement.

 

Persistent cache lookups

Indicates the rate at which the persistent cache was looked up.

Hits/Sec

Persistent cache hit ratio

Indicates the ratio of persistent cache hits to the sum of persistent cache lookups and cache hits.

Percent

A value over 80% is indicative of effective usage of the persistent cache. Low values indicate poor persistent cache usage, which may be probably because:

  • the cache is not sized right;

  • sufficient data was not stored in cache.

Rows sent

Indicates the rate at which rows were sent by server to clients.

Rows/Sec