Wednesday, January 27, 2010 4:02 PM
I am setting up a new server with 24GB Memory and 16 CPU. This is for a OLTP database
The following error message appeared when trying to reindex:
This index operation requires 512 KB of memory per DOP. The total requirement of 8192 KB for DOP of 16 is greater than the sp_configure value of 2048 KB set for the advanced server configuration option "index create memory (KB)". Increase this setting or reduce DOP and rerun the query.
What is this error exactly and what should be the fix for this.?
I had changed the Index creation memory value to 30720 (KB). Then I rebuild the index, which worked fine.
Can some one explain why is this error and what is the right solution for this. And what should be the best values for memory to be set with the above mentioned configuration.
Wednesday, January 27, 2010 4:35 PMVenkat,
you can do the following:
1. Set hint at the end of the command to rebuild the index. "ALTER INDEX) (INDEX) (TABLE ON REBUILD WITH (MAXDOP= 2)"
2. Apply the command to reset the memory needs.
exec sp_configure 'index create memory (KB)', 51200
reconfigure with override
SQL Server - SQLOS
MCP | MCTS | MCITP - DBA SQL Server Sênior http://leivio.spaces.live.com/ | http://br.linkedin.com/in/leivio
Wednesday, January 27, 2010 5:09 PMThanks for your reply
Using max dop, once after rebuilding the index, does it change back to default value.?
If index create memory is increased, is this used only for index rebuild, once after rebuilding, does this memory is allocated for any other queries.?
I am facing deadlocks while rebuilding the index online = on. why this happens on Enterprise edition.?
How can we solve this.
Thursday, January 28, 2010 2:21 AM
You would need to enable -T1222 to get the deadlock graphs. Without the deadlock graphs, it is difficult to determine what statements are deadlocking.
Regarding the MAXDOP query: If you are using a MAXDOP query hint, then the MAXDOP restriction that you set applies to the current query that you are executing using that hint. It doesn't have any effect on the other queries running on the server unless you have reduced the Max Degree of Parallelism on the server using Server Properties or sp_configure.
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: http://troubleshootingsql.wordpress.com
Thursday, August 16, 2012 6:54 PM