http://www.sqlservercentral.com/blogs/stratesql/2010/09/15/index-black-ops-part-1-_1320_-locks-and-blocking/

Printed 2014/11/01 05:09AM

Index Black Ops Part 1 – Locks and Blocking

2010/09/15

Close EncountersAs I mentioned in the 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.

Meat and Potato Columns

Italian meat-balls #3I said meat and potato columns, so here we are.  These are the columns that you can

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(100. * 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(100. * 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:

image

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:

image

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:

image

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:

image

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:

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.

Related posts:

  1. Snapshots of Index Contention
  2. Monitoring Index Contention With DMFs
  3. Analyze This – Analyze Your Indexes – Part 6


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.