Basics of sys.dm_exec_requests

Basics of sys.dm_exec_requests
5 (100%) 4 votes

Performance monitoring and troubleshooting in SQL Server is a vast topic. In SQL Server 2005, dynamic management views, also known as DMV’s, have been introduced and became an essential helping tool for diagnosing SQL Server performance problems. At the same time, we can use dynamic management views for Azure SQL Database. Some of them can differ from SQL Server on-premise database but the logic of work is still the same. Microsoft has very good documentation about dynamic management views. The only thing,  you need to be careful about the version and product validation of dynamic management views. Such as sys.dm_exec_request is available for SQL Server 2008 and later versions and for Azure SQL database but sys.dm_db_wait_stats is valid only for Azure SQL database.

In this post, we will talk about the basics of the sys.dm_exec_request. sys.dm_exec_requests is a dynamic management view which returns only the currently executing requests. It means that when you run sys.dm_exec_requests query, it snapshots request which is running in that time and does not include any historical data. Therefore, this view is very handy for monitoring real-time requests. In other words, it gives the answer to “what is going on in my server just now?” This view includes a lot of details but we will discuss the most important ones.

session_id: This value defines a session identification number. In SQL Server, session IDs which are equal to or less than 50 are dedicated to the background process.

start_time: This value defines the start date and time of the request.

status: This value defines a status of the request. The available statuses are:

  • background
  • running
  • runnable
  • sleeping
  • suspended

background: This status type defines the background processes. Some of them are LAZY WRITER, CHECKPOINT, and LOG WRITER.

running: This status type defines that request is processing by CPU.

runnable: This status type can be simply defined as a request which is waiting in the CPU queue for running. If you detect a lot of runnable process in sys.dm_exec_requests, it can be a symptom of CPU pressure. This metric is not enough to diagnose this CPU performance issue. Because of this reason, we need to support this case with more evidence. This is significant for us to prove our theory. The sys.dm_os_wait_stats dynamic management view includes a column which is signal_wait_time_ms. This column value can be an assist to determine the CPU issue. The following query shows us the percentage of Signalwait. If this metric has a high value, most probably you are facing a CPU performance problem. You can deepen your review in this way.

suspended: This status type defines the waiting process of some resource. It can be I/O, LOCK, and LATCH etc. As mentioned above, we can use the sys.dm_os_wait_stats dynamic management view to detect wait_time_ms.

sleeping: It means that the request is connected to SQL Server but not currently running any commands.

command: This column defines a type of command which is being executed. At the same time, we can find additional information for particular commands which is a completion ratio. According to the online books, these are;

ALTER INDEX REORGANIZE

AUTO_SHRINK option with ALTER DATABASE

BACKUP DATABASE

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKTABLE

DBCC INDEXDEFRAG

DBCC SHRINKDATABASE

DBCC SHRINKFILE

RECOVERY

RESTORE DATABASE

ROLLBACK

TDE ENCRYPTION

Demo:

  • Connect the master database and start the backup for any database.
    Tip: When you take the database backup to the “NUL” device, SQL Server does not write a backup file anywhere and you avoid the deletion of a test backup file. But don’t use this command in your production environment. It can cause to break the LSN chain.

Execute the following  query:

sql_handle: This value defines the SQL statement of the request. But this value is in the bitmap format. For this reason, we need to use the sys.dm_exec_sql_text table-valued function to convert the value into meaningful text.

database_id: This value defines which database makes the request. We can join this field with sys.databases to get the database name.

wait_type: This value defines the current wait type of the request. If the duration of the query execution takes longer than expected, you can check and determine the wait type of query.

transaction_isolation_level: This value defines the transaction level of the submitted query:

0=Unspecified

1=ReadUncomitted

2=ReadCommitted

3=Repeatable

4=Serializable

5=Snapshot

blocking_session_id: It is the id of the session which is blocking the request. If this column is NULL, the request has not blocked any session.

Demo:

  • Open SQL Server Management Studio and execute the following query.

Open a new query window and execute the following query.

Open another new query window and run this DMV query.

With this demonstration, we found out the reason why the second query is blocked and which session is blocked the request. When you run sp_BlitzWho 65, you can find out the details of blocking and the blocked session details.

In this demonstration, we retrieved the details of blocking and blocked sessions and at the same time, we got the details about these sessions. This is a very basic demonstration but it shows how the issue can be resolved.

In this post, we talked about sys.sys.dm_exec_requests. This dynamic management view gives us the flexibility to get a snapshot during the current rung of a request. Also, these details data can assist or guide us through the process of discovering the problem.

References

Esat Erkeç

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç

Latest posts by Esat Erkeç (see all)