Sql-server – Synchronize Queries Retrieving Sessions And Locks From DMVs

dmvsql servert-sql

I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" (sys.dm_exec_requests, sys.dm_exec_sessions, etc.) and "Locks" (sys.dm_tran_locks). The output is similar to SQL Server Activity Monitor, but it displays more information.

Sometimes, a request appears in the Requests query, but it completes before the Locks query runs. For example, the Requests query may show SPID 51 is waiting on a lock resource, but the Locks query does not include any lock information for SPID 51. (I know about the wait_type and wait_resource columns from sys.dm_exec_requests.)

Is there a way to ensure that these two separate queries display a coherent snapshot of database activity?

I expect commercial database monitoring applications must encounter the same problem.

I have experimented with running these queries in SERIALIZABLE concurrency and added locking hints to the DMV joins, but the queries acquire no locks. I would not want to do this in production anyway.

The best ideas I have so far are:

  1. Run these queries simultaneously from different sessions.

  2. Join requests and locks together in one query. Considering that I have seen over 100,000 locks at once, this join would return a lot of duplicate request and session data, but it might work.

I am not familiar enough with extended events to know if they would work better– event pairing, maybe?

Best Answer

Not possible. These DMVs do not offer even self-consistency guarantees. For instance it is possible for a resource to appear in different mutually exclusive states, in the same run, in the same result set. If such DMVs would take the necessary steps to 'stabilize' the data during a scan the results would be disastrous for performance (imagine all locks being frozen in place while a scan over sys.dm_tran_locks is progressing).

Extended Events do offer a much better picture, but obviously is the picture of events, not of state. In theory is always possible to reconstruct state from the sequence of events knowing the initial state and achieve the same picture you get from the state dump (the DMVs) but that is only theory...

Related Question