Index Black Ops Part 1 – Locks and Blocking

by Jason S, posted in DMO, Performance Tuning & Tracing, SQL Server, SQLServerSyndication, T-SQL

As I mentioned in my TSQL2sDay index summary post, the next few posts will be on sys.dm_db_index_operational_stats and the information that the DMV contains.  In this post, we are going to look at the locking and blocking columns.

Base Columns

Before we jump over to the meat and potatoes, let’s first take a look at four columns that we’ll be using to make sense of the data in the DMV.

  • database_id (smallint) – The ID of the database.  This can be translated with DB_NAME() and by querying sys.databases.
  • object_id (int) – ID of the table or view  This can be translated using sys.all_objects, sys.tables, or with OBJECT_NAME().
  • index_id (int) – ID of the index or heap. Used in conjunction with object_id this can determine the view that is being referenced in sys.indexes.
  • partition_number (int) – 1-based partition number within the index or heap. Every index has at least a single partition.  Even it you aren’t partitioning the index, the partition is there for you non-partitioned data.

Meat and Potato Columns

I said meat and potato columns, so here we are.  These are the columns that you can

  • row_lock_count (bigint) – Cumulative number of row locks requested.
  • row_lock_wait_count (bigint) – Cumulative number of times the Database Engine waited on a row lock.
  • row_lock_wait_in_ms (bigint) – Total number of milliseconds the Database Engine waited on a row lock.
  • page_lock_count (bigint) – Cumulative number of page locks requested.
  • page_lock_wait_count (bigint) – Cumulative number of times the Database Engine waited on a page lock.
  • page_lock_wait_in_ms (bigint) – Total number of milliseconds the Database Engine waited on a page lock.

These are the columns that will provide the details at an individual index level on the blocking that is occurring.  The locks report on the volume of activity on the index.  The lock wait counts provide details on the rate in which the locks are being blocked.  Finally the lock wait in ms will help establish the degree of severity that the locking is in regards to.

Get Your Query On

Before going much further let’s build a couple queries that we can use to investigate locks and blocking.  One query to get page locks and blocking percentages:

SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,page_lock_count
,page_lock_wait_count
,CAST(100. * page_lock_wait_count / NULLIF(page_lock_count,0) AS decimal(6,2)) AS page_block_pct
,page_lock_wait_in_ms
,CAST(1. * page_lock_wait_in_ms / NULLIF(page_lock_wait_count,0) AS decimal(12,2)) AS page_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count + page_lock_wait_count DESC, row_lock_count + page_lock_count DESC

And another to get row locks and blocking percentages:

SELECT OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,row_lock_count
,row_lock_wait_count
,CAST(100. * row_lock_wait_count / NULLIF(row_lock_count,0) AS decimal(6,2)) AS row_block_pct
,row_lock_wait_in_ms
,CAST(1. * row_lock_wait_in_ms / NULLIF(row_lock_wait_count,0) AS decimal(12,2)) AS row_avg_lock_wait_ms
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
WHERE OBJECTPROPERTY(ios.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count + page_lock_wait_count DESC, row_lock_count + page_lock_count DESC

What Does It Mean?

At this point you are sitting there staring across the screen at me as though I am the one building the Devil’s Tower out of mashed potatoes.  I wouldn’t have written this post if this didn’t mean something.  And it does… no really.

Let’s look at how these locks are tabulated.  First run a query that will return all of the results for a table.  I’ll be using AdventureWorks – because that’s how I roll.

USE AdventureWorks
GO

SELECT *
FROM Person.Contact

Run the page lock query and the following results will be returned:

Index Black Ops Part 1-1

As you can see, returning all rows resulted in page locks as the query placed a lock on each page to return the data.

Change the query a bit to only return a single row.

USE AdventureWorks
GO

SELECT *
FROM Person.Contact
WHERE ContactID = 1

Run the row lock query and the following results will be returned:

Index Black Ops Part 1-2

This time the single row returned resulted in a row lock on a single row.

What About Blocking?

Copy the following query text into another query window and execute it.

BEGIN TRAN
UPDATE Person.Contact
WITH (PAGLOCK)
SET NameStyle = NameStyle

WAITFOR DELAY '00:00:10'
COMMIT TRAN

Go back to the original query window and execute the SELECT query without the WHERE clause above.  When it finishes execute the page lock query and the following results will be returned:

Index Black Ops Part 1-3

Now this time we have some blocking.  The query had a 10 second wait (and the 3 seconds that I took to get the other query started) – which resulted in about 7 seconds of waits on the index PK_Contact_ContactID.  So for that who time, nobody could access or edit any of the rows in the that were locked by the UPDATE statement.

Now take the query below and execute it in another query window.

BEGIN TRAN
UPDATE Person.Contact
SET NameStyle = NameStyle
WHERE ContactID = 1

WAITFOR DELAY '00:00:10'
COMMIT TRAN

As before, go back to the original query window and execute the SELECT query with the WHERE clause.  And when it finishes, execute the row lock query for (you guessed it) the results below:

Index Black Ops Part 1-4

And as you could have guessed, this will show the wait on the single row and then an accumulation of time on the index as well.

Conclusion

In the queries above, I’ve demonstrated how the locking and waits on indexes are tabulated.  The effect of these were shown in the queries.  Hopefully, you’ve seen through these examples how you can use sys.dm_db_index_operational_stats to identify indexes where locking pressure is occuring.

Relieving locking pressure isn’t always the easiest thing to do.  But it generally boils down to:

  • Reviewing queries utilizing the index to determine if they are performing optimally
  • Reviewing indexes to determine if you have the proper indexing in place

Two very broad areas, but by using the information above you can identify which indexes to look at and hone in on issues as they start arising in your index usage patterns.