Finding a SQL Server running Task with percentage complete using DMVs

 

Monitoring Query Progress in SQL Server Using Live Query Statistics

When working with SQL Server, it's common to encounter long-running operations. At times, it becomes difficult to determine whether these operations are making progress or are stuck. Fortunately, SQL Server provides tools to help monitor such tasks in real-time.

One such tool is the Live Query Statistics feature, which can be enhanced with a custom query to track the percentage completion of various operations. These include:

• Database backup and restore processes
• DBCC commands like SHRINKFILE, SHRINKDATABASE, CHECKDB, CHECKTABLE, etc.
• Index reorganizations
• Transaction rollbacks

The following T-SQL script provides real-time insights into these operations:

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) AS 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;

This query taps into Dynamic Management Views (DMVs) to provide current execution details and the progress of each request.

---

Key Columns in sys.dm_exec_requests

The query above pulls data from the sys.dm_exec_requests DMV. Below are some important columns that provide valuable insights:

Column Name	Data Type	Description
session_id	smallint	Identifies the session related to the request.
request_id	int	Unique ID of the request within the session.
start_time	datetime	Time when the request started.
status	nvarchar(30)	Current status: Running, Suspended, Sleeping, etc.
sql_handle	varbinary(64)	Identifies the SQL batch or procedure.
statement_start_offset / statement_end_offset	int	Byte offset positions to isolate the running T-SQL statement.
plan_handle	varbinary(64)	Token for the query execution plan.
database_id	smallint	Database context in which the query is executing.
user_id	int	ID of the user who initiated the request.
blocking_session_id	smallint	ID of any blocking session, if applicable.
wait_type	nvarchar(60)	Type of wait the request is experiencing.
wait_time	int	Duration (in milliseconds) the request has been waiting.
last_wait_type	nvarchar(60)	The last type of wait, if any.
wait_resource	nvarchar(256)	Resource the request is currently waiting for.
percent_complete	real	Percentage of task completion.
estimated_completion_time	bigint	Time remaining in milliseconds (internal).
cpu_time	int	CPU time used in milliseconds.
total_elapsed_time	int	Total time elapsed since the request started.
reads / writes / logical_reads	bigint	Counts of reads and writes performed by the request.
transaction_isolation_level	smallint	Isolation level used (e.g., ReadCommitted, Serializable).
row_count	bigint	Number of rows returned to the client.
granted_query_memory	int	Number of pages granted for query execution.
query_hash / query_plan_hash	binary(8)	Hash values for identifying similar queries and plans.
parallel_worker_count	int	Number of parallel workers, applicable in newer versions.

For a full reference, see Microsoft’s documentation on sys.dm_exec_requests.

---

By using this method, DBAs can proactively monitor long-running processes and gain better visibility into SQL Server's performance and behavior during critical operations.