相信很多SQL Server DBA或開發人員在重建或重組大表索引時，都會相當鬱悶，不知道索引重建的進度，這個對於DBA完全是一個黑盒子，對於系統負載非常大的系統或維護視窗較短的系統，你會遇到一些挑戰。例如，你創建索引的時候，很多會話被阻塞，你只能取消創建索引的任務。查看這些索引維護操作的進度、預估... ...
相信很多SQL Server DBA或開發人員在重建或重組大表索引時，都會相當鬱悶，不知道索引重建的進度，這個對於DBA完全是一個黑盒子，對於系統負載非常大的系統或維護視窗較短的系統，你會遇到一些挑戰。例如，你創建索引的時候，很多會話被阻塞，你只能取消創建索引的任務。查看這些索引維護操作的進度、預估時間對於我們有較大的意義，需要根據這個做一些決策。下麵我們來看看看看如何獲取CREATE INDEX、ALTER INDEX REBUILD、ALTER INDEX ORGANIZE的進度。
索引重組
從SQL Server 2008開始，有個DMV視圖sys.dm_exec_requests，裡面有個欄位percent_complete表示以下命令完成的工作的百分比，這裡面就包括索引重組（ALTER INDEX REORGANIZE），這其中不包括ALTER INDEX REBUILD，可以查看索引重組（ALTER INDEX ORGANIZE）完成的百分比。也就是說在SQL Server 2008之前是無法獲取索引重組的進度情況的。
| percent_complete | real | Percentage of work completed for the following commands: |
測試環境：SQL Server 2008 、 2017 RTM CU13
SELECT er.session_id ,er.blocking_session_id ,
er.status ,
er.command ,
DB_NAME(er.database_id) DB_name ,
er.wait_type ,
et.text SQLText ,
er.percent_complete
FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) etWHERE er.session_id = 57 AND er.session_id <> @@SPID;
索引重建
上面DMV視圖sys.dm_exec_requests是否也可以查看索引重建的進度呢? 答案是不行，測試發現percent_complete這個進度一直為0，那麼要如何查看索引重建（INDEX REBUILD）的進度呢？
不過自SQL Server 2014開始，SQL Server提供了一個新特性：sys.dm_exec_query_profiles，它可以實時監控正在執行的查詢的進度情況（Monitors real time query progress while the query is in execution）。當然，需要啟用實時查詢監控才行。一般只需啟用會話級別的實時查詢監控，可以通過啟用SET STATISTICS XML ON; 或SET STATISTICS PROFILE ON;開啟。而從SQL Server 2016 (13.x)SP1 開始，您可以或者開啟跟蹤標誌 7412或使用 query_thread_profile 擴展的事件。下麵是官方文檔的描述：
In SQL Server 2014 (12.x) SP2 and later use SET STATISTICS PROFILE ON or SET STATISTICS XML ON together with the query under investigation. This enables the profiling infrastructure and produces results in the DMV for the session where the SET command was executed. If you are investigating a query running from an application and cannot enable SET options with it, you can create an Extended Event using the query_post_execution_showplan event which will turn on the profiling infrastructure.
In SQL Server 2016 (13.x) SP1, you can either turn on trace flag 7412 or use the query_thread_profile extended event.
--Configure query for profiling with sys.dm_exec_query_profiles
SET STATISTICS PROFILE ON;
GO
--Or enable query profiling globally under SQL Server 2016 SP1 or above
DBCC TRACEON (7412, -1);
GO
ALTER INDEX Your_Index_Name ON Your_Table_Name REBUILD;
GO
DECLARE @SPID INT = 53;
;WITH agg AS
(
SELECT SUM(qp.[row_count]) AS [RowsProcessed],
SUM(qp.[estimate_row_count]) AS [TotalRows],
MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
[physical_operator_name],
N'<Transition>')) AS [CurrentStep]
FROM sys.dm_exec_query_profiles qpWHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Sort' , N'Index Scan')
AND qp.[session_id] = @SPID), comp AS(
SELECT *, ([TotalRows] - [RowsProcessed]) AS [RowsLeft], ([ElapsedMS] / 1000.0) AS [ElapsedSeconds] FROM agg)
SELECT [CurrentStep],[TotalRows],
[RowsProcessed],
[RowsLeft],
CONVERT(DECIMAL(5, 2),
(([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],[ElapsedSeconds],
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft], DATEADD(SECOND,(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
GETDATE()) AS [EstimatedCompletionTime]FROM comp;
註意事項：在SQL Server 2016 SP1之前，如果要使用sys.dm_exec_query_profiles查看索引重建的進度，那麼就必須在索引重建之前設置SET STATISTICS PROFILE ON or SET STATISTICS XML ON。 而自
SQL Server 2016 SP1之後，可以使用DBCC TRACEON (7412, -1);開啟全局會話的跟蹤標記，或者開啟某個會話的跟蹤標記，當然如果要使用sys.dm_exec_query_profiles查看索引重建的進度，也必須開啟7412跟蹤標記
，然後重建索引，否則也沒有值。
註意事項：：索引重組時，sys.dm_exec_query_profiles中沒有數據。所以sys.dm_exec_query_profiles不能用來查看索引重組的進度。
新建索引
<
