question

dataminor avatar image
dataminor asked

Modifying busy indexes

We have a nonclustered index that we want to add another INCLUDE column to. The index is frequently being used and also frequently getting INSERTs, UPDATEs and DELETEs. We recently tried dropping the index and recreating it, but we encountered blocking problems (looooong blocking problems). The DROP INDEX was being blocked by other processes. So we're a bit wary of that approach now. What is the best way to manage these sorts of changes to a busy index?
indexesblocking
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
What's your version of SQL Server? If you have Enterprise edition, you could do CREATE INDEX WITH DROP EXISTING, specifying ONLINE=ON. Otherwise I think you should try finding a window of time when there's least activity on the table. If you have activity 24/7 and run on a Standard Edition license, you should consider switching to Enterprise.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
+1 i couldn't have said it better! :) I held a session at Techdays about enterprise edition and the conclusion was, "can you afford not to use enterprise edition"
3 Likes 3 ·
WilliamD avatar image WilliamD commented ·
Please just be aware that an ONLINE index operation doesn't come for free. If the index is being hit with lots of changes, this will slow down the index rebuild and use more resources (primarily tempdb) Take a look at this [MSDN article on online index operations][1] to get a better understanding. [1]: http://msdn.microsoft.com/en-us/library/ms191261.aspx
2 Likes 2 ·
dataminor avatar image dataminor commented ·
We're using SQL 2005 enterprise. Incidentally BOL says (under DROP INDEX) "...The ONLINE option can only be specified when you drop clustered indexes...." I think I misinterpreted this.
0 Likes 0 ·
dataminor avatar image dataminor commented ·
Tried it (ONLINE = ON, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) and it worked beautifully. Many thanks.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.