Quick Scan Report – Reindexing During the Day

What this check looks for

Index rebuild and reorganize statements captured in the DBHealthHistory waits collection with a log time between 8:00am and 6:00pm server time. It needs the DBHealthHistory database, so it only reports on instances where history collection is running.

Why it matters

Index maintenance is the single most resource intensive scheduled thing most instances do, and it is being done while users are working.

A rebuild reads and rewrites the entire index. On a large table that means:

• Sustained I/O, competing directly with the reads the application needs.
• Buffer pool eviction. The rebuild pulls the whole index through memory, pushing out the pages the application had cached. Query response times stay poor after the rebuild finishes, while the cache refills.
• Transaction log generation, which is substantial. In full recovery an offline rebuild is fully logged, so the log grows and log backups get large.
• CPU, particularly with a high degree of parallelism.

And the locking, which is the part that turns slow into stopped.

An offline rebuild on a busy table during the day is an outage for that table for the duration. An online rebuild is much better but is not free: the brief schema modification lock at the start and end waits behind every open transaction on that table, and while it waits, every new request queues behind it. That is the classic pattern where an “online” rebuild produces a two minute total stall on a busy system.

The usual causes:

• A job scheduled in the window because somebody picked a time without considering it.
• A job that started overnight and ran long, which is the most common by far. It was scheduled for 1am and it is still going at 9am, which means the real problem is duration, not schedule.
• A maintenance plan with no time limit, working through every database in sequence until it finishes whenever it finishes.
• Ad hoc maintenance run by hand during the day, usually in response to a performance complaint, which makes the complaint worse before it makes it better.

How to confirm it yourself

What is running right now:

SELECT r.[session_id],
       r.[command],
       r.[status],
       r.[percent_complete],
       DATEADD(SECOND, r.[estimated_completion_time] / 1000, GETDATE()) AS [estimated_finish],
       r.[wait_type],
       r.[blocking_session_id],
       DB_NAME(r.[database_id]) AS [database_name],
       t.
  FROM sys.dm_exec_requests AS r WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE r.[command] IN ('ALTER INDEX', 'DBCC', 'RESTORE DATABASE', 'BACKUP DATABASE')
    OR t. LIKE '%ALTER INDEX%';

percent_complete and estimated_completion_time are populated for index operations, so this tells you whether to wait or to stop it.

What it is blocking, which is the reason this matters:

SELECT r.[session_id],
       r.[blocking_session_id],
       r.[wait_type],
       r.[wait_time] / 1000 AS [wait_seconds],
       DB_NAME(r.[database_id]) AS [database_name],
       t.
  FROM sys.dm_exec_requests AS r WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE r.[blocking_session_id] <> 0;

When and how long the maintenance job actually runs:

SELECT j.[name] AS [job_name],
       msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) AS [started],
       h.[run_duration],
       h.[run_status]
  FROM msdb.dbo.sysjobhistory AS h
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = h.[job_id]
 WHERE h.[step_id] = 0
   AND (j.[name] LIKE '%index%' OR j.[name] LIKE '%maint%' OR j.[name] LIKE '%reindex%')
 ORDER BY h.[run_date] DESC, h.[run_time] DESC;

run_duration is in HHMMSS format, so 23000 is two hours thirty minutes. Read the start time and the duration together: a job starting at 01:00 and running 080000 is finishing at 9am, which is the finding.

The schedule it is supposed to keep:

SELECT j.[name] AS [job_name],
       s.[name] AS [schedule_name],
       s.[active_start_time],
       s.[freq_type],
       s.[freq_interval]
  FROM msdb.dbo.sysjobs AS j
 INNER JOIN msdb.dbo.sysjobschedules AS js ON js.[job_id] = j.[job_id]
 INNER JOIN msdb.dbo.sysschedules   AS s  ON s.[schedule_id] = js.[schedule_id]
 ORDER BY j.[name];

How to fix it

Deal with the schedule if it is a schedule problem, and with the duration if it is a duration problem. They need different fixes.

If the job is scheduled during the day, move it. Pick the quietest window you actually have, which is worth measuring rather than assuming.

If the job is overrunning, which is more common, shortening it is the fix:

1. Reorganize rather than rebuild for moderate fragmentation. The conventional thresholds are reorganize between 5 and 30 percent, rebuild above 30. Reorganize is online, incremental and interruptible, and most indexes never need more.
2. Skip small indexes entirely. Anything under about 1000 pages gains nothing from maintenance and consumes the window. That single exclusion often halves the runtime.
3. Give the job a hard time limit. A maintenance script with a time boundary stops at 6am whether or not it finished, and resumes from where it left off next time. That converts an unbounded job into a bounded one, which is the important property.
4. Use online rebuilds where the edition allows it, and understand that the brief schema modification locks at start and end still need a quiet moment:

ALTER INDEX [IX_Orders_CustomerID] ON [dbo].[Orders]
  REBUILD WITH (ONLINE = ON, MAXDOP = 4, SORT_IN_TEMPDB = ON);

5. On SQL Server 2017 and later, use resumable rebuilds, which are exactly the tool for a job that runs out of window:

ALTER INDEX [IX_Orders_CustomerID] ON [dbo].[Orders]
  REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60);

-- later, in the next window
ALTER INDEX [IX_Orders_CustomerID] ON [dbo].[Orders] RESUME;

6. On 2014 and later, set WAIT_AT_LOW_PRIORITY so an online rebuild that cannot get its schema lock gives up instead of queueing every other request behind it:

ALTER INDEX [IX_Orders_CustomerID] ON [dbo].[Orders]
  REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES,
                                                   ABORT_AFTER_WAIT = SELF)));

That one option removes the worst failure mode of daytime online rebuilds.

7. Limit MAXDOP on the maintenance job so it does not take every scheduler while it runs.

If you have to stop one in progress, an offline rebuild rolls back, and the rollback takes roughly as long as the work done so far. A reorganize stops cleanly and keeps its progress, which is another reason to prefer it.

Do not simply stop doing index maintenance. The right answer is bounded maintenance in a quiet window, not none, and the fragmentation and statistics checks cover the other side of that.

How long it takes

About an hour to move the schedule or add the exclusions and limits. Rewriting an unbounded maintenance plan into a bounded script is longer, and worth it.

Related reports

Related checks

Frequently asked questions

Online rebuilds are online, so why does this matter? An online rebuild still needs a short schema modification lock at the start and the end. On a busy table that lock waits for open transactions, and everything else queues behind it while it waits. WAIT_AT_LOW_PRIORITY is the option that stops that being an outage.

The job is scheduled overnight but appears here anyway. Then it is overrunning. Look at the duration in job history rather than the schedule, and shorten the job rather than moving it.

Can I just stop doing index maintenance? Reduce it rather than stop it. Skipping small indexes and preferring reorganize usually leaves you with a fraction of the work and most of the benefit.

We run 24 hours a day and have no quiet window. Then resumable rebuilds with a duration limit are the right tool, run in small slices during the least busy hours, with WAIT_AT_LOW_PRIORITY so a slice never blocks.