Thursday, June 3, 2010

How to optimize memory for indexing in SQL Server 2008

By default, SQL Server 2008 is able to manage the amount of memory allocated for index creation operations. The Server will be able to allocate additional memory for index creation operations, if additional memory is required for the process and the memory is available based on the “server memory configuration settings”. If additional memory is required but not available, the index creation will use the memory allocated to perform index creation. Usually, SQL Server self-tuning is able to work very well with this feature. However, the main exception in cases in which you can use partitioned tables and indexes and have nonaligned partitioned indexes. If there is a high degree of parallelism (lots of simultaneous index creation operations), you can encounter problems creating indexes. In this situation, you can allocate a specific amount of index creation memory.

If you want to use a specific index creation memory allocation, follow the following simple 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:
exec sp_configure "index create memory",

Windows PowerShell
Invoke-Sqlcmd -Query "exec sp_configure 'index create memory',
"-ServerInstance "Server\Instance"

Here, you have to note that the amount of the allocated memory 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.

By ,