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 QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE 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 PctComplete
FROM sys.dm_exec_query_profiles
WHERE session_id IN (
    SELECT session_id FROM sys.dm_exec_requests WHERE command LIKE '%INDEX%'
)
GROUP BY session_id, physical_operator_name
ORDER 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!