SQL Server Index Maintenance Checklist (Free)

0%

0 of 28 items complete

7 critical items still open - these are the highest-risk gaps.

Download checklist:

Before you start

Confirm a full backup and a log backup completed successfully todayCRITICALConfirm the ERP vendor supports your planned index changes and does not manage its own indexesCRITICALPick a maintenance window outside batch posting, MRP and period closeCheck free disk space for data, tempdb and the transaction log (rebuilds grow the log)CRITICALRecord current wait statistics and top slow queries as a baselineNote the SQL Server edition: online rebuilds need Enterprise (or Azure SQL)

Assess fragmentation

Query sys.dm_db_index_physical_stats in LIMITED mode for indexes of 1,000 pages or moreCRITICALIgnore indexes under about 1,000 pages; fragmentation there rarely affects performanceReorganize when fragmentation is roughly 5 to 30 percentRebuild when fragmentation is above roughly 30 percentReview page density as well as fragmentation percent for heavily updated tablesCheck fill factor on hot tables and avoid blanket low fill factors

Run the maintenance

Use a tested maintenance solution or script rather than rebuilding everything nightlyCRITICALUse ONLINE = ON for rebuilds where the edition allows itCap MAXDOP so the rebuild does not starve ERP usersUpdate statistics after reorganize (a rebuild updates its own index stats)Update statistics on large tables with sampling suited to skewed data, for example item and transaction tablesMonitor log size and log backup frequency during the runCRITICALSet a time limit so the job stops before business hoursSkip or handle columnstore and memory-optimized indexes separately

Verify and follow up

Re-run the fragmentation query and compare with the baselineCompare top slow queries and wait statistics before and afterRun DBCC CHECKDB on schedule, separately from index workCRITICALReview missing index and unused index reports before adding or dropping anyLog what was rebuilt, when and how long it tookCheck that auto-update statistics is enabled unless the vendor says otherwiseRevisit thresholds quarterly as data volumes growDocument the job schedule and owner in the ERP runbook

Score is the share of items done. Fix every critical item first; a missing recent backup or untested restore matters more than any fragmentation tuning.

ERP database health review

We can review your maintenance plan and slow query list. Optional, no obligation.

Optional. The tool and every export work without this. No spam.

How to use the checklist

Work through the groups in order and tick items as you complete them. Items marked critical are the ones that protect data or cause outages if skipped. Export the completed list to PDF to keep with your change record. Run it on a test copy first if you have one, and run it again whenever you change the schedule, upgrade the ERP or migrate to new storage.

The fragmentation guidance behind it

The thresholds are the commonly cited Microsoft guidance: reorganize between roughly 5 and 30 percent logical fragmentation, rebuild above roughly 30 percent, and skip small indexes under about 1,000 pages. They are starting points, not laws. On modern flash storage, fragmentation matters much less than on spinning disks, and out of date statistics usually cause more bad plans than fragmented indexes. Many teams get more benefit from frequent statistics updates than from frequent rebuilds.

Reorganize is online and interruptible
Rebuild recreates the index and updates its statistics
Statistics drive plan choice more than fragmentation does

ERP specific cautions

Some ERPs ship their own indexes, and vendors may ask that you do not add or drop them, as upgrades can fail on unexpected schema changes. Add custom indexes only through the supported route and document them. Large transaction and inventory tables are the usual hot spots. Watch the log: a rebuild of a very large table can generate log equal to the index size, and a full log stops the ERP. In availability groups, a big rebuild also floods the secondary replicas.

Common mistakes

Rebuilding every index every night burns log, I/O and backup time for little gain. Shrinking the database after a rebuild undoes the work by re-fragmenting. Running index maintenance and CHECKDB or backups at the same time causes long blocking. Skipping the post-run comparison means you never know whether it helped. Not telling the business about the window is a frequent cause of angry support calls.

Frequently Asked Questions

How often should I rebuild indexes on an ERP database?

Not on a fixed nightly basis. Check fragmentation weekly, act only on indexes above your thresholds and over about 1,000 pages, and update statistics more often, often daily on busy transaction tables. Adjust based on measured query performance.

Is reorganize or rebuild better?

Reorganize is lighter, online and can be stopped, suited to moderate fragmentation. Rebuild fixes heavier fragmentation and refreshes statistics but uses more resources and, without Enterprise edition, can block access to the table.

Can I use Ola Hallengren's scripts on an ERP database?

Many ERP teams do, because they are widely used and threshold driven. Confirm with your ERP vendor that they have no objection, test on a copy, and set fragmentation levels, time limits and logging explicitly.

Why is the ERP still slow after maintenance?

Likely causes are bad plans from parameter sniffing, missing indexes, blocking, undersized memory or custom reports scanning large tables. Compare wait statistics and top queries before and after to find out which applies.

If you want help baselining ERP database performance on-premises, or surfacing slow queries in plain language for your team, we can take a look.

Talk to an Expert Explore More Free Tools

Related Tools

ERP Operations

More ERP Operations tools

See all

SQL Server Index Maintenance Checklist for ERP Databases