Earlier this week, I asked a question regarding the locks that are acquired by SQL Server during Index Maintenance. Before I proceed with answering the question, let’s revisit the types of index maintenance operations:

1. Index Rebuild
   
   
   
   • During a rebuild, the entire index (from the root to the leaf nodes) is rebuilt using the same columns, index type, uniqueness attribute, and sort order
     
   • The rebuild of an index effectively recreates the entire B-tree structure
   
   
2. Index Reorganize
   
   
   
   • Index reorganization, on the other hand, only removes fragmentation at the leaf level
     
   • Intermediate-level pages and the root page are not defragmented

From an availability perspective, Reorganization is better than a rebuild, because reorganization is always an ONLINE process, i.e. it acquires only short-term locks whereas REBUILD prevents any changes to the underlying table until the operation completes, i.e. it acquires long-term locks.

What kinds of locks are acquired during Index Maintenance?

To answer this question, let us perform a small test. We will rebuild an index on one of the table within the AdventureWorks sample database, and perform a reorganize on another table, while monitoring the locks acquired.

Locks during Index Rebuild

In one session to the SQL Server, run the following query to rebuild the index on the table HumanResources.Employee within the AdventureWorks sample database. As you can see, we are not using the ONLINE clause.

USE AdventureWorks2012

GO

ALTER INDEX ALL

    ON HumanResources.Employee REBUILD

GO

While the rebuild is being executed, execute the following query using another query editor window/session to the same SQL Server instance.

USE AdventureWorks2012

GO

SELECT tl.resource_associated_entity_id AS ObjectId,

       OBJECT_NAME(tl.resource_associated_entity_id) AS ObjectName,

       tl.request_mode AS LockRequestMode

FROM sys.dm_tran_locks AS tl

WHERE tl.resource_database_id = DB_ID()

  AND tl.resource_associated_entity_id = OBJECT_ID(‘HumanResources.Employee’)

GO

Here’s the output:

ObjectId	ObjectName	LockRequestMode
1237579447	Employee	Sch-M

We can see that during a Rebuild, the SQL server requests a Schema Modification Lock (Sch-M) on the underlying object (i.e. “table”). The Sch-M lock prevents concurrent access to the table, meaning that it blocks all outside operations until the locks are released.

Locks during Index ReOrganize

In one session to the SQL Server, run the following query to rebuild the index on the table Sales.SalesOrderDetail within the AdventureWorks sample database. Note that we are not using the ONLINE clause.

USE AdventureWorks2012

GO

ALTER INDEX ALL

    ON Sales.SalesOrderDetail REORGANIZE

GO

While the reorganize is being executed, execute the following query using another query editor window/session to the same SQL Server instance.

USE AdventureWorks2012

GO

SELECT tl.resource_associated_entity_id AS ObjectId,

       OBJECT_NAME(tl.resource_associated_entity_id) AS ObjectName,

       tl.request_mode AS LockRequestMode

FROM sys.dm_tran_locks AS tl

WHERE tl.resource_database_id = DB_ID()

  AND tl.resource_associated_entity_id = OBJECT_ID(‘HumanResources.Employee’)

GO

Here’s the output:

ObjectId	ObjectName	LockRequestMode
1154103152	SalesOrderDetail	S
1154103152	SalesOrderDetail	IX
1154103152	SalesOrderDetail	IS

We can see that during a Reorganize, the SQL Server requests effectively an Intent Exclusive lock on the table. The table is therefore locked for definition changes (DDL), but is still available for SELECT operations. This is expected because REORGANIZE is always an ONLINE process.

Conclusions – The correct answer to the question

• Locks acquired on the underlying table during Index Rebuild: Schema Modification Lock (Sch-M)
  
• Locks acquired on the underlying table during Index Reorganize: Intent Exclusive lock (IX)

Who came up with the correct answers?

First of all, I would like to thank everyone who responded. But, this is a competition, and hence, below are the people whose responses were correct (partially correct answers also considered here):

• Olga Medvedeva
  
• Nirav Gandhi
  
• Gaurang Patel
  
• Roji Thomas

And now, for the winners…

The following are the winners, randomly selected from those who participated in the quiz.

• Olga Medvedeva
  
• Roji Thomas

CONGRATULATIONS TO ALL THE WINNERS! Expect my DM on your twitter accounts this week-end (or earlier) with more information on your prize – a Pluralsight training code!

Check out: http://www.pluralsight-training.net/microsoft for a detailed list of the courses available. Did you know that Pluralsight has courses spanning almost every Microsoft technology currently available in the Market? If you are a blogger, do write about how you used the Pluralsight trainings – which trainings you took, and what is your feedback.

Interesting Reading/Reference:

Locking and Index Maintenance within SQL Server is a very interesting topic, and here are some resources to help you read/know more:

• Lock Modes in SQL Server (MSDN/BOL): http://msdn.microsoft.com/en-us/library/ms175519(SQL.105).aspx
  
• Locks Occupied by the WITH (NOLOCK) hint: http://blog.sqlauthority.com/2011/05/08/sql-server-what-kind-of-lock-with-nolock-hint-takes-on-object/
  
• “SQL Server Performance: Indexing Basics” (Pinal Dave, Vinod Kumar) – Pluralsight Online courses: http://www.pluralsight.com/training/Courses/TableOfContents/sql-server-indexing

Until we meet next time,

Be courteous. Drive responsibly.

Edit: August 17, 2012: Statement “The table is therefore locked for changes” has been changed to “The table is therefore locked for definition changes (DDL)” for clarity.