How to Tracking query progress with Live Query Statistics
In SQL Server Some tasks that are running take a long time to run and it is sometimes difficult to tell whether these tasks are progressing or not.
With the help of below script we are able to track the percentage completion of tasks like:-
Database Backup and Restore
Various DBCC operations (SHRINKFILE, SHRINKDATABASE, CHECKDB, CHECKTABLE…)
Index Reorganizations
Rollback Operations etc..
SELECT r.session_id AS [Session_Id]
,r.command AS [command]
,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [%Complete]
,DATEADD(ms, r.estimated_completion_time, GetDate()) AS [EstimatedCompletionTime]
,r.total_elapsed_time / 1000.0 / 60.0 AS [ElapsedMin]
,r.estimated_completion_time / 1000.0 / 60.0 AS [EstimatedMin]
, r.estimated_completion_time / 1000.0 / 60.0 / 60.0 AS [EstimatedHours]
,r.wait_time
, ( SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE
WHEN r.statement_end_offset = - 1
THEN 1000
ELSE (r.statement_end_offset - r.statement_start_offset) / 2
END) TSQL_text
FROM sys.dm_exec_sql_text(sql_handle))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
Some useful column from dm_exec_requests DMV
| Column name | Data type | Description |
| session_id | smallint | ID of the session to which this request is related. Is not nullable. |
| request_id | int | ID of the request. Unique in the context of the session. Is not nullable. |
| start_time | datetime | Timestamp when the request arrived. Is not nullable. |
| status | nvarchar(30) | Status of the request. This can be one of the following: Background Running Runnable Sleeping Suspended Is not nullable. |
| sql_handle | varbinary(64) | Is a token that uniquely identifies the batch or stored procedure that the query is part of. Is nullable. |
| statement_start_offset | int | Indicates, in bytes, beginning with 0, the starting position of the currently executing statement for the currently executing batch or persisted object. Can be used together with the sql_handle, the statement_end_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request. Is nullable. |
| statement_end_offset | int | Indicates, in bytes, starting with 0, the ending position of the currently executing statement for the currently executing batch or persisted object. Can be used together with the sql_handle, the statement_start_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request. Is nullable. |
| plan_handle | varbinary(64) | Is a token that uniquely identifies a query execution plan for a batch that is currently executing. Is nullable. |
| database_id | smallint | ID of the database the request is executing against. Is not nullable. |
| user_id | int | ID of the user who submitted the request. Is not nullable. |
| blocking_session_id | smallint | ID of the session that is blocking the request. If this column is NULL or equal to 0, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified). -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is owned by a deferred recovery transaction. -4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions. |
| wait_type | nvarchar(60) | If the request is currently blocked, this column returns the type of wait. Is nullable. For information about types of waits, see sys.dm_os_wait_stats (Transact-SQL). |
| wait_time | int | If the request is currently blocked, this column returns the duration in milliseconds, of the current wait. Is not nullable. |
| last_wait_type | nvarchar(60) | If this request has previously been blocked, this column returns the type of the last wait. Is not nullable. |
| wait_resource | nvarchar(256) | If the request is currently blocked, this column returns the resource for which the request is currently waiting. Is not nullable. |
| percent_complete | real | Percentage of work completed for the following commands: 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 Is not nullable. |
| estimated_completion_time | bigint | Internal only. Is not nullable. |
| cpu_time | int | CPU time in milliseconds that is used by the request. Is not nullable. |
| total_elapsed_time | int | Total time elapsed in milliseconds since the request arrived. Is not nullable. |
| reads | bigint | Number of reads performed by this request. Is not nullable. |
| writes | bigint | Number of writes performed by this request. Is not nullable. |
| logical_reads | bigint | Number of logical reads that have been performed by the request. Is not nullable. |
| transaction_isolation_level | smallint | Isolation level with which the transaction for this request is created. Is not nullable. 0 = Unspecified 1 = ReadUncomitted 2 = ReadCommitted 3 = Repeatable 4 = Serializable 5 = Snapshot |
| row_count | bigint | Number of rows that have been returned to the client by this request. Is not nullable. |
| granted_query_memory | int | Number of pages allocated to the execution of a query on the request. Is not nullable. |
| query_hash | binary(8) | Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values. |
| query_plan_hash | binary(8) | Binary hash value calculated on the query execution plan and used to identify similar query execution plans. You can use query plan hash to find the cumulative cost of queries with similar execution plans. |
| parallel_worker_count | int | Applies to: SQL Server 2016 (13.x) and later. The number of reserved parallel workers if this is a parallel query. |
