SSTS Blog

Some news and tidbits that we share

SQL Server Index Rebuild Progress

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Aug 31 in Blog 0 Comments

Here is a query to monitor index rebuild progress:

 

;WITH cte AS
(
SELECT
object_id,
index_id,
partition_number,
rows,
ROW_NUMBER() OVER(PARTITION BY object_id, index_id, partition_number ORDER BY partition_id) as rn
FROM sys.partitions
)
SELECT
object_name(cur.object_id) as TableName,
cur.index_id,
cur.partition_number,
PrecentDone =
CASE
WHEN pre.rows = 0 THEN 0
ELSE
((cur.rows * 100.0) / pre.rows)
END,
pre.rows - cur.rows as MissingRows
FROM cte as cur
INNER JOIN cte as pre on (cur.object_id = pre.object_id) AND (cur.index_id = pre.index_id) AND (cur.partition_number = pre.partition_number) AND (cur.rn = pre.rn +1)
ORDER BY 4

Tags: Untagged
Hits: 34626

About the author

SSTS

Server Side Technology Solutions is a consulting firm that specializes in database design, development and support.

Comments

Please login first in order for you to submit comments