Memory issues while reindex on sql server Enterprise edition 2005

Question

Hi

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.

Thanks
Venkat

Answer 1

Venkat,
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

[]´s

SQL Server - SQLOS
http://leivio.spaces.live.com/blog/cns!A9C38548B0E679DB!236.entry

---

MCP | MCTS | MCITP - DBA SQL Server Sênior http://leivio.spaces.live.com/ | http://br.linkedin.com/in/leivio

Answer 2

Thanks 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.

Thanks
Venkat

Answer 3

You would need to enable -T1222 to get the deadlock graphs. Without the deadlock graphs, it is difficult to determine what statements are deadlocking.

Reference: http://troubleshootingsql.wordpress.com/2009/12/30/deadlock-troubleshooting/

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
Twitter: www.twitter.com/banerjeeamit

Answer 4

I know this is an old post, but I just found it. Why would you not set the configuration option back to the default value of 0?

---

---

My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
My book: Pro SQL Server 2008 Mirroring