I truly don’t know when it happened, but over the last while I have noticed that the percentage complete on indexes has disappeared when I run sp_whoisactive. It makes me so sad! I used that functionality often to track how things were progressing in my databases. At first I thought it was a version thing and it would come back, then I wondered if it was only when I use “ONLINE = ON”, but I am seeing it blank more and more. It has left me feeling like I am missing something and today, I finally did the digging to learn how to get that visibility back.
I started with trying this:
SELECT r.session_id, r.command, r.percent_complete, DATEADD(SECOND, r.estimated_completion_time / 1000, GETDATE()) AS EstimatedCompletion, r.wait_type, r.wait_time / 1000 AS WaitTimeSec, LEFT(t.text, 100) AS QueryTextFROM sys.dm_exec_requests rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) tWHERE r.command LIKE '%INDEX%'ORDER BY r.percent_complete DESC;
The results were a little disappointing. The percent_complete was zero and the EstimatedCompletion was the same time as when I ran the query:
Then I tried this:
SELECT session_id, physical_operator_name, SUM(row_count) AS RowsProcessed, SUM(estimate_row_count) AS EstimatedRows, CAST(SUM(row_count) * 100.0 / NULLIF(SUM(estimate_row_count), 0) AS DECIMAL(5,2)) AS PctCompleteFROM sys.dm_exec_query_profilesWHERE session_id IN ( SELECT session_id FROM sys.dm_exec_requests WHERE command LIKE '%INDEX%')GROUP BY session_id, physical_operator_nameORDER BY session_id;
These are the results! Look! PctComplete has information:
PctComplete was updating! I kept hitting refresh and it was nice to see progress happening:
Since I was rebuilding all the indexes on the table, I could tell when it moved to an index that was a little more complex:
How cool is that? It helps me to feel more calm especially when indexes are taking a long time and I have no idea if progress is being made.
The song for this post is Electric Light Orchestra – All Over the World and all over the SQL database world you can see some progress tonight!
Andrea Allred presents RoyalSQL 
[…] Andrea Allred goes searching for the truth: […]
Try again with a resumable index.
Thank you for the great call out. This works great if you can use it, but if you don’t have Enterprise or can’t use resumable for any other reason, this gives another solution.
Nice, I wanted to see the table being worked on so added this:
SELECT qp.session_id, OBJECT_NAME(qp.object_id, qp.database_id) AS TableName, er.command, qp.physical_operator_name, SUM(qp.row_count) AS RowsProcessed, SUM(qp.estimate_row_count) AS EstimatedRows, CAST(SUM(qp.row_count) * 100.0 / NULLIF(SUM(qp.estimate_row_count), 0) AS DECIMAL(5, 2)) AS PctComplete, SUBSTRING( st.text, (er.statement_start_offset / 2) + 1, ((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END – er.statement_start_offset ) / 2 ) + 1 ) AS ExecutingStatement FROM sys.dm_exec_query_profiles qp INNER JOIN sys.dm_exec_requests er ON qp.session_id = er.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st WHERE er.command LIKE ‘%INDEX%’ OR er.command LIKE ‘ALTER%’ GROUP BY qp.session_id, qp.object_id, qp.database_id, er.command, qp.physical_operator_name, st.text, er.statement_start_offset, er.statement_end_offset ORDER BY qp.session_id;
Brgds,
Arjan
Awesome, thank you for sharing!
I’ve found that DECIMAL(5,2) is on the smallish side when your stats is out.May I suggest an update to DECIMAL(15,2)?
My stats is out of order, so I had to change decimal(5,2) to decimal(15,2). Moved it to 15 as it was the least typing.Thank you for blogging about this finding. Most useful.
Happy to help!