:::: MENU ::::

Process Buffer for SQL Server Analysis Services

  • May 13 / 2009
  • 0
dbDigger

Process Buffer for SQL Server Analysis Services

Analysis Services creates a process buffer in memory for each partition it processes. It allocates memory to each buffer as it is needed, and releases this memory from each buffer when partition processing is complete. Analysis Services uses each buffer for two separate tasks.

  • First, Analysis Services loads fact data for the partition from the read-ahead buffer into the process buffer and then sorts, indexes, and writes the fact data into the fact level of the MOLAP partition file in segments. The sorting process includes as much data as can be held in the process buffer at one time.
  • Second, Analysis Services uses the process buffer for calculating aggregations. If the process buffer in memory is not large enough to hold all calculations during this phase, Analysis Services supplements the process buffer with temporary files on disk to complete the calculation of aggregations.

The Process buffer size setting on the Processing tab in Analysis Manager (the ProcessReadSegmentSize value in the registry) determines the maximum size of each process buffer. By default, the maximum size of each process buffer is approximately 32 MB. For most applications, this is probably too small and should be immediately increased. A more effective setting is at least 150 to 200 MB. If the size of each process buffer is large enough to efficiently sort and index large portions of the fact data before it is written to segments in the partition file, overall data organization and query responsiveness improve. If Analysis Services exceeds the size of a process buffer while creating aggregations, Analysis Services changes its algorithm to use temporary files that augment the memory allocated to the process buffer. If temporary files are used, Analysis Services moves aggregations between these temporary files and the memory allocated to the process buffer as the aggregations are being calculated. All aggregations for a partition are calculated at once and must fit into memory; otherwise temporary files are used.

Consult us to explore the Databases. Contact us