May 8, 2012 at 3:00 am
Hi
Yesterday I had a tough time creating a nonclustered index on a table of size 4 gb. It was during peak load of our application. As soon as I run the create index script, i tstarts blocking lots of processes and there comes many processes that blocks this index creation. I waited for 20 mins and then killed the command. ultimately I couldn't create it.
But today during non peak hours, it got created easily in seconds. But we had a good business impact because of this. I understand that this is a reactive approach and all indexes should already be there in place instead of creating them during peak hours. But what if you get into such situation?
What are the things one should take care while creating a nonclustered index during peak load? What can make it faster?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 8, 2012 at 3:39 am
Assuming you have Enterprise edition you can use the CREATE INDEX OPTIONS of ONLINE=ON and SORT_IN_TEMPDB=ON. These are my standard use for creating non clustered indexes.
These will allow all transactions to proceed unblocked until the index is fully implemented.
May 8, 2012 at 4:12 am
In general, this is one of those "Doctor, doctor! It hurts when I do this" moments. The correct response being "Don't do do that."
It's pretty rare to have an emergency index create script. I just won't run those during peak times because of exactly what you described. If you are in a true emergency, then run the script and deal with the consequences. Using the options in the previous post will mitigate the pain, but not eliminate it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2012 at 7:19 am
Unfortunately the edition was Standard and I couldn't use online option.
MysteryJimbo (5/8/2012)
Assuming you have Enterprise edition you can use the CREATE INDEX OPTIONS of ONLINE=ON and SORT_IN_TEMPDB=ON. These are my standard use for creating non clustered indexes.These will allow all transactions to proceed unblocked until the index is fully implemented.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 8, 2012 at 7:20 am
ok, so the bottomline is don't create indexes on big table during peak hours.
Grant Fritchey (5/8/2012)
In general, this is one of those "Doctor, doctor! It hurts when I do this" moments. The correct response being "Don't do do that."It's pretty rare to have an emergency index create script. I just won't run those during peak times because of exactly what you described. If you are in a true emergency, then run the script and deal with the consequences. Using the options in the previous post will mitigate the pain, but not eliminate it.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
May 8, 2012 at 7:22 am
S_Kumar_S (5/8/2012)
ok, so the bottomline is don't create indexes on big table during peak hours.Grant Fritchey (5/8/2012)
In general, this is one of those "Doctor, doctor! It hurts when I do this" moments. The correct response being "Don't do do that."It's pretty rare to have an emergency index create script. I just won't run those during peak times because of exactly what you described. If you are in a true emergency, then run the script and deal with the consequences. Using the options in the previous post will mitigate the pain, but not eliminate it.
Correct. "Big" being relative to your hardware and system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply