sys.dm_exec_requests – Day 31 – One DMV a Day

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.

Transact-SQL

1

2

3

SELECTpercent_complete,

estimated_completion_time/1000/60.00ASest_min,*

FROMsys.dm_exec_requestsWHEREsession_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.

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.

Transact-SQL

1

2

3

4

5

6

7

8

--Session 57 - Lead Blocker

BEGINTRAN

UPDATEtest

SETid=5

WHEREid=4

--Session 56 - Victim

SELECT*FROMtest

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

Share This Story, Choose Your Platform!

Manohar Punna has started his career in 2008 as a core DBA and is now working as Senior DBA for S&P Capital IQ (McGraw Hill Financials). In his 5 years of game with SQL Server he has worked in service, support, insurance & banking and financial domains. As a Microsoft GTSC ex he bring great knowledge on SQL core internals. In his free time Manohar wants to explore everything else from travelling to sitting idle, from dieting to heavy 7 course meal, from full day sleeps to overnight ride in the city.