The DP-300 exam tests your ability to choose the right tool at the right moment. When should you use REBUILD instead of REORGANIZE? When does manual statistics update beat the automatic one? Where exactly does MAXDOP configuration belong? The options all look plausible, but once you understand why each feature exists, unfamiliar scenarios start resolving naturally.
Index Fragmentation — Reshuffling a Library vs Updating a Map
Picture a library in use for decades. Books were shelved in order at first, but after countless returns and checkouts, gaps appeared and books drifted out of place. A librarian now has to wander several aisles to find a single title. Database indexes suffer the same fate after repeated INSERT, UPDATE, and DELETE operations.
Fragmentation is measured through . Microsoft's thresholds split into two bands. When fragmentation falls between 5% and 30%, use — like updating location notes without moving books. Pages are rearranged in place and the operation always runs online. When fragmentation exceeds 30%, use — emptying the shelves and re-sorting everything from scratch. By default runs offline with a table lock, but lets it run without blocking concurrent queries.
The decisive difference: automatically refreshes statistics using a full scan during re-creation, while leaves statistics untouched. To clear fragmentation and update statistics in one step, is your only option.
Statistics and Query Plans — An Outdated Map Leads You Astray
Imagine navigating with a three-year-old GPS map. Roads that no longer exist might send you the wrong way. The query optimizer faces the same risk. Statistics are histograms describing the data distribution of each column, and when they grow stale the optimizer picks plans based on fiction rather than fact.
is ON by default and triggers when roughly 20% of a table's rows change. On a table with hundreds of millions of rows, 20% means tens of millions of changes — so auto-update may rarely fire. Force a manual refresh with to sample every row. When statistics updates cause momentary slowdowns, moves the update to the background — subsequent queries benefit from the fresh histogram without waiting. updates only tables whose statistics have changed, making it practical for scheduled maintenance jobs.
DBCC CHECKDB — Annual Check-Up, with a High-Stakes Repair Option
Just as an annual physical exam catches problems early, periodic integrity checks protect a database from silent corruption. inspects page integrity, allocation errors, and constraint violations across the entire database. skips non-clustered indexes to speed up the scan; limits inspection to physical page structure — useful for very large databases.
When corruption is detected and no clean backup exists, is available as an absolute last resort. The command removes damaged data to make the database accessible again, but lost rows cannot be recovered. On the exam, whenever appears, think "last resort, permanent data loss risk."
Automatic Tuning — The Workshop That Runs While You Sleep
What if factory machines could detect their own faults overnight and quietly restore the previous configuration before morning shift? Azure SQL Database's Automatic Tuning does exactly that for query plans.
monitors Query Store data for plan regressions. If a query that ran in 0.1 seconds yesterday suddenly climbs to 10 seconds today, Automatic Tuning forces the last known good plan without human intervention. This feature is on by default in Azure SQL Database; in SQL Managed Instance and on-premises SQL Server it must be enabled manually. The CREATE INDEX and DROP INDEX recommendations are Azure SQL Database only — if a recommended index degrades performance, the system rolls it back automatically.
MAXDOP and Resource Governor — How Many Lanes to Open
More lanes on a motorway do not always mean faster traffic. At a complex interchange, too many lanes create a merge bottleneck. MAXDOP (Max Degree of Parallelism) caps the number of CPU cores a single query can use at one time.
MAXDOP has three configuration scopes. Instance level: applies to all databases on SQL Server or SQL Managed Instance. Database level: overrides the instance setting per database. Query level: the hint applies to a single statement, overriding both higher settings. OLTP workloads typically favor MAXDOP 1 to 4; OLAP workloads benefit from higher values.
Resource Governor — available on SQL Server and SQL Managed Instance, not Azure SQL Database — partitions connections into workload groups and enforces CPU and memory limits via a Classifier Function, preventing reporting queries from starving the OLTP workload.
REBUILD vs REORGANIZE at a Glance
| Criterion | REBUILD | REORGANIZE | |:--|:--|:--| | Fragmentation threshold | Over 30% | 5–30% | | Online execution | Requires ONLINE = ON | Always online | | Statistics update | Full scan, automatic | None | | Lock impact | Table lock when offline | Minimal |
A common exam trap: a question may describe "running index maintenance online" in a way that sounds like REORGANIZE, but if fragmentation exceeds 30%, the correct answer is . REORGANIZE has no ONLINE option — it is always online.
!REBUILD versus REORGANIZE
Intelligent Query Processing — Learning on the Job
A new employee is noticeably more effective after a month than on the first day, because experience teaches adaptation. Intelligent Query Processing (IQP), introduced in SQL Server 2017, applies a similar feedback loop to query execution.
Adaptive Joins evaluate the actual row count at runtime and switch dynamically between Hash Join and Nested Loop Join. Memory Grant Feedback records how much memory a query actually used and adjusts future allocations, so over-allocated or under-allocated queries converge on the right amount. Batch Mode on Rowstore brings batch processing to regular heap and B-tree tables, accelerating analytical queries even without a columnstore index. All IQP features activate at database compatibility level 140 or higher.
Exam Key Takeaways
"Index fragmentation 5–30%, must stay online" -- REORGANIZE "Index fragmentation over 30%, statistics also need refreshing" -- REBUILD "Prevent REBUILD from locking the table" -- WITH (ONLINE = ON) "Force a full-scan statistics refresh" -- UPDATE STATISTICS WITH FULLSCAN "Update statistics without blocking queries" -- AUTO_UPDATE_STATISTICS_ASYNC ON "Automatic plan regression detection and recovery" -- FORCE_LAST_GOOD_PLAN "Automatic index recommendations (Azure SQL Database only)" -- Automatic Tuning CREATE/DROP INDEX "Database integrity check, last-resort repair" -- DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS "Per-workload CPU and memory limits (MI/on-prem only)" -- Resource Governor "Database-scoped MAXDOP configuration" -- ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP "Auto-adjust memory allocation per query" -- Memory Grant Feedback "Switch join type dynamically at runtime" -- Adaptive Joins
Automatic Tuning = automatic plan regression recovery (Azure SQL Database adds index recommendations), MAXDOP = parallel core cap (three configuration scopes), Resource Governor = workload isolation on MI and SQL Server