Get a blocking list for the objects & indexes using DMV

Yet another help from DMV I would like to share here, say on a SQL Instance you are getting low-level I/O and blocking intermittently. For the I/O issues using SYSMON (PERFMON) is ideal to get more information during the busy times and low-usage times (for memory too WhatUNeed2Check post).

For the blocking in SQL 2000 & 70 version you can take help of sp_blocker_pss80 stored procedure or by running PROFILER (Server side trace is ideal)  refer to this KBA224453 in this case. Similarly in SQL 2005 you can still use PROFILER and look for the Blocked Process Report event class that will obtain information to capture the task that has been blocked for more than a specified amount of time, for more information on this event class visit BOL too.

Also I would like to copy reply from a post that I have replied on SSP: If you are observing the blocking for sp_cursorclose(), then these are stored procedures are called by the SQL Server ODBC Driver or OLE DB Provider. You can see they are server-side cursors. By default within ADO, a server-side cursor occurs if the application specifies a keyset, dynamic or static cursor type on a Recordset object. If you are using ADO then test and set the CacheSize property of the Recordset object to a value that gets the result set rows back in a small number or a single call to sp_cursorfetch.

Additionally that doesn't mean cursors are always causing blocking in this case, you may be aware database & system locks dynamically occur all the time and not always they are necessarily bad things. If you finding few deadlocks during any transactions then check for process info using SP_WHO2 and look for blocking & blockedby columns. IN general the performance or blocking is affected for the applications due to the long running transactions.

Coming to using DMVs in SQL 2005 you could get more information such as list of objects (tables & SPs) and indexes that has most contention during the blocking. Using sys.dm_db_index_operational_stats DMV with following TSQL get such list :

--** Change the current database context to relevant database

declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
 , indexname=i.name, i.index_id 
 , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
 , row_lock_wait_in_ms
 , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
 ,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc
(this script is supplied by MSFT, I've modified as per my requirement)

Additionally I would like to remind the following 2 blogs in this site that helps to identify the relevant query & fine tune the performance:

IdentifySlowQueries & DetectTableFragmentation