SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability40
Although a query executed in parallel can be much faster, there is a point at which the parallel query execution becomes inefficient and can even extend the execution time. For example, parallel queries performing small joins and aggregations on small data sets might be inefficient. Because of different degrees of parallelism chosen at execution time, response times for one query can be different, depending on resource availability such as CPU and memory. Varying response times can be experienced by SAP system end users.
The following considerations are based on the workload profile of different SAP products:
SAP online transaction processing (OLTP) workload products. Less than one percent of queries benefit from parallel execution. Most of the heavier queries are executed by using batch jobs. In this case, set the max degree of parallelism option to 1.
SAP BW. The number of SAP BW queries that can be executed in parallel can be up to 10 percent. Because of the high percentage of heavy, resource-consuming queries, most SAP BW queries would typically be executed serially. In this case, set the max degree of parallelism option to 1 for the daily user workload. Set the parameter to 0 when aggregates are being built or rebuilt and during the delta load.
max server memory / min server memory
SQL Server 2005 can adapt its memory consumption to the workload. When the max server memory and min server memory options are set to the defaults (2147483647 and 0, respectively), SQL Server allocates memory dynamically within the range of the real memory. Use this setting for a dedicated database server that does not make the most of AWE.
The memory setting becomes more restrictive when SAP components are running on the same server. In this case, at a minimum, an upper boundary should be defined for SQL Server.
Assume that even a lightly loaded two-tier SAP installation requires at least 2 GB of memory to be allocated to run the SAP instance. Eventually, more memory will be required. If the SAP instance is heavily loaded, memory requirements range from 4 to 6 GB.
Note that these two parameters can be adjusted on the fly, even for AWE-allocated memory.
In dedicated database server configurations, a fixed memory allocation can be set on dedicated database servers. However, ensure that some memory is left for the operating system. Figure 15 shows where to modify the memory allocation parameters.
Microsoft Corporation © 2005