SQL Server 2017 & newer have a new DMV, sys.index_resumable_operations, that show you the percent_completion for index creations and rebuilds. It works, but…only if the data isn’t changing. But of course your data is changing – that’s the whole point of doing these operations as resumable. If they weren’t changing, we could just let the operations finish.
Let’s see how it works to understand why the numbers aren’t really reliable:
Well, that’s … disappointingly inaccurate.
Here are the demo scripts if you want to give it a shot on your own systems:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
DROP TABLE IF EXISTS dbo.DiningRoomTable; GO CREATE TABLE dbo.DiningRoomTable (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing CHAR(1000)); INSERT INTO dbo.DiningRoomTable (Stuffing) SELECT 'Stuff' FROM sys.messages; GO CREATE INDEX IX_Stuffing ON dbo.DiningRoomTable(Stuffing) WITH (ONLINE = ON, RESUMABLE = ON); GO /* Run this immediately in another window: */ ALTER INDEX IX_Stuffing ON dbo.DiningRoomTable PAUSE; GO /* Check the percent complete: */ SELECT name, percent_complete FROM sys.index_resumable_operations; GO /* Double the number of rows in the table: */ INSERT INTO dbo.DiningRoomTable (Stuffing) SELECT 'Stuff' FROM sys.messages; GO /* Check the percent complete again: */ SELECT name, percent_complete FROM sys.index_resumable_operations; GO /* Triple 'em: */ INSERT INTO dbo.DiningRoomTable (Stuffing) SELECT 'Stuff' FROM sys.messages; GO /* Check the percent complete again: */ SELECT name, percent_complete FROM sys.index_resumable_operations; GO |
6 Comments. Leave new
This works fairly well
— https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
— add this line to index or insert tsql statement before running it
— SET STATISTICS PROFILE ON;
DECLARE @SPID INT = 76 ;
;WITH agg AS
(
SELECT SUM(qp.[row_count]) AS [RowsProcessed],
–SUM(qp.[estimate_row_count])
10554351100 AS [TotalRows], — change the value here to number of rows from the properties of the AHLDB temp table
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”)) AS [CurrentStep]
FROM sys.dm_exec_query_profiles qp
WHERE
qp.[physical_operator_name] IN (N’Table Scan’, N’Clustered Index Scan’, N’Sort’, ‘Index Scan’) — most common operation
–qp.[physical_operator_name] IN (N’Table Scan’, N’Clustered Index Scan’, N’Sort’, ‘Index Scan’,N’Clustered Index Insert’,N’Online Index Insert’,N’Compute Scalar’) — all operations
–qp.[physical_operator_name] IN ( N’Clustered Index Insert’) — single operation
–qp.[physical_operator_name] IN ( N’Online Index Insert’) — single operation
–qp.[physical_operator_name] IN ( N’Index Insert’) — single operation
–qp.[physical_operator_name] IN ( N’Sort’) — single operation
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;
— run this query below to find out what operations are running and adjust where clause above — if you get negative number then it has already gone past one operation to another –
— if you get a an error with zero divide in the above query, the operation in where clause is not running, choose another line or run the command below to find active operation
—
— select * from sys.dm_exec_query_profiles
Brian – that doesn’t work at all in the scenario described in the post, where I’m checking on a *paused* index operation.
Thanks for stopping by, though.
This script worked for me for quite some time now – but only for ONLINE rebuilds, don’t know whether it works for paused ones as well, build it back in 2010:
;WITH PartitionDups AS (
SELECT
[object_id]
, [index_id]
, [partition_number]
FROM sys.partitions
WITH (NOLOCK)
GROUP BY
[object_id]
, [index_id]
, [partition_number]
HAVING count(1) > 1
)
SELECT DISTINCT
DB_ID()
, I.[object_id]
, I.[index_id]
, CASE WHEN I.[index_id] = 0 THEN ”{HEAP}”
ELSE QUOTENAME( I.[name] )
END
, P1.[partition_id]
, P2.[partition_id]
, CONVERT( DECIMAL(18,15), ( P2.[rows] *100.0 ) /NULLIF( P1.[rows], 0 ) )
FROM PartitionDups PD
INNER JOIN sys.partitions P1
ON P1.[object_id] = PD.[object_id]
AND P1.[index_id] = PD.[index_id]
AND P1.[partition_number] = PD.[partition_number]
INNER JOIN sys.partitions P2
WITH (NOLOCK)
ON P2.[object_id] = PD.[object_id]
AND P2.[index_id] = PD.[index_id]
AND P2.[partition_number] = PD.[partition_number]
INNER JOIN sys.indexes I
WITH( NOLOCK )
ON I.[object_id] = PD.[object_id]
AND I.[index_id] = PD.[index_id]
WHERE
P1.[partition_id] P2.[partition_id]
AND P1.[rows] > P2.[rows]
Dennis – just a thought here, but why not test it before you suggest it as a solution? I show you exactly how to test it in the video. (Techniques like this simply won’t work with paused index builds, as you’ll learn when you test it.)
I have this SQL2017 DevEdtn with a test database at level 140.
Still the create index statement does not work !
CREATE INDEX IX_Stuffing ON dbo.DiningRoomTable(Stuffing)
WITH (DROP_EXISTING=OFF,ONLINE = ON, RESUMABLE = ON);
Msg 155, Level 15, State 1, Line 12
‘RESUMABLE’ is not a recognized CREATE INDEX option.
#ScratchedMyHead
Docs state:
RESUMABLE = { ON | OFF}
Applies to: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database
Right, this blog post is about sys.index_resumable_operations, which appeared in SQL Server 2017. The initial support was just for online index builds. If you want to test it on 2017, you can try something similar with alter index rebuild commands instead of a create.