Tip: Optimize Memory for Indexing in SQL Server 2008

Follow Our Daily Tips

• facebook.com/TechNetTips
• twitter.com/TechNetTips
• blogs.technet.com/tnmag

By default, SQL Server 2008 dynamically manages the amount of memory allocated for index creation operations. If additional memory is needed for creating indexes, and the memory is available based on the server memory configuration settings, the server will allocate additional memory for index creation operations. If additional memory is needed but not available, index creation will use the memory already allocated to perform index creation.

Normally, SQL Server self-tuning works very well with this feature. The main exception is in cases in which you use partitioned tables and indexes and have nonaligned partitioned indexes. In these cases, if there is a high degree of parallelism (lots of simultaneous index creation operations), you might encounter problems creating indexes. If this happens, you can allocate a specific amount of index creation memory.

To use a specific index creation memory allocation, complete the following steps:
1. In the Server Properties dialog box, go to the Memory page and set a value in the Index Creation Memory box. This value is set in kilobytes.
2. Click OK.

You can also use the stored procedure sp_configure to set the index creation memory size. The related commands are as follows:

T-SQL

exec sp_configure "index create memory", 
    <number of kilobytes>

Windows PowerShell

Invoke-Sqlcmd -Query "exec sp_configure 'index create memory', 
    <num kb>"-ServerInstance "Server\Instance"

Note that the amount of memory allocated to index creation operations should be at least as large as the minimum memory per query. If it is not, SQL Server will use the amount of memory specified as the minimum memory per query and display a warning about this.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator’s Pocket Consultant, Second Edition by William R. Stanek.

Looking for More Tips?

For more tips on Microsoft products and technologies, visit the TechNet Tips library.