Wait Events - Sys.dm exec requests

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Monitoring & Tuning - Wait Events

SQL Server 2005 exposes a wealth of "under-the-hood" information through dynamic management views and dynamic management functions, often abbreviated as DMV's and DMF's. DMV's and DMF's display a snapshot of the server's state so their output can vary widely from one execution to next. DMV's are particularly useful for monitoring and tuning memory and CPU pressures.

Sys.dm_exec_requests



This DMV shows each connection on the given instance of SQL Server. User connections (as opposed to internal connections executing background tasks) have session ids greater than 50. Sleeping status indicates that the connection is active and SQL Server is waiting for the next SQL statement from this connection. The following table documents the columns returned by sys.dm_exec_requests:

Column Explanation
Session_id Session identifier which you can also obtain from master.dbo.sysprocesses or by executing sp_who2 system procedure.
Request_id Request identifier. Each session can submit multiple requests. Each request identifier is unique per connection.
Start_time Date and time when the request was submitted.
Status

Request can have one of the following values for it's current status:

  • Background - reserved for internal background requests. These will always have session identifier less than or equal to 50.
  • Running - the request is currently being executed.
  • Runnable - the request is placed in runnable queue. Resources are available but request is waiting for an available scheduler.
  • Sleeping - connection is open but either no SQL statement has been submitted or the submitted statements have been executed. SQL Server is waiting for the next request from this connection.
  • Suspended - request is waiting on resource (is in the waiting queue)
Command

This column shows the type of command being submitted, but not the actual statement. Could have one of the following values for user processes:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • BACKUP LOG
  • BACKUP DB
  • WAITFOR
  • DBCC


For internal processes the column could also take the following values:

  • BRKR EVENT HNDLR
  • BRKR TASK
  • LAZY WRITER
  • LOCK MONITOR
  • RESOURCE MONITOR
  • SIGNAL HANDLER
  • TASK MANAGER
  • TRACE QUEUE TASK
Sql_handle Bitmap value of the Transact-SQL command submitted through the given request. You can use the value of this column along with the sys.dm_exec_sql_text DMV to retrieve the actual command in humanly readable format.
Statement_start_offset If the current request is executing a stored procedure or a batch of statements, you can use the value of this column to obtain the character number where currently executing statement starts. This way you can identify exactly which statement of the stored procedure is experiencing the wait instead of having to debug the entire stored procedure or batch of statements.
Statement_end_offset If the current request is executing a stored procedure or a batch of statements, you can use the value of this column to obtain the character number where currently executing statement ends. This way you can identify exactly which statement of the stored procedure is experiencing the wait.
Plan_handle Bitmap value of the query execution plan.
Database_id Database identifier against which the current request was submitted.
User_id User identifier of the user who submitted the request.
Connection_id Globally unique connection identifier.
Blocking_session_id

If wait is caused by blocking this column contains the session identifier of the blocking connection. This column displays negative values in special circumstances:

  • -2 if the blocking resource is owned by orphaned distributed transaction.
  • -3 if the blocking resource is owned by a deferred database recovery transaction.
  • -4 if the session id of the blocking latch owner could not be determined at the time the DMV was queried. This could happen due to internal latch state changes.
Wait_type

Wait type that precludes the current session request from being executed.
Wait_time Total wait time measured in milliseconds. This number of milliseconds includes signal wait time; meaning the amount of time the session has spent in the runnable queue after necessary resources became available to it.
Last_wait_type If the request has previously been blocked this column displays the previous wait type.
Wait_resource The resource for which the current request is waiting.
Open_transaction_count Number of open transactions for the current request.
Open_resultset_count Number of result sets being created for the current request.
Transaction_id Identifier for the transaction on which the request is executing.
Context_info Session's execution context.
Percent_complete This column is intended to report percent of work completed for the current request. Unfortunately, you cannot use this column to estimate how long the query will take or to effectively determine its progress. Do not use this column for your troubleshooting or query analysis.
Estimated_completion_time Do not use this column for your troubleshooting or query analysis.
Cpu_time CPU time used by the current request.
Total_elapsed_time Total time elapsed since the request was submitted.
Scheduler_id Identifier of the scheduler, which will execute the request.
Task_address Memory address of the task, which will execute the request.
Reads Number of read operations performed by the request.
Writes Number of write operations performed by the request.
Logical_reads Number of logical (as opposed to physical) read operations performed by the request.
Text_size Value of TEXTSIZE setting for the current request. Controls the size of large varying length data type columns returned by the query, if applicable.
Language Value of LANGUAGE setting for the request.
Date_format Value of DATEFORMAT setting for the request.
Date_first Value of DATEFIRST setting for the request.
Quoted_identifier Value of QUOTED_IDENTIFIER setting for the request.
Arithabort Value of ARITHABORT setting for the request.
Ansi_null_dflt_on Value of ANSI_NULL_DFLT_ON setting for the request.
Ansi_defaults Value of ANSI_DEFAULTS setting for the request.
Ansi_warnings Value of ANSI_WARNINGS setting for the request.
Ansi_padding Value of ANSI_PADDING setting for the request.
Ansi_nulls Value of ANSI_NULLS setting for the request.
Concat_null_yields_null Value of CONCAT_NULL_YIELDS_NULL setting for the request.
Transaction_isolation_level Transaction isolation level for the request.
Lock_timeout Lock time-out in milliseconds as specified for the request.
Deadlock_priority Value of DEADLOCK_PRIORITY setting for the request.
Row_count Number of rows returned to the client by the request.
Prev_error Last error that occurred during the execution of the request.
Nest_level Current nesting level of transaction that is executing on the request.
Granted_query_memory Number of pages allocated to execute the query request.
Executing_managed_code Shows whether current request is executing Common Language Runtime (CLR) code.

We can use the value in sql_handle column to retrieve the actual Transact-SQL statement executed on the waiting connection, as follows: 

SELECT [text] FROM  sys.dm_exec_sql_text(0x02000000A38B54032E997B5CF15CC67442FF621ECD465EA3)

Results: 

Text    
SELECT AccountDescription FROM DimAccount WHERE AccountKey = 2
Retrieved from "http://sqlserverpedia.com/wiki/Wait_Events_-_Sys.dm_exec_requests"