As 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.
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.
I 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.
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
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:
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:
This time the single row returned resulted in a row lock on a single row.
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:
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:
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.
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.