June 1, 2016 at 3:35 am
Hi,
I am trying to create a non clustered index with ONLINE on a table(say tableA), I am having a huge chain of blockings on this table for write queries. below are the set options I am using
June 1, 2016 at 5:17 am
Hi there,
When it comes to near the end of the process, it will acquire a schema modification lock.. for a period in order to actually add the index to the table ... !
Cheers,
R
June 1, 2016 at 5:22 am
We are trying to create index on a huge table, we expect it to run for around 1hr (as we tried in a replica copy with same data), but we are observing the huge blockings in few seconds after starting the index scripts.
June 1, 2016 at 5:53 am
1) Is there any trigger on that table ?
2) You must ensure your database have enough space to perform the online index operation, as it requires about 1.5 times the size of the table in addition to the current size..
Some more info here : https://msdn.microsoft.com/en-us/library/ms191261.aspx
June 1, 2016 at 10:46 am
yes there is an update trigger on this table and there is enough space so, I don't think that is causing the issue.
June 1, 2016 at 11:14 pm
Then run exec sp_lock @@SPID where @@SPID is the session id from where you create the index and see what is locking ..
June 2, 2016 at 2:03 am
when you do online indexing defiantly will take longer time to update the index and might lock the table, you can check locks SPID to find out more which process is getting blocked.
June 2, 2016 at 3:03 am
Thanks for the response. Here is the nice blog why there would be blockings.
But there are other precautions that we could take to reduce the blocking.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply