DBA tool--dmv--How to know how long a TSQL statement has been running

Original: DBA tool--dmv--How to know how long a TSQL statement has been running

The DBA usually wants to know how long the running statement has been executed? You can use SQL Server Profiler to capture the start time of a statement and compare it to an existing time, but in a production environment there is usually a negative impact on performance, so it is generally not recommended that you use it in a production environment without unnecessary assumptions.

At this point, you can use the DMV to query:

There is an important field in sys.dm_exec_requests: Start_time, which represents the start time of the request, a batch is a request, a request corresponds to a task, and if the batch is concurrent, it corresponds to multiple tasks. The following script does not consider concurrency scenarios. There is a field in Sys.dm_os_workers in the DMV that indicates the time (in ticks) that the thread (worker) gets to the task.

Because sys.dm_exec_requests does not contain statement text, it needs to be sys.dm_exec_sql_text converted sql_handle. Then get the execution plan from sys.dm_exec_query_plan. It also outputs which sentence is being executed for the specific stmt. This is especially useful for a batch with multiple statements:

DECLARE @ms_per_tick DECIMAL (6)--millisecond per tickselect @ms_per_tick = 1.0 * DATEDIFF (millisecond, Sqlserver_sta                                                     Rt_time, GETDATE ())/(ms_ticks -sqlserver_start_time_ms_ticks) from Sys. [Dm_os_sys_info];--select @ms_per_tickSELECT req.session_id, Req.start_time request_start_time, ((Sele CT ms_ticks from Sys.dm_os_sys_info)-workers.task_bound_ms_ticks) * @ms_per_tick ' Ms_since_tas K_bound ', DATEDIFF (MS, Req.start_time, GETDATE ()) ' Ms_since_request_start ', tasks.task_state, work Ers.state worker_state, Req.status request_state, St.text, SUBSTRING (St.text, Req.statement_star T_OFFSET/2) + 1, (Req.statement_end_offset WHEN-1 then datalength (st.t EXT) ELSE Req.statement_end_offset End-req.statement_stArt_offset)/2) + 1) as stmt, Qp.query_plan, Req.*from sys.dm_exec_requests req left JOIN sys . dm_os_tasks tasks on tasks.task_address = Req.task_address left joins sys.dm_os_workers workers on Tasks.task_addre SS = workers.task_address Cross Apply sys.dm_exec_sql_text (Req.sql_handle) St cross apply Sys.dm_exec_query_ Plan (Req.plan_handle) qpwhere (req.session_id > OR req.session_id is NULL) go

The above @ms_per_tick is used to calculate a tick equivalent to how many MS (milliseconds), usually a tick is basically a millisecond. The output is as follows:

DBA tool--dmv--How to know how long a TSQL statement has been running