:::: MENU ::::

Using sys.dm_exec_requests to monitor slow response of SQL Server

  • May 07 / 2009
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, T-SQL Scripts

Using sys.dm_exec_requests to monitor slow response of SQL Server

It is very important for a DBA to monitor the response time of SQL Server to connected users. Minute details like read, write and resource consumption may be analyzed later but first priority should be to point out slow response time. Several DMVs and other system data may be utilized to monitor the slow response of SQL Server. For this post I have

 
USE master   
 GO   
 SELECT start_Time,DB_NAME(database_id),st.TEXT,  
 [Status], command, wait_type ,wait_time,  
 wait_resource, last_wait_type,  
 CASE transaction_isolation_level  
 WHEN 0 THEN 'unspecified'   
 WHEN 1 THEN 'ReadUncomitted'  
 WHEN 2 THEN 'ReadCommitted'  
 WHEN 3 THEN 'Repeatable'  
 WHEN 4 THEN 'Serializable'  
 WHEN 5 THEN 'Snapshot'  
 END AS [TRANSACTION ISOLATION],executing_managed_code   
 FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 
 ORDER BY wait_type   
 GO  

sys.dm_exec_requests result
The field [wait_type] is important if some wait type is given for any transaction. And it become critical if wait type is due to any lock. Such wait types will begin with LCK prefix. T-SQL statement being used is also given, so DBA may get a clue that which statement is experiencing slow response.
Above script just provides a clue. If you note any problem then further fields from sys.dm_exec_requests may be included and other ways to track down the problem may be used.
For details of fields in sys.dm_exec_requests please click here
As a DMV is being used in above script so it is obvious that above script will be used for SQL Server 2005 and later.

Consult us to explore the Databases. Contact us