AI came up with the following proposal:
Summary
tsf_optimize touches every index and every modified table on every run, regardless of whether there is anything to gain. On larger databases this makes it slow while doing almost nothing useful. We propose adding thresholds (with parameters and safe defaults) so that it only maintains indexes and statistics that actually need it.
Observed situation (customer database, SQL Server, ~100 GB)
- Run time of
tsf_optimize: 2 h 22 min. - After the run: ~850 indexes (~54 GB) have 0.2–0.9 % average fragmentation, but all of them are REORGANIZEd every run because the only rule is
frag <= 30 → REORGANIZE. - The actual REBUILDs (83 indexes > 30 %) are all tiny (< 1,000 pages, 14 MB in total) and take seconds.
- Afterwards
UPDATE STATISTICS <table>runs for every table with modification_counter > 0, including large temporal history tables (5–7 GB each), and also for tables whose indexes were just rebuilt (and so already have fresh full-scan statistics).
Why the current logic is costly
- No lower bound on fragmentation. An index with 0 % fragmentation still gets a REORGANIZE. That read is pure overhead.
- No lower bound on size. Small indexes (< ~1,000 pages) gain nothing from defragmentation. Microsoft and the common maintenance scripts skip them.
- REORGANIZE is the expensive path for large indexes. It is single-threaded and fully logged, and
LOB_COMPACTION = ONalso reads all LOB pages. Running it on every large index costs time, log volume and I/O, and in AG/log-shipping setups also extra log traffic. - Statistics are updated per table, for any change, and twice after a rebuild. A single modified row triggers a statistics update for the whole table. Statistics already refreshed by an index rebuild are updated again, and with sampling, so the full-scan statistics from the rebuild are replaced by sampled ones.
Proposed changes
| # | Change | Default |
|---|---|---|
| 1 | Skip indexes below a minimum fragmentation | @frag_low = 5 (%) |
| 2 | Skip indexes below a minimum size | @min_pages = 1000 |
| 3 | Keep the REORGANIZE/REBUILD boundary, but make it configurable | @frag_high = 30 (%) |
| 4 | REBUILD ONLINE = ON when the edition supports it (Enterprise, Developer, Azure SQL); otherwise keep the current behaviour | automatic |
| 5 | Update statistics per statistic (UPDATE STATISTICS t s), not per table, and only when the change is significant relative to the row count | e.g. modification_counter > 0.1 % of rows or the SQL Server dynamic threshold (sqrt(1000 * rows)) |
| 6 | Skip statistics of indexes that were just rebuilt | automatic |
| 7 | Optional: @max_duration_minutes to stop starting new operations after a time limit | null (no limit) |
Expose the thresholds as optional parameters with these defaults. Existing calls without parameters keep working, and customers can tune them without overriding the template.
Expected effect
On our database the run would drop from about 2 h 20 min to minutes. Hardly any index is above 5 % fragmentation after the first run, and statistics are only refreshed where needed. The index and statistics quality stays the same, because the skipped work does not change anything measurable.
Does this hold in all cases?
- Skipping low fragmentation and small indexes (1, 2): yes, safe everywhere. The worst case is that an index with slightly more than 0 % fragmentation stays as it is, which is the standard in Microsoft guidance and in Ola Hallengren's scripts. On SSD storage, fragmentation matters even less.
- REBUILD vs REORGANIZE (3, 4): depends on the edition. On Standard Edition, REBUILD is offline and locks the table, so REORGANIZE (online) remains the right choice for applications in use 24/7. That is why the boundary should stay configurable and ONLINE should only be used when available.
- Statistics (5, 6): yes, safe everywhere.
AUTO_UPDATE_STATISTICSkeeps covering the rest. Customers with very skewed data may want a lower threshold orFULLSCAN, so this should be configurable. - Very small databases: no noticeable gain, but no harm either.
- Other RDBMS (DB2, Oracle, PostgreSQL): this proposal only concerns the SQL Server template.