# SQL Server Index Maintenance Checklist for ERP Databases

> Source: https://netray.co/tools/sql-server-index-maintenance-checklist
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

A practical checklist for index rebuilds, reorganizes, statistics updates and verification on SQL Server hosted ERPs.

## Category

erp-operations

## Audience

- ERP DBA
- ERP administrator
- IT manager

## Tool

**Kind:** checklist

**Scoring Note:** 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.

**Groups:**

#### Before you start

**Items:**

**Text:** Confirm a full backup and a log backup completed successfully today

**Critical:** true

**Text:** Confirm the ERP vendor supports your planned index changes and does not manage its own indexes

**Critical:** true

Pick a maintenance window outside batch posting, MRP and period close

**Text:** Check free disk space for data, tempdb and the transaction log (rebuilds grow the log)

**Critical:** true

Record current wait statistics and top slow queries as a baseline

Note the SQL Server edition: online rebuilds need Enterprise (or Azure SQL)

#### Assess fragmentation

**Items:**

**Text:** Query sys.dm_db_index_physical_stats in LIMITED mode for indexes of 1,000 pages or more

**Critical:** true

Ignore indexes under about 1,000 pages; fragmentation there rarely affects performance

Reorganize when fragmentation is roughly 5 to 30 percent

Rebuild when fragmentation is above roughly 30 percent

Review page density as well as fragmentation percent for heavily updated tables

Check fill factor on hot tables and avoid blanket low fill factors

#### Run the maintenance

**Items:**

**Text:** Use a tested maintenance solution or script rather than rebuilding everything nightly

**Critical:** true

Use ONLINE = ON for rebuilds where the edition allows it

Cap MAXDOP so the rebuild does not starve ERP users

Update 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 tables

**Text:** Monitor log size and log backup frequency during the run

**Critical:** true

Set a time limit so the job stops before business hours

Skip or handle columnstore and memory-optimized indexes separately

#### Verify and follow up

**Items:**

Re-run the fragmentation query and compare with the baseline

Compare top slow queries and wait statistics before and after

**Text:** Run DBCC CHECKDB on schedule, separately from index work

**Critical:** true

Review missing index and unused index reports before adding or dropping any

Log what was rebuilt, when and how long it took

Check that auto-update statistics is enabled unless the vendor says otherwise

Revisit thresholds quarterly as data volumes grow

Document the job schedule and owner in the ERP runbook

## Content

**Intro:** ERP databases slow down gradually as data grows and rows are updated, and index maintenance is one of the few things a DBA or ERP admin can do about it. This checklist keeps the work safe and targeted instead of a nightly rebuild-everything habit.

**Sections:**

#### How to use the checklist

**Body:** 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

**Body:** 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.

**Bullets:**

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

#### ERP specific cautions

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### How often should I rebuild indexes on an ERP database?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

sql server index maintenance checklist, index fragmentation rebuild reorganize, sql server statistics update erp, erp database maintenance plan, sql server index rebuild best practices, syteline database maintenance, ola hallengren erp, sql server maintenance window checklist

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json