This option will control the maximum amount of memory to be allocated for the index creation. It's a defined size allocated for the index creation.
Scenario 1 : If the server memory is available and if the index needs more memory space. In that case, SQL Server will try to use the available server memory.
Scenario 2 : If the server memory is not available and if the index needs more memory space. In that case, SQL Server will try to use the existing allocated index space.
Right click on the server ->Properties -> Memory. You will get this option.
![]()
By default, the value is 0
Coniguring this setting through query,
--------------------------------------------------------------------------------
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'index create memory', 4096 -- Am setting the value as 4096KB
GO
RECONFIGURE;
GO
--------------------------------------------------------------------------------
Cheers,