Guidence required in time bound index reorganization job

You could use Ola Hallengren's Maintenance Solution, which by many is considered to be the standard solution for database maintenance. The IndexOptimize procedure accepts a parameter @TimeLimit which "Set the time, in seconds, after which no commands are executed. By default, the time is not limited." That is, it will not interrupt on an operation which strays beyond the permitted window. Thus, you would set the time limit so that nothing starts after 4:45 or how long the reorg job on your biggest table runs.

If you want to use the full time until 5:00, you would have another job that kills the reorg job at that time. This is fairly unproblematic since Index Reorganize works will small transactions where it shuffles around a few pages at a time. Thus, if you kill the job, nothing is lost, and you get the benefit of the work so far. It's a different thing with index rebuild, which is an all-or-nothing operation.

For any future questions you may have, can you share the output of "SELECT @@version"?

Index reorganize is already designed to be safely interruptible and resumable at the next run, which fits the time‑bound requirement.

Key properties from the product behavior

• ALTER INDEX … REORGANIZE is an online, incremental operation.
• If a reorganize operation is canceled or interrupted, all progress up to that point is persisted; there is no rollback of completed work and no database inconsistency.
• The operation can be started and stopped multiple times until it completes.
• Statistics are not updated by reorganize; if statistics freshness is important, schedule UPDATE STATISTICS separately.

“If you cancel a reorganize operation, or if it's otherwise interrupted, the progress it made to that point is persisted in the database. To reorganize large indexes, the operation can be started and stopped multiple times until it completes.”

This means a job that is killed at 05:00 can safely be stopped and simply run again in the next window.

1. Recommended approach for a time‑bound reorganize job

High‑level pattern:

1. Use a custom T‑SQL/Agent job (not the wizard Maintenance Plan) that:
   • Enumerates indexes needing maintenance (for example, by fragmentation thresholds via DMVs).
   • Processes them in a deterministic order (e.g., largest/most fragmented first).
   • Checks current time before each index operation and exits when the end of the window is reached.
2. Use ALTER INDEX … REORGANIZE for rowstore indexes and, when needed, ALTER INDEX … REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) for columnstore indexes.
3. Let the job simply stop when the time window ends; do not attempt to forcefully kill individual SPIDs mid‑statement. Instead, design the loop so it does not start a new reorganize if there is not enough time left.

Core T‑SQL pattern (conceptual):

• Pseudocode structure:
  • At job start, capture @WindowEnd = '05:00' (same day) or compute based on current date.
  • Loop over candidate indexes ordered by size/fragmentation.
  • Before each ALTER INDEX … REORGANIZE, check IF GETDATE() >= @WindowEnd then BREAK.
  • Optionally log which index was last processed.

Because reorganize is incremental and persists progress, there is no need to track partial progress within a single index; the next run can simply re‑evaluate fragmentation and continue.

For columnstore indexes:

• Reorganize can be used to force delta store row groups into compressed row groups and merge small row groups:

    ALTER INDEX IndFactResellerSalesXL_CCI
        ON FactResellerSalesXL_CCI
        REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
  

• Starting with SQL Server 2019, Azure SQL Database, and Azure SQL Managed Instance, a background merge task already improves columnstore quality over time, often reducing the need for explicit reorganize.

2. Safe termination and automatic continuation

• Safe termination:
  • If the Agent job is stopped or the server fails during ALTER INDEX … REORGANIZE, completed work remains; the index is left in a consistent state.
  • There is no need for special rollback logic.
• Automatic continuation:
  • Next run re‑evaluates fragmentation and continues reorganizing indexes that still exceed thresholds.
  • Because reorganize is incremental, repeated runs over multiple nights gradually complete maintenance on large indexes.

3. Scheduling and frequency best practices

From the Configuration Manager/SQL Server guidance:

• Index maintenance can be performed as often as once a week.
• Statistics should be updated as often as once a day for good performance.

“You should perform index maintenance as often as once a week and update statistics of tables and views as often as once a day to maintain SQL Server performance. There is no one-size-fits-all approach to database maintenance. The optimal strategy depends on factors such as database size, workload characteristics, and operational requirements.”

Recommended pattern for 100 GB–2 TB databases:

• Frequency:
  • Start with weekly index maintenance (reorganize/rebuild) during the nightly window.
  • Daily UPDATE STATISTICS (targeted to highly volatile tables) outside peak hours.
• Maintenance window:
  • Define a clear window (e.g., 23:00–05:00) and ensure the job logic stops before 05:00.
  • For very large indexes, consider:
    • Prioritizing most critical tables first.
    • Spreading work across multiple nights (the incremental nature of reorganize supports this).
• Reorganize vs rebuild:
  • Reorganize is online and incremental, but does not update statistics.
  • Rebuild is heavier but updates statistics with a full scan for rowstore indexes and can be online or offline depending on edition and index type.
  • For very large databases, use a mix:
    • Rebuild highly fragmented, critical indexes during longer windows.
    • Reorganize moderately fragmented indexes more frequently.

4. Additional considerations

• In Azure SQL Database/Managed Instance or environments with read scale‑out/geo‑replication, index maintenance increases log generation and can increase replica latency or affect failover times. Ensure replicas are sized appropriately and schedule maintenance when increased latency is acceptable.
• ALTER INDEX REORGANIZE requires free space in the data file containing the index; if a file is full, error 1105 can occur even if the filegroup has free space in other files. Monitor file space and autogrowth.
• Index reorganize cannot run when ALLOW_PAGE_LOCKS = OFF on the index.

Summary

• Use a custom Agent job that:
  • Enumerates fragmented indexes.
  • Loops with a time check before each ALTER INDEX … REORGANIZE.
  • Stops cleanly when the window ends.
• Rely on the documented behavior that reorganize is incremental and persists progress when interrupted.
• Schedule weekly index maintenance and daily statistics updates, adjusting based on workload and the 100 GB–2 TB database sizes and the available nightly window.

References:

• Optimize index maintenance to improve query performance and reduce resource consumption
• Reorganize Index Task (Maintenance Plan)
• Configuration Manager site sizing and performance FAQ