10.1 Index, Statistics, and Integrity Maintenance

Why Maintenance Matters for DP-300

Database performance degrades silently: indexes fragment, statistics go stale, and storage corruption — rare but catastrophic — must be caught before a restore window expires. Domain 3 of the DP-300 exam weights "monitor, configure, and optimize database resources" at 20–25%, and routine maintenance is a heavy slice. Expect scenarios that ask which operation to choose given a fragmentation level, what resumable index rebuilds buy you, when to update statistics with a full scan versus a sample, and how to handle suspect pages detected by DBCC CHECKDB.

Index Rebuild vs Reorganize

SQL Server exposes two index-repair operations. ALTER INDEX ... REORGANIZE is an always-online, lightweight operation that physically reorders the leaf-level pages of an index to defragment it; it uses minimal log space and can be stopped and restarted with progress preserved. ALTER INDEX ... REBUILD drops and recreates the index (or partitions of it) and produces a tighter result, but historically required an exclusive lock unless you enabled the ONLINE = ON option.

The classic exam heuristic — rebuild above 30% fragmentation, reorganize between 5% and 30%, ignore below 5% — comes from old Microsoft guidance. Current guidance de-emphasizes fixed thresholds in favor of page_count-aware decisions. A 1,000-page index showing 50% fragmentation affects almost no I/O; a 5-million-page index at 20% fragmentation absolutely does. Read sys.dm_db_index_physical_stats and weigh avg_fragmentation_in_percent together with page_count. A common trap is rebuilding tiny indexes because they crossed a percentage threshold — the right answer is to leave them alone.

ALTER INDEX REBUILD/REORGANIZE Options

Key options the exam tests:

Resumable index rebuilds are a flagship Enterprise feature - resumable online index rebuild arrived in SQL Server 2017 and resumable online index create in SQL Server 2019: a 500 GB rebuild that hits a maintenance-windowcutoff can be paused (ALTER INDEX ... PAUSE) and resumed later or auto-resumed after a failover. Resumable mode requires ONLINE = ON, is not available for columnstore indexes, and cannot be combined with SORT_IN_TEMPDB = ON. On Azure SQL Database and Managed Instance, online and resumable operations are supported in the platform — default to them.

Statistics Maintenance

The query optimizer relies on statistics (histograms of column value distribution) to estimate cardinality. Stale statistics produce bad plans, and the optimizer does not know they are stale. Three maintenance levers:

• Auto-update statistics (database-level AUTO_UPDATE_STATISTICS ON by default): the engine automatically updates statistics after enough rows change. The modification counter threshold is roughly 500 rows plus 20% of table cardinality for tables over 500 rows; small tables hit the threshold quickly, large tables need proportionally more change.
• Auto-update statistics asynchronously (AUTO_UPDATE_STATISTICS_ASYNC): when triggered, the existing (stale) plan is compiled and the current query runs with old stats; the update happens in the background. This trades plan quality for latency stability and is preferred for workloads that cannot tolerate compile-time stalls.
• Manual update via UPDATE STATISTICS: supports FULLSCAN (every row) or a SAMPLE percentage; NORECOMPUTE disables the auto-update on that statistic; INCREMENTAL = ON builds per-partition statistics for partitioned tables so a new partition does not invalidate the whole statistic.

Two patterns matter for the exam. A full scan is mandatory for skewed or small lookup tables where sampling can miss hot values; sampling is sufficient for large, evenly distributed tables and runs much faster. The STATS_DATE function and sys.dm_db_stats_properties DMV report when statistics were last updated and the rows sampled.

A common scenario: a plan regresses after a large data load because the modification counter threshold was not crossed, or because async stats update deferred the refresh. The remedy is UPDATE STATISTICS dbo.Fact WITH FULLSCAN on the affected tables, or a scheduled job that follows bulk loads. After a stats update, expect new plans — clear the plan cache (via ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE on Azure SQL DB/SQL Server 2019+) if you need to force recompilation immediately.

Ola Hallengren vs Microsoft Maintenance Solution

Two community and industry patterns dominate index and statistics maintenance. The Ola Hallengren SQL Server Maintenance Solution is the de facto community standard: a set of stored procedures (IndexOptimize, StatisticsUpdate, DatabaseIntegrityCheck, CommandLog, Backup) parameterized to run as SQL Agent jobs. IndexOptimize reads fragmentation and page counts from sys.dm_db_index_physical_stats and applies the rebuild/reorganize/ignore decision per index with sensible defaults you can override (for example, @FragmentationLevel1 = 5, @FragmentationLevel2 = 30). All outcomes are logged to CommandLog, invaluable for audit and trend analysis.

Microsoft's Maintenance Solution (the script that creates curated Maintenance Plans and jobs) ships built-in to SQL Server via the Maintenance Plan wizard. It is GUI-driven and simpler to deploy but far less flexible — you cannot easily express "only rebuild indexes above 10,000 pages." For DP-300 scenarios asking for the most flexible, scriptable, loggable maintenance pattern on SQL Server, SQL MI, or VMs, the Ola Hallengren solution is the intended answer. Both are supported on SQL Server, SQL MI, and SQL Server on Azure VMs; on Azure SQL Database, equivalent maintenance is achieved through Azure SQL Database automatic tuning and Elastic Jobs, since SQL Server Agent does not exist on single databases or elastic pools.

Database Integrity Checks

DBCC CHECKDB validates the logical and physical integrity of every object in a database. It is expensive on large databases, so the exam tests the lighter options:

On very large databases, run PHYSICAL_ONLY nightly and a full CHECKDB weekly or monthly. Splitting the workload across CHECKTABLE per table over a cycle is a common large-DB strategy. On Azure SQL Database and Managed Instance, integrity checks are performed by the platform behind the scenes, but you can still run DBCC CHECKDB (with PHYSICAL_ONLY recommended for cost) for your own assurance.

Page Corruption and Suspect Pages

When DBCC CHECKDB or a read finds a torn or bad checksum page, the page is marked in the suspect_pages table in msdb. Recovery options depend on severity and backup availability:

1. Restore from backup — the cleanest fix. Use RESTORE DATABASE ... WITH PAGE = '...' for targeted page restores from a known-good full or log backup.
2. Repair with data loss — DBCC CHECKDB ... WITH REPAIR_ALLOW_DATA_LOSS allocates a fresh page and deallocates the corrupt one; some rows are lost and referential integrity may break. It is the last resort.
3. Repair without data loss — REPAIR_REBUILD fixes nonstructural issues only (rarely sufficient for real corruption).

The exam pattern: a scenario describes corruption discovered on a production database with recent backups. The correct answer is page-level restore from backup, not REPAIR_ALLOW_DATA_LOSS. Reserve repair for cases where backups are unavailable or the database is a non-production copy. Monitor the suspect_pages table (8,000 row limit — older entries are pushed out) and act on any new entry.