Author: Carl Rabeler
Contributers: Eric Jacobsen, Thomas Kejser
Technical Reviewers: Brad Daniels, Lindsey Allen, John Desch, Wayne Robertson, Kevin Cox

Introduction

SQL Server 2005 Service Pack 2 introduced a new advanced configuration setting for Analysis Services that enables an administrator to specify that a certain percentage of memory be allocated to Analysis Services when the service starts (memory preallocation). This configuration setting was introduced because the Microsoft Windows Server 2003 operating system did not scale well with many small virtual memory allocations due to memory fragmentation and inefficiencies in retrieving information already located in memory. With the use of this configuration setting with 64-bit versions of Windows Server 2003 on multiprocessor computers (specifically computers with 16, 32, and 64 cores), the SQL Customer Advisor Team (SQL CAT) observed significant performance gains during Analysis Services processing and querying when memory use substantially exceeds 3 GB. Indeed, when Unisys and Microsoft achieved a record-setting benchmark for Analysis Services cube processing, the memory preallocation configuration setting for Analysis Services was set to 20 rather than to the default setting of 0 (resulting in 20% of physical memory being allocated to Analysis Services). For more information about this benchmark, see Data Warehouse Performance: Unisys and Microsoft Achieve Record-setting Benchmark.

The memory manager in Windows Server 2008 includes many performance and scalability enhancements, one of which is a change in the algorithm for scanning pages in memory. Additional memory manager improvements include the following:

· Kernel virtual address space is dynamically allocated to improve scalability when a greater number of processors and larger memory configurations are used. The sizes of paged and non-paged memory pools are no longer fixed, so manually reconfiguring systems to prevent resource imbalances is not required. Previously, the sizes of some resources, such as memory pools, were set by registry keys or by SKU.

· Kernel virtual address space is made more available in x86 architectures by more efficiently using space in kernel-mode stacks in recursive calls into the kernel.

· When a kernel-mode component allocates more than a page of memory, the memory manager now uses the pool memory between the end of that allocation and the next page boundary to satisfy other memory requests.

· SuperFetch, an adaptive page prefetch technique that analyzes data usage patterns and preloads data into memory according to those patterns, was added.

· Numerous enhancements that make writing to the page file faster and more efficient have been added. The memory manager now writes more data in each operation, aligns pages with their neighbors, and does not write pages that are completely zero.

· There is now greater coordination between the memory manager and the cache manager for write operations.

· Prefetch-style clustering of pages to satisfy page faults and populate the system cache was added.

· The capabilities of NUMA architectures are more fully utilized.

For more information about the improvements in the Windows Server 2008 memory manager, see:

· Advances in Memory Management for Windows

· Memory Management, Dynamic Kernel Addressing, Memory Priorities and I/O Handling

· Designing Applications for High Performance

· Inside Windows Server 2008 Kernel Changes

We decided to test and compare Analysis Services partition and dimension processing using SQL Server 2008 Analysis Services RC0 running on Windows Server 2008 and Windows Server 2003 to assess the impact of these memory manager improvements on the type of hardware and with the type of data sets with which we have previously seen significant performance benefits with the memory preallocation configuration setting.

Executive Summary

Due to the improvements in the Windows Server 2008 memory manager related to the change in the algorithm for scanning pages in memory, SQL Server 2008 Analysis Services performed equally well during both partition and dimension processing with or without memory preallocation when running on Windows Server 2008. However, SQL Server 2008 Analysis Services performed substantially better during both partition and dimension processing with the use of the memory preallocation configuration setting when running on Windows Server 2003. Specifically, the processing performance of Analysis Services 2008 without memory preallocation on Windows Server 2008 was virtually identical to the processing performance of Analysis Services 2008 on Windows Server 2003 with memory preallocation. With the hardware that we used in our tests and with the Analysis Services objects that we processed, we observed a performance benefit of approximately 100% during partition processing and a performance benefit of between 30-40% during dimension processing.

If you use memory preallocation with SQL Server 2008 (or SQL Server 2005), use a value that is low enough to ensure that sufficient memory remains for other processes on the computer (avoiding paging) and high enough for Analysis Services (use the peak value for the Process: Private Bytes counter for the msmdsrv instance to establish this value).

Note: While we did not specifically test SQL Server 2005 Analysis Services for this technical note, the results discussed here apply to Analysis Services 2005 as well as to Analysis Services 2008 because the underlying code base for the portion of Analysis Services 2008 that we tested has not changed.

Test Environment

For our testing, we used the following:

· A Unisys ES7000/one Enterprise Server with 32 dual-core x64 processors and 128 GB of RAM that was connected to two HP EVA 8000 arrays

· Windows Server 2003 Datacenter Edition and Windows Server 2008 Datacenter Edition

· SQL Server 2008 RC0

· An internal Microsoft sales database, containing approximately 1 terabyte of relational data

· An Analysis Services cube based on this relational database, which resulted in a cube that was approximately 180 GB in size. The dimensions were approximately 60 GB in size and the partitions were approximately 120 GB in size (and contained no aggregations).

Note: We used the Microsoft Enterprise Engineering Center, with its extensive hardware inventory and team expertise to build the test system for this test.

Database Engine Configuration

For our processing tests, we set the minimum and maximum memory for the relational engine to 20 GB. We left all other settings at their defaults. We located the data and log files for the relational database on different LUNs from the Analysis Services dimension and partition files.

Analysis Services Configuration

