Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 10 down vote favorite
1

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?

share|improve this question
are you getting this error from a simple CREATE INDEX statement? – SQLRockstar Jan 10 '11 at 22:16
Yes on index creation. – olle Jan 17 '11 at 17:40

1 Answer

up vote 4 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.