12

I am trying to create some indexes a SQL Server 2008 database but am getting the following error message.

Msg 8606, Level 17, State 1, Line 1 This index operation requires 1024 KB of memory per DOP. The total requirement of 2048 KB for DOP of 2 is greater than the sp_configure value of 704 KB set for the advanced server configuration option "index create memory (KB)". Increase this setting or reduce DOP and rerun the query.

Currently the index creation memory setting is set to 794KB and the minimum memory per query is set to 1024KB. What would be the impact of changing these values? Especially with regards to performance. Should I change them, create the index and change them back or should I set it to 0?

RolandoMySQLDBA
185k34 gold badges327 silver badges541 bronze badges
asked Jan 7, 2011 at 11:39
2
  • are you getting this error from a simple CREATE INDEX statement? Commented Jan 10, 2011 at 22:16
  • Yes on index creation. Commented Jan 17, 2011 at 17:40

1 Answer 1

5

Basing my assumptions off this question, your error message seems to indicate to me that you need to set "index create memory" to [1024 * DOP]. The error also seems to indicate that your DOP is set to 2, so if I were you, I would set index create memory to 2048KB(as stated in the error message) and see what happens.

If that doesn't work, I would try reducing the minimum memory per query and setting index create memory to [minimum memory per query * DOP](assuming you don't change DOP).

answered Jan 7, 2011 at 16:53

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.