In Analysis Services, we set memory preallocation to 40 (<PreAllocate>40</PreAllocate>) in the msmdsrv.ini file (which preallocates 40% of the 128 GB of memory on our test computer to Analysis Services at startup). We experimented with enabling large page support, but found no measurable performance benefit. Large pages require that the Analysis Services service account have the lock pages in memory user directly on the local computer. Large page support enables server applications to establish contiguous large-page memory regions. For more information about large page support and restrictions when using large pages, see Large Page Support.

Caution: When we enabled large page support for Analysis Services on Windows Server 2003 after the computer had been in operation for some time, and then restarted Analysis Services, this service took longer to restart than without large page support (from several minutes to occasionally much longer, sometimes requiring a system restart due to the inability of this service to acquire the specified amount of contiguous large pages because of memory fragmentation). However, we rarely saw this symptom with Windows Server 2008. When we enabled large page support for Analysis Services with Windows Server 2008, this service would restart quickly.

Test Methodology

For our processing tests, we processed the following Analysis Services objects:

· A single measure group containing 158 partitions. The size of these partitions on disk was approximately 9 GB. We fully processed this measure group after unprocessing all of the partitions in the measure group.

· A dimension containing 89 attributes, with the key attribute containing approximately 26 million distinct members. The size of this dimension on disk was approximately 38 GB. We fully processed this dimension after unprocessing the dimension.

· A dimension containing 47 attributes, with the key attribute containing approximately 75,000,000 distinct members. The size of this dimension on disk was approximately 18 GB. We incrementally processed this previously processed dimension (process update).

With each processing test, we always worked with warm file system cache (we did not reboot the computer between tests). That is, for each test, we tested once with the cold file system cache and then tested three times with the warm file cache to confirm that our test results were reproducible.

Note: We chose large dimensions and measure groups to attempt to maximize the potential impact of memory preallocation. The performance benefit of memory preallocation with smaller dimensions and partitions was not tested.

Test Results

In our tests, we observed the following:

· Using default settings, the processing performance of Analysis Services 2008 on Windows Server 2008 was always superior to the processing performance of Analysis Services 2008 on Windows Server 2003 (see Figure 1).

· Using memory preallocation with Analysis Services 2008 on Windows Server 2003 enabled us to match the processing performance of Analysis Services 2008 on Windows Server 2008 with default settings (See Figure 2).

· Using memory preallocation with Analysis Services 2008 on Windows Server 2008 provided no measurable performance benefit (See Figure 3).

Note: The results that we saw may not apply across all hardware platforms or with all data sets. We verified our test results on an HP Superdome with 32 IA64 single-core processors and 64 GB of RAM to compare the x64 and IA64 platforms with respect to this configuration setting. We did not verify our test results with SQL Server 2005, but do expect that we would see very similar results with this version of SQL Server. We also did not verify our results on any other hardware platforms.

Caution: If you choose to preallocate memory with Analysis Services 2008 (or Analysis Services 2005) when running on Windows Server 2003, test thoroughly to determine if there is a performance benefit in your environment. If you do preallocate memory, you must ensure that sufficient memory remains for the operating system and other services on your computer.

Partition Processing

During our partition processing tests, we observed performance improvements with memory preallocation of approximately 100% (see Figure 1).

Figure 1: Analysis Services 2008 Partition Processing

Dimension Processing

During our dimension processing tests, we observed performance improvements with memory preallocation of between 30-40% (see Figures 2 and 3).

Figure 2: Analysis Services 2008 Dimension Processing—Full

Figure 3: Analysis Services 2008 Dimension Processing—Incremental

Note: While we did not include MDX querying in this memory preallocation test, we observed the benefit of memory preallocation with SQL Server 2005 Analysis Services on Windows Server 2003 when we tested the performance of MDX queries involving many-to-many dimensions. For more information, see Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques. We expect the results that we saw in this test when running Analysis Services 2008 on Windows Server 2008 to apply equally to MDX queries, but more testing is required in this area.

Determining the Appropriate Value for Memory Preallocation

If you use memory preallocation with SQL Server 2008 (or SQL Server 2005), use a value that is low enough to ensure that sufficient memory remains for other processes on the computer (avoiding paging) and high enough for Analysis Services (use the peak value for the Process: Private Bytes counter for the msmdsrv instance to establish this value).

· If you set the memory preallocation value too high, you will starve other processes on the server and the result will be memory paging. If you are running the SQL Server relational engine on the same server as Analysis Services, you should also limit the maximum amount of memory used by the SQL Server relational engine. While there is no hard limit for how much memory you should leave for the operating system, 6-8 GB is generally sufficient if no other services are running on the same server.

· Set the memory preallocation to a value that is high enough to ensure that Analysis Service preallocates sufficient memory for Analysis Services query and processing activity on the server such that it does not need to request any additional address space. To determine this value, monitor the peak value for the Process: Private Bytes counter for the msmdsrv instance. The peak value establishes the maximum value for memory preallocation that you need to set. When you cannot set the preallocation value this high, set it as high as possible without starving other processes and the operating system.

Conclusion

For optimum performance with SQL Server 2008 Analysis Services default settings, use Windows Server 2008. To match the performance of SQL Server 2008 Analysis Services running on Windows Server 2008 when running SQL Server 2008 Analysis Services on Windows Server 2003, consider using memory preallocation. These conclusions apply to SQL Server 2005 as well as to SQL Server 2008.