Description of the feature
Title: Feature Request – Support for Table Prioritization in Index Maintenance
Description:
I propose adding support for prioritizing specific tables during index optimization in the SQL Server Maintenance Solution.
Background & Motivation:
In many production environments, a small number of “heavy hitter” tables are disproportionately responsible for the workload and system performance. If the indexes on these tables become fragmented or outdated, query performance can degrade significantly.
Currently, when the index optimization job runs with a defined @TimeLimit or “can run until” setting, lower-priority or non-critical tables may be processed before these heavy-hitter tables. As a result, critical performance-impacting tables can be skipped entirely if time runs out.
Proposed Implementation:
-
Prioritization Table
-
Execution Logic
- When
IndexOptimize is run, check this table first.
- Any listed tables with pending maintenance are processed before other tables.
- Once prioritized tables are complete, process the remaining tables according to the existing ordering logic.
-
Optional Parameters
- A new parameter such as
@UsePriorityList = 'Y' could control whether the prioritization feature is enabled for a given run.
Benefits:
- Ensures business-critical performance – High-impact tables are maintained first, reducing risk of degraded performance on key queries.
- Better use of limited maintenance windows – Critical maintenance is done within available runtime even if the job ends early.
- Configurable by DBAs or Developers – No need to alter the core procedure; simply maintain the priority list table.
- Backwards compatible – If no priority table is defined or
@UsePriorityList = 'N', existing logic runs unchanged.
Example Use Case:
In a retail e-commerce database, the dbo.Orders, dbo.OrderLines, and dbo.Inventory tables are queried heavily. If their indexes are not optimized promptly, order processing can slow down, directly impacting revenue. Using a prioritization list ensures these tables are always optimized first during nightly maintenance, even if the rest of the tables are postponed.
Description of the feature
Title: Feature Request – Support for Table Prioritization in Index Maintenance
Description:
I propose adding support for prioritizing specific tables during index optimization in the SQL Server Maintenance Solution.
Background & Motivation:
In many production environments, a small number of “heavy hitter” tables are disproportionately responsible for the workload and system performance. If the indexes on these tables become fragmented or outdated, query performance can degrade significantly.
Currently, when the index optimization job runs with a defined
@TimeLimitor “can run until” setting, lower-priority or non-critical tables may be processed before these heavy-hitter tables. As a result, critical performance-impacting tables can be skipped entirely if time runs out.Proposed Implementation:
Prioritization Table
Create a user-maintained table (e.g.,
dbo.IndexOptimizePriority) that lists schema/table combinations requiring higher priority.Columns might include:
SchemaName(sysname)TableName(sysname)PriorityOrder(int) – smaller numbers indicate higher priorityExecution Logic
IndexOptimizeis run, check this table first.Optional Parameters
@UsePriorityList = 'Y'could control whether the prioritization feature is enabled for a given run.Benefits:
@UsePriorityList = 'N', existing logic runs unchanged.Example Use Case:
In a retail e-commerce database, the
dbo.Orders,dbo.OrderLines, anddbo.Inventorytables are queried heavily. If their indexes are not optimized promptly, order processing can slow down, directly impacting revenue. Using a prioritization list ensures these tables are always optimized first during nightly maintenance, even if the rest of the tables are postponed.