Tip: Customize Memory Allocation for Queries in SQL Server 2008

By default, SQL Server 2008 allocates a minimum of 1024 KB of memory for query execution. This memory allocation is guaranteed per user, and you can set it anywhere from 512 KB to 2 GB. If you increase the minimum query size, you can improve the performance of queries that perform processor-intensive operations, such as sorting or hashing. If you set the value too high, however, you can degrade the overall system performance. Because of this, you should adjust the minimum query size only when you are having trouble executing queries quickly.

The default setting of 1024 KB of RAM works in most cases. However, you might want to consider changing this value if the server operates in an extremely busy environment, with lots of simultaneous queries running in separate user connections, or in a relatively slow environment, with few (but large or complex) queries. In this case, four factors should determine your decision to adjust the minimum query size:

• The total amount of free memory (when the system is idle and SQL Server is running)
• The average number of simultaneous queries running in separate user connections
• The average query size
• The query response time you hope to achieve

A compromise is often necessary when setting these values. You cannot always get an instant response, but you can optimize performance based on available resources. Use the following equation to get a starting point for the optimization:
FreeMemory / (AvgQuerySize * AvgNumSimulQueries)

For example, if the system has 2200 MB of free memory, the average query size is 2 MB, and the average number of simultaneous queries is 50, then the optimal value for the query size is 2200 MB / (2 * 50), or 22 MB. Generally, this value represents the maximum you should assign given the current environment, and you will want to lower this value if possible.

To allocate memory for queries, complete the following steps:
1. In the Server Properties dialog box, go to the Memory page and set a value for the Minimum Memory Per Query box. This value is set in kilobytes.
2. Click OK.

You can also use the stored procedure sp_configure to set the minimum query size. The related command is:
exec sp_configure "min memory per query", <number of kilobytes>

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.