Index & Statistics Maintenance

Status: Reviewed (deep-dive v1)
Stack: PowerShell (optional host pressure) → SQL jobs/procs → Blazor Server UI
Depends on: SQL-01 Inventory; safer after SQL-02 Backup coverage is trusted
Goal: Keep indexes and stats healthy without wrecking log/IO during business hours.

Why this third

Fragmented indexes and stale stats cause real pain — but blind rebuilds cause more. Measure, threshold, window, then act. Never maintain what you haven’t inventoried and can’t restore.

What “good” looks like

Per-instance maintenance window + max runtime known
Clear rebuild vs reorganize vs “leave alone” thresholds
Stats update policy separate from index rebuild (don’t only rely on rebuild to refresh stats)
Heaviest offenders visible in Blazor; last job run + outcome visible
Online vs offline capability respected (edition + index type)
No secrets in UI; no ad-hoc destructive clicks in v1

Starting thresholds (confirm with Rick)

Metric	Reorganize	Rebuild	Skip / watch
Fragmentation (`avg_fragmentation_in_percent`)	~10–30%	≥ ~30%	< ~10%
Page count	Only if ≥ ~1000 pages (ignore tiny indexes)	Same	Small indexes
Stats	`UPDATE` if modification counter high or sample stale	After large data loads	Tiny tables

These are common starting points, not dogma. Partitioned tables, LOBs, columnstore, and AG secondaries need special cases (see below).

Prefer:

REORGANIZE for mid fragmentation (online, cheaper log)
REBUILD with ONLINE=ON when edition supports and window allows
Targeted UPDATE STATISTICS with smart sample rather than blanket fullscan nightly

Checklist — collect / evaluate (SQL first; PS assists)

Run against inventoried instances in a maintenance/eval job (read-heavy).

[ ] Sample index health: sys.dm_db_index_physical_stats (LIMITED mode for estate scans)
[ ] Capture: DB, schema, table, index, type, fragmentation, page_count, allow_page_locks
[ ] Stats health: sys.stats + sys.dm_db_stats_properties (rows, modification_counter, last_updated)
[ ] Optional PS: disk free % / outstanding IO on host before allowing heavy rebuild queue
[ ] Emit top-N offenders + job runtime budget remaining

Avoid scanning every DB with DETAILED mode on a large estate during peak.

Checklist — store (SQL + stored procedures)

Table	Purpose
`dbo.IndexMaintPolicy`	Per-instance or per-db thresholds + window
`dbo.IndexHealthSnapshot`	Latest frag/page stats sample
`dbo.StatsHealthSnapshot`	Latest stats modification sample
`dbo.IndexMaintQueue`	Planned actions (Reorg/Rebuild/UpdateStats) + status
`dbo.IndexMaintRun`	Job run audit (start/end, counts, errors)

Procs / jobs:

[ ] usp_IndexHealth_ApplySnapshot — store sample; enqueue actions over thresholds
[ ] usp_IndexMaint_GetTopOffenders — Blazor grid
[ ] usp_IndexMaint_GetLastRun — job status card
[ ] Agent job wrapper: evaluate → enqueue → execute within window → stop on budget

Execution engine can be T-SQL job steps or PS calling T-SQL; keep commands logged.

Checklist — present (Blazor Server)

[ ] Top fragmented indexes (filter by instance/db, min pages)
[ ] Stale stats (high modification_counter)
[ ] Last maintenance run (success/fail, duration, actions done vs skipped)
[ ] Queue view (pending/failed actions) — read-only in v1
[ ] Link to this runbook; optional “request eval now” flag for next cycle

Operator runbook (short)

Check Backup coverage Green for the DB before heavy rebuilds
Open Index health → sort by fragmentation × page_count
Confirm window / AG role (prefer primary for rebuilds unless policy says otherwise)
If job failed mid-run: read IndexMaintRun error; check log disk, blocking, timeout
After big load/ETL: prefer targeted stats update before blaming indexes
If users report slowness post-maint: check for oversized maintenance still running; kill only with change control

Special cases (don’t skip)

Situation	Guidance
Always On secondary	Readable secondary: reorg sometimes OK; rebuilds usually on primary; respect backup preference / sync lag
Enterprise vs Standard	ONLINE rebuild not on all editions; plan downtime or reorg-only
Columnstore	Different DMVs / rebuild semantics — track separately later
LOB / XML / spatial	Rebuild cost high; test in nonprod
Fill factor	Don’t change casually; document if used for hotspot tables
Ola Hallengren / Minion / custom	If already present, document which; don’t run two competing jobs

Job design tips (lean)

Cap runtime (e.g. stop enqueue after N minutes)
Order by impact (page_count × fragmentation) descending
One DB at a time on busy hosts
Separate eval schedule (frequent/light) from execute schedule (window-only)
Alert on: job failed, skipped critical queue items, log disk < threshold (PS)

Permissions notes

Eval: view DB state / index DMVs; avoid needless sysadmin
Execute job owner: enough ALTER INDEX / UPDATE STATISTICS on targets
Blazor: read procs only
PS host checks: local performance counter / CIM read

Done definition for this topic

[ ] Snapshots landing for at least one instance
[ ] Blazor shows top offenders + last run
[ ] Windowed job completes without runaway growth of log on a test DB
[ ] This note linked from the Knowledge Base list item

Next after this

SQL Agent jobs & schedules — monitor the jobs that run this maintenance (and backups).

SQL Dude — SQL-03 Index & Statistics Maintenance v1