sys.dm_exec_requests – Day 31 – One DMV a Day

Hello Geeks and welcome to the Day 31 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Today I am going to cover the next DMV in the execution related DMVs. sys.dm_exec_requests will cover most of the columns which are related to requests. A request is an operation which needs to be executed inside a batch. A batch can have multiple requests which in turn can have multiple tasks. I have covered how the execution of a task is performed in this blog post.

I will cover what information this DMV provides. This DMV gives a picture of what is happening on my instance at this point in time. It provides information related to the request status, wait information, handles to check statement running and execution plan, request attributes set in the session, time taken and activity, command type, and many other details.

The most important information which sys.dm_exec_requests can give is the percent_complete. The column percent_complete and estimated_completion_time is calculated based on the current progress of the request. These values are valid for only certain set of requests. These include commands like backup, restore, recovery, shrink, checkdb, checktable, index reorganize, rollback, TDE encryption, etc. You can use the below command to track the progress of any of the above operation.

SELECT percent_complete, 
	estimated_completion_time/1000/60.00 AS est_min, * 
FROM sys.dm_exec_requests WHERE session_id = <spid>

Sys.dm_exec_requests in combination with sys.dm_exec_sessions can be used to troubleshoot other issues like blocking. The scope of this DMV usage is broad and can only be explored on usage. So I will leave the imagination of its usage to your DB brains. Below is one query you can use to detect lead blocker in case of blocking.

;WITH b AS
(
	SELECT distinct blocking_session_id AS blockers
	FROM sys.dm_exec_requests
)
SELECT session_id, 
	0 AS blocking_session_id,
	Block_Desc = 'Lead'
FROM sys.dm_exec_sessions es
INNER JOIN b
	ON es.session_id = b.blockers
WHERE NOT EXISTS(SELECT 1 FROM sys.dm_exec_requests er WHERE es.session_id = er.session_id)
	OR EXISTS(SELECT 1 FROM sys.dm_exec_requests er WHERE es.session_id = er.session_id AND er.blocking_session_id = 0)

UNION ALL

SELECT session_id, 
	blocking_session_id,
	Block_Desc = 'Victim'
FROM sys.dm_exec_requests 
WHERE blocking_session_id != 0

In the above query I have used two conditions in the WHERE clause. Can you guess why? It is because a session can cause blocking with an open transaction. The request may have been completed but it is still not committed. This does not show up in sys.dm_exec_requests. For example in the above case I have run the below commands in session 57 and 56 to create the blocking. You can check when the blocking is happening in sys.dm_exec_requests and 57 will not show up.

--Session 57 - Lead Blocker
BEGIN TRAN
UPDATE test 
	SET id = 5 
WHERE id = 4

--Session 56 - Victim
SELECT * FROM test

Tomorrow I will be covering another execution related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook