A colleague mentioned he received a warning while building indexes online the other day, so I decided to check it out. A quick search in sys.messages for messages LIKE ‘%online%index%’ found the following message:
Warning: Online index operation on table ‘%.*ls’ will proceed but concurrent access to the table may be limited due to residual lock on the table from a previous operation in the same transaction.
The error message is fairly clear – if you have a transaction open with existing locks, you’ll get this warning, letting you know that your online index rebuild might not be as online as you think it will be.
As a test, let’s try the following code on AdventureWorks2008:
BEGIN TRAN UPDATE Person.Person SET FirstName = 'Kenneth' WHERE BusinessEntityID = 1 -- Rebuild an index on this table ALTER INDEX IX_Person_LastName_FirstName_MiddleName ON Person.Person REBUILD WITH (ONLINE = ON) ROLLBACK
In this case, we get the warning message due to the five extra locks taken as part of the UPDATE command. While this index is being rebuilt, other sessions trying to access that row will be blocked. This is a good warning.
However, what happens if we try to rebuild an index on a different table? Ideally, I’d want the same sort of warning – if I have any locks that could cause blocking, I’d like to know immediately. The following code will test that:
BEGIN TRAN UPDATE Person.Person SET FirstName = 'Kenneth' WHERE BusinessEntityID = 1 -- Rebuild an index on a completely different table ALTER INDEX IX_Customer_TerritoryID ON Sales.Customer REBUILD WITH (ONLINE = ON) ROLLBACK
The result is that we get no warning. Rebuilding an index on the Sales.Customer table has nothing to do with the Person.Person update we performed, so we miss out of the warning.
This goes against what I’d ideally like. In this case, access to the row modified in Person.Person will result in a block until the index rebuild has finished.
This is not much of an issue though. I can’t think of many situations where I’d want to rebuild an index inside a transaction – it’s the type of task where it’s more likely to be run in a very narrow, constrained transaction.