#0187-SQL Server-Quiz-What locks are acquired during Index Maintenance? Winners announced!


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:



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.

Advertisements

7 thoughts on “#0187-SQL Server-Quiz-What locks are acquired during Index Maintenance? Winners announced!

  1. rojipt

    Thanks.

    Couple of points need clarification.

    1. Using the sys.dm__tran__locks to inspect the lock requirements may not yield the desired result since the DMV only gives a point in time snapshot of the locks. To see and understand what locks are really required for an operation, one should capture the Locks Acquired and Locks Released events in Profiler with a filter on the SPID.

    2. Your stament about REORGANIZE – “The table is therefore locked for changes, but is still available for SELECT operations” – is confusing. You can perform ALL DML operations while the REORGANIZE is running. the IX lock is compatible with another IX lock. Afterall that is the whole point of an operation being ONLINE.

    Regards

    Roji Thomas

    http://sqlindian.com

    Like

    Reply
  2. Nakul Vachhrajani

    @Olga: A twitter account would be better, but in case you don’t plan to have one, can you send me an E-mail through this website? I will send over your codes via E-mail.

    @Roji:

    The AdvenutreWorks is a small database. Therefore, using `sys.dm_tran_locks` on a fast server may not yield the required results – true. However, from a representational standpoint on the blog, I decided to use DMVs.

    Yes IX are compatible with other IX, which is why DMLs are allowed. Regarding the statement, “The table is therefore locked for changes…” has been updated to “The table is therefore locked for definition changes…” (which was the original intention). Thanks for pointing out that miss.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s