The DMV for Day 27 is sys.dm_tran_locks, which is described by BOL as:
Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.
This DMV can be used to help identify locking and blocking issues. This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.
-- Look at active Lock Manager resources for current database SELECT request_session_id, DB_NAME(resource_database_id) AS [Database], resource_type, resource_subtype, request_type, request_mode, resource_description, request_mode, request_owner_type FROM sys.dm_tran_locks WHERE request_session_id > 50 AND resource_database_id = DB_ID() AND request_session_id <> @@SPID ORDER BY request_session_id;-- Look for blocking SELECT tl.resource_type, tl.resource_database_id, tl.resource_associated_entity_id, tl.request_mode, tl.request_session_id, wt.blocking_session_id, wt.wait_type, wt.wait_duration_ms FROM sys.dm_tran_locks as tl INNER JOIN sys.dm_os_waiting_tasks as wt ON tl.lock_owner_address = wt.resource_address ORDER BY wait_duration_ms DESC;
I have a couple of sample queries using sys.dm_tran_locks shown above. The first query shows lock types and their status by SPID, filtered by the current database, and eliminating the current connection and eliminating system SPIDs. The second query will give you some information about any blocking that may be occurring, instance-wide. You typically need to run both of these queries multiple times to catch blocking, unless you have pretty severe blocking going on.