SQL Analysis Procedure Aggregations Test
Processing is the process to load data into SSAS objects like Cubes, Partitions, and Dimensions, etc. on the OLAP Server with the data from the underlying relational data warehouse. As part of the processing step(s), aggregations are performed, data is loaded into one or more pre-defined partitions and various actions, which involve data modifications are performed in this step. The processed data is stored into respective locations like the OLAP Server, relational data warehouse, etc. depending on the Storage Mode defined for the different objects.
Processing consumes both memory and time. In fact, if the server does not have enough physical memory for processing aggregations, SSAS will have to use temp files for processing. This can increase the processing time.
This is why, if processing slows down, then administrators should first determine whether a memory shortage is causing the slowness. This is where the SQL Analysis Procedure Aggregations test helps! This test monitors the partitions and aggregations processed, measures the memory used during aggregation processing, and further reveals if temporary files were utilized for processing. If a processing slowdown is observed, then these metrics will indicate if it is because the server is not sized with sufficient memory for processing aggregations.
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
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:
|
SSAS Version |
Specify the SSAS version being monitored. By default, 11 is displayed here, indicating that version 11 of SSAS is monitored by default. |
Measurement |
Description |
Unit |
Interpretation |
---|---|---|---|
Current partitions |
Indicates the current number of partitions being processed. |
Number |
|
Partitions processed |
Indicates the total number of partitions processed, successfully or otherwise. |
Number |
|
Memory size |
Indicates the size of current aggregations in memory. |
MB |
|
Temporary file data write rate |
Indicates the rate of writing rows to a temporary file. |
Writes/Sec |
An Analysis Services instance uses temporary files to supplement the memory allocated to each process buffer when a process buffer is too small for the aggregations being processed. This means that if the value of this measure is high, you can conclude that the SSAS instance does not have enough memory to process aggregations. For best performance, it is more important to allocate a sufficiently large process buffer to obviate the need for temporary files during processing. If processing requires temporary files, the algorithm is slower than if the process buffer was large enough to perform the processing entirely in memory. If you find that the temporary files are used extensively and you cannot eliminate their use, you can add a second temporary file folder on a different physical drive. Using two temporary folders increases processing performance. |