Default Maintenance Plan Defragment Index Task

What this check looks for

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

The check is skipped on Amazon RDS.

Why it matters

Reorganize is the gentler of the two index operations, which makes this the quieter version of the same mistake. It is always online, it can be stopped partway through without losing the work already done, and it uses far less log than a rebuild. None of that makes it free, and the task still applies it to every index regardless of need.

Three things specifically:

• No fragmentation threshold. Every index in every selected database is reorganized on every run, including the ones at two percent fragmentation and the ones with forty pages, where the operation cannot achieve anything measurable.
• It updates no statistics at all. This is the difference that catches people out. A rebuild updates statistics with a full scan as a side effect; a reorganize does not touch them. A plan that replaced its rebuild task with a reorganize task, usually to shorten the maintenance window, silently stops maintaining statistics, and the resulting bad cardinality estimates show up weeks later as slow queries with no obvious cause.
• It is single threaded. Unlike a rebuild, which can go parallel, a reorganize works through the index one allocation unit at a time. On a very large index that makes it slower than the rebuild it was chosen to be cheaper than.

Reorganize is also less effective on badly fragmented indexes. Above roughly 30 percent it is doing a great deal of work to achieve less than a rebuild would in less time.

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];

Check whether statistics are actually being maintained, which is the hidden cost here:

SELECT OBJECT_NAME(s.[object_id]) AS [table_name],
       s.[name]                   AS [statistics_name],
       sp.[last_updated],
       sp.[rows],
       sp.[rows_sampled],
       sp.[modification_counter]
  FROM sys.stats AS s WITH (NOLOCK)
 CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.[stats_id]) AS sp
 WHERE sp.[last_updated] < DATEADD(DAY, -30, GETDATE())
    OR sp.[modification_counter] > 10000
 ORDER BY sp.[last_updated];

If a lot of rows come back on an instance that runs index maintenance nightly, the reorganize task is why.

How to fix it

Replace the task with a script that decides per index. Ola Hallengren’s IndexOptimize is the standard answer: it reorganizes what benefits from a reorganize, rebuilds what needs a rebuild, skips what needs nothing, and updates statistics as a separate, deliberate step.

A reasonable policy:

Whatever you do about the indexes, make statistics maintenance explicit. If you keep the reorganize task for now, add an Update Statistics step alongside it, or statistics are being left to the automatic threshold alone, which is too slow on large tables.

Then remove the Reorganize Index task from the plan. No downtime is required.

How long it takes

About two hours to put a replacement in place and remove the task.

Related reports

Related checks

Frequently asked questions

Reorganize is online and safe. Why report it at all? Because it is being applied without judgment, it costs a maintenance window, and it maintains no statistics. Safe and useful are different properties.

We switched from rebuild to reorganize to shorten the window. That is the most common reason this task exists, and it is also the moment statistics maintenance stopped. Check the statistics query above.

Does reorganize update statistics in any version? No. It never has. Only a rebuild does, and only for the indexes it rebuilds.

Is it worth reorganizing a small index? No. Below roughly a thousand pages an index occupies a handful of extents and fragmentation does not affect how it is read.