August 4, 2014 at 1:06 am
I want to create a nonclustered index but it takes a very long time in a testing server ( 8 hours) . When I run SP_who2 I found out that it blocked 1 stored procedure ( INSERT process)
I wonder how do I find out the time that a certain stored procedure is not executed so I can run my index creation?
Btw that Stored proc has been executed millions time in 1 day..
On the other hand , I really need to create that index to improve the performance of query .
Pls kindly advise
Many thanks
August 4, 2014 at 1:12 am
If you need to create the index, you can either drop the procedure and ensure you have a backup to hand to recreate it. That will prevent anyone running the proc to block the index creation.
Or set the DB to single user mode and run the index creation script in, then set back to multi user once done.
When your moving this to production, you will want to do it in your maintenance window, if you dont have one, schedule a downtime window with the business to say what your doing.
August 4, 2014 at 4:43 am
There's no magic. The creation of the index requires locks placed on the table that are going to prevent inserts. You'll have to finish the creation of the index prior to releasing some locks.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply