index create memory Option
In SQL Server, the index create memory option controls the maximum amount of memory initially allocated for creating indexes. If more memory is later needed for index creation, and the memory is available, the server will use it, thus exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.
Due to introduction of partitioned tables and indexes in SQL Server 2005, the minimum memory requirements for index creation may increase significantly in case of non-aligned partitioned indexes and a high degree of parallelism. Starting with SQL Server 2005, this option controls the total initial amount of memory allocated for all index partitions in a single index creation operation. The query will terminate with an error message if the amount set by this option is less than the minimum required to run the query.
The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
The default value for this option is 0 (self-configuring).
The run value for this option will not exceed the actual amount of memory that can be used for the operating system and hardware platform on which SQL Server is running. On 32-bit operating systems, the run value will be less than 3 gigabytes (GB).
Note
The setting of the min memory per query option has precedence over the index create memory option. In SQL Server 2000, these options were independent, but starting with SQL Server 2005 they interact. If you alter both options, and the index create memory is less than min memory per query, you receive a warning message, but the value is set. During query execution, you receive a similar warning.
The index create memory option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change index create memory only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).
Example
To configure the index create memory option to 4096:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'index create memory', 4096
GO
RECONFIGURE;
GO