SQL Server - Get query completion – detailed

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:

Results screen

Script results detailed description:

Column nameData typeDescription
session_idsmallintIdentifies the session associated with each active primary connection. Is not nullable.
databasenvarchar(128)Execution context database name.
login_namenvarchar(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_timedatetimeTime when session was established. Is not nullable.
program_namenvarchar(128)Name of client program that initiated the session. The value is NULL for internal sessions. Is nullable.
percent_completerealPercentage 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_usageintNumber of 8-KB pages of memory used by this session. Is not nullable.
cpu_timeintCPU time, in milliseconds, that was used by this session. Is not nullable.
statusnvarchar(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_readsbigintNumber of logical reads that have been performed on the session. Is not nullable.
readsbigintNumber of reads performed, by requests in this session, during this session. Is not nullable.
writesbigintNumber of writes performed, by requests in this session, during this session. Is not nullable.
transaction_isolation_levelsmallintTransaction isolation level of the session. 0 = Unspecified, 1 = ReadUncomitted, 2 = ReadCommitted, 3 = Repeatable, 4 = Serializable, 5 = Snapshot. Is not nullable.
blockedsmallintD 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_idsmallintD 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_addressvarchar(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_namenvarchar(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.
textnvarchar(max)Text of the SQL query. Is NULL for encrypted objects.
waittypebinary(2)Reserved.
waittimebigintCurrent wait time in milliseconds. 0 = Process is not waiting.
lastwaittypenchar(32)A string indicating the name of the last or current wait type.

Related posts

Leave a Reply