Retrieve information about query completion percent and estimated finish time. This is useful when running big databases restore or some hard DBCC database command. You want to check actual state or plan your activities based on estimated finish time.
Just put session id for process you want to get completion info about and run the script. Keep in mind that this is only estimation and real finish time can vary based on actual server load.
Warning: It is not showing all types of commands. Supported commands listed below:
- 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
Tip: Use WHERE clauses to focus on some program or session.
SELECT
des.session_id,
DB_NAME(er.database_id),
des.login_name,
des.login_time,
des.program_name,
er.percent_complete,
des.memory_usage,
des.cpu_time,
des.status,
des.logical_reads,
des.reads,
des.writes,
des.transaction_isolation_level,
sp.blocked,
er.blocking_session_id,
dec.client_net_address,
des.host_name,
dest.text,
sp.waittype,
sp.waittime,
sp.lastwaittype
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id
INNER JOIN sys.sysprocesses sp ON sp.spid = des.session_id
INNER JOIN sys.dm_exec_requests er ON er.session_id = des.session_id
CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
--WHERE des.program_name LIKE 'Microsoft Office%'
--WHERE des.session_id = [session id here]
Results
Script results screen:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| session_id | smallint | Identifies the session associated with each active primary connection. Is not nullable. |
| database | nvarchar(128) | Execution context database name. |
| login_name | nvarchar(128) | SQL Server login name under which the session is currently executing. For the original login name that created the session, see original_login_name. Can be a SQL Server authenticated login name or a Windows authenticated domain user name. Is not nullable. |
| login_time | datetime | Time when session was established. Is not nullable. |
| program_name | nvarchar(128) | Name of client program that initiated the session. The value is NULL for internal sessions. Is 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. |
| memory_usage | int | Number of 8-KB pages of memory used by this session. Is not nullable. |
| cpu_time | int | CPU time, in milliseconds, that was used by this session. Is not nullable. |
| status | nvarchar(30) | Status of the session. Possible values: Running - Currently running one or more requests Sleeping - Currently running no requests Dormant - Session has been reset because of connection pooling and is now in prelogin state. Preconnect - Session is in the Resource Governor classifier. Is not nullable. |
| logical_reads | bigint | Number of logical reads that have been performed on the session. Is not nullable. |
| reads | bigint | Number of reads performed, by requests in this session, during this session. Is not nullable. |
| writes | bigint | Number of writes performed, by requests in this session, during this session. Is not nullable. |
| transaction_isolation_level | smallint | Transaction isolation level of the session. 0 = Unspecified, 1 = ReadUncomitted, 2 = ReadCommitted, 3 = Repeatable, 4 = Serializable, 5 = Snapshot. Is not nullable. |
| blocked | smallint | D of the session that is blocking the request. If this column is NULL, 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 due to internal latch state transitions. |
| blocking_session_id | smallint | D 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. |
| client_net_address | varchar(48) | Host address of the client connecting to this server. Is nullable. Prior to V12 in Azure SQL Database, this column always returns NULL. |
| host_name | nvarchar(128) | Name of the client workstation that is specific to a session. The value is NULL for internal sessions. Is nullable. Security Note: The client application provides the workstation name and can provide inaccurate data. Do not rely upon HOST_NAME as a security feature. |
| text | nvarchar(max) | Text of the SQL query. Is NULL for encrypted objects. |
| waittype | binary(2) | Reserved. |
| waittime | bigint | Current wait time in milliseconds. 0 = Process is not waiting. |
| lastwaittype | nchar(32) | A string indicating the name of the last or current wait type. |