Default Maintenance Plan Reindex Task

What this check looks for

The maintenance plan package definitions stored on this instance are searched for the Rebuild Index task, which appears in the package XML as Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask.

The check is skipped on Amazon RDS.

Why it matters

The task has no idea which indexes need rebuilding, so it rebuilds all of them.

There is no fragmentation threshold, no page count floor and no way to add one. Every index in every selected database is rebuilt every time the plan runs, which produces four problems at once:

• It is enormously slow. Most maintenance windows that overrun are overrunning on this task, and on a large database it does not finish at all, so it is killed partway through and a different arbitrary subset of indexes gets rebuilt each night.
• It generates a full database worth of transaction log, every run. In full recovery that is a full database worth of log backups too, which is why log drives fill on maintenance nights and why log shipping secondaries fall behind on exactly those nights.
• It rebuilds tiny indexes pointlessly. An index of 40 pages cannot be meaningfully fragmented, because fragmentation below about a thousand pages is not measurable in any way that affects a query. Rebuilding them costs time and log and achieves nothing.
• In Standard edition it is offline. Online rebuild is an Enterprise feature until SQL Server 2019 made it available more widely, and the task’s default is offline, so every table is locked while its indexes are rebuilt.

It also updates statistics as a side effect of the rebuild, which is why plans that use it often also have an Update Statistics task doing the same work again immediately afterwards.

How to confirm it yourself

SELECT p.[name]        AS [plan_name],
       sp.[subplan_name],
       j.[name]        AS [job_name],
       j.[enabled]
  FROM msdb.dbo.sysmaintplan_plans AS p WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp WITH (NOLOCK)
         ON sp.[plan_id] = p.[id]
  LEFT JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK)
         ON j.[job_id] = sp.[job_id]
 ORDER BY p.[name];

How long it actually takes each night:

SELECT j.[name],
       h.[run_date], h.[run_time],
       h.[run_duration],
       h.[run_status]
  FROM msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK)
         ON j.[job_id] = h.[job_id]
 WHERE h.[step_id] = 0
 ORDER BY h.[run_date] DESC, h.[run_time] DESC;

And how much of that work was needed:

SELECT OBJECT_NAME(ips.[object_id]) AS [table_name],
       i.[name]                     AS [index_name],
       CAST(ips.[avg_fragmentation_in_percent] AS DECIMAL(5,1)) AS [fragmentation_pct],
       ips.[page_count]
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
 INNER JOIN sys.indexes AS i WITH (NOLOCK)
         ON i.[object_id] = ips.[object_id]
        AND i.[index_id]  = ips.[index_id]
 WHERE ips.[page_count] > 1000
 ORDER BY ips.[avg_fragmentation_in_percent] DESC;

Count how many rows come back above 30 percent, and compare that with how many indexes the plan rebuilds.

How to fix it

Replace the task with a script that checks fragmentation before acting. The standard answer, and a good one, is Ola Hallengren’s IndexOptimize, which is free, widely used and well tested. It reorganizes lightly fragmented indexes, rebuilds heavily fragmented ones, skips small ones entirely, and can be given a time limit so it stops rather than overrunning the window.

A sensible starting policy, whatever you use to implement it:

Then remove the Rebuild Index task from the maintenance plan and schedule the script instead. No downtime is needed for the change itself.

Update statistics separately. A rebuild updates statistics with a full scan for the indexes it touched, but the indexes it skipped still need their statistics maintained, and so do column statistics on tables with no indexes. IndexOptimize handles both.

How long it takes

About two hours to install and schedule a replacement and remove the task. The benefit shows up the same night.

Related reports

Related checks

Frequently asked questions

Is rebuilding every index actually harmful, or just wasteful? Mostly wasteful, but the waste has consequences: an overrunning window, a full log drive, and log shipping secondaries falling behind. In Standard edition, where the rebuild is offline, it is also an outage.

Ola’s scripts are already installed here. Then check whether they are scheduled and running. Installing them and leaving the maintenance plan in place is common enough to have its own check.

Should we use reorganize instead of rebuild? Neither, unconditionally. Reorganize is lighter and always online but does not update statistics and is less effective on heavy fragmentation. The point is choosing per index rather than applying one action to all.

We have a small database and the window is fine. Then the cost is low and this is a low priority. It is worth revisiting when the database grows, because the task’s runtime grows with the whole database rather than with the fragmentation.