SQL Server track progress of command

env: Windows Server 2016

        SQL Server 2016 SP2

在"SQL Server estimate time create(alter) index phase1"我們利用DMV"sys.dm_exec_query_profiles"查詢create(alter) index執行的即時進度，這次要利用這個方法來查詢一般語法執行的進度。

 

1.在一般查詢的語法執行前執行以下指令

command:

在單一session啟用STATISTICS PROFILE

例如:

SET STATISTICS PROFILE ON;

 

另外一個方式是啟用TRACE FLAG 7412

DBCC TRACEON (7412, -1);  

GO

 

2.執行以下語法查詢執行進度

執行以下語法會發現有些步驟完成度超過100%，是實際資料筆數與統計資料的筆數產生落差所致。

command:

USE MASTER
GO

SET NOCOUNT  ON
SET LOCK_TIMEOUT 10000

DECLARE @SPID INT --= 80;
DECLARE @SQLSTR NVARCHAR(MAX)
DECLARE @Conditions NVARCHAR(MAX)

SELECT 
@SPID=SPID
FROM SYSPROCESSES SP 
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE ER.command IN ('INSERT')
ORDER BY CPU DESC

SELECT @Conditions =stuff((
SELECT
CAST(physical_operator_name AS NVARCHAR) + ''', N'''
FROM sys.dm_exec_query_profiles   
WHERE session_id=@SPID
GROUP BY node_id,physical_operator_name  
FOR XML PATH('')),1,0,'')

SET @Conditions='(N''' +LEFT(@Conditions, LEN(@Conditions)-4) +')'
--PRINT @Conditions

SET @SQLSTR='SELECT 
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0, [physical_operator_name], N''<Transition>'')) AS [CurrentStep],
SUM(qp.[estimate_row_count]) AS [TotalRows],
SUM(qp.[row_count]) AS [RowsProcessed],
(SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])) AS [RowsLeft], 
CONVERT(DECIMAL(10, 2), ((SUM(qp.[row_count]) * 1.0) / SUM(qp.[estimate_row_count])) * 100) AS [CompletePercent],
CONVERT(varchar, DATEADD(ms, ((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) * 1000, 0), 114)  AS [AlreadyRunTime_hhmiss], 
CONVERT(varchar, DATEADD(ms, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])))* 1000, 0), 114)  AS [RemainTimeToReady_hhmiss],
DATEADD(SECOND, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count]))), GETDATE()) AS [EstimatedCompletionTime]
FROM sys.dm_exec_query_profiles qp 
WHERE qp.[physical_operator_name] IN ' + @Conditions +'
    AND  
qp.[session_id] = @SPID'

--PRINT @SQLSTR
EXECUTE SP_EXECUTESQL @SQLSTR, N'@SPID INT',
@SPID=@SPID

--Run this in a different session than the session in which your query is running. 
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT
node_id,
physical_operator_name, 
SUM(row_count) row_count, 
SUM(estimate_row_count) AS estimate_row_count, 
CAST(SUM(row_count)*100 AS float)/IIF(SUM(estimate_row_count)>0, SUM(estimate_row_count), 1)  as estimate_percent_complete 
FROM sys.dm_exec_query_profiles   
WHERE session_id=@SPID
GROUP BY node_id,physical_operator_name  
ORDER BY node_id desc;