SQL Analysis Procedure Indexes Test

When we talk of processing a cube, there are two parts to it: processing data which rebuilds dimensions with attribute store, hierarchy store and fact data store, and processing indexes which creates bitmap indexes and defined aggregation. One of the most common sources of query performance problems is unprocessed indexes. Typically, to process indexes, the Process Index mode is used. This processing mode is available for Dimensions, Measure Groups, Partitions, and Cubes. This option works only on the processed objects and for unprocessed objects, it returns an error. When applied on any processed objects, it creates aggregations and indexes if they do not already exist. If it already exists, it recreates them.

If query performance degrades, you may want to check index processing. This is where the SQL Analysis Procedure Indexes test helps! This test monitors the processing of indexes for MOLAP data files, and reports the number of partitions processed, and the count of rows indexed. The rate at which rows are indexed is also revealed, so administrators can rapidly detect any slowness in indexing.

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

Unit

Interpretation

Current partitions

Indicates the number of partitions being processed currently.

Number

 

Partitions processed

Indicates the total number of partitions processed.

Number

 

Rate of rows from MOLAP files

Indicates the rate of rows from MOLAP files used to create indexes.

Rows/Sec

A consistent rise in the value of this measure could indicate latency in index processing.

For fast index processing and to reduce stress on server during indexing, do the following:

  • Do a Process Data and Process Index separately instead of a Process Full, as it reduces the stress on the server

  • When creating hierarchies, you should make as many of them natural as possible

  • Reduce the number of attributes in the dimensions

  • Manage partitions using the free tool, SSAS Partition Manager

  • You must ensure that the processing query that populates the temporary partition does not duplicate any data already present in an existing partition

Rows from MOLAP files

Indicates the total rows from MOLAP files used to create indexes.

Number