Study guide

10.1 Index, Statistics, and Integrity Maintenance

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

10.1 Index, Statistics, and Integrity Maintenance

Key Takeaways Explain This Topic AI

Index rebuild vs reorganize guidance now favors page_count-aware decisions over fixed fragmentation thresholds; a 1,000-page index at 50% fragmentation does not matter.

Resumable index rebuilds (RESUMABLE = ON) can be paused, resumed, and survive failovers; they require ONLINE = ON and are unavailable with SORT_IN_TEMPDB.

UPDATE STATISTICS WITH FULLSCAN is mandatory for skewed or small tables; sampling is sufficient for large evenly distributed ones; async stats update trades plan quality for compile latency stability.

DBCC CHECKDB WITH PHYSICAL_ONLY skips logical checks and is suitable for frequent runs on very large databases; run a full CHECKDB weekly or monthly.

Page corruption recovery: prefer page-level restore from backup over DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS, which is the last resort when no backup exists.

Last updated: August 2026

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:

Option Effect Notes

ONLINE = ON Rebuild keeps the table accessible for reads/writes Enterprise only on SQL Server; standard on Azure SQL DB/MI

ONLINE = OFF (default on Standard Edition) Rebuild holds an exclusive lock Use only in maintenance windows

RESUMABLE = ON Rebuild can be paused and resumed, surviving restarts Implicitly ONLINE = ON ; cannot combine with SORT_IN_TEMPDB = ON

  • MAXDOP = n Caps parallelism per rebuild Override server MAXDOP for the operation
  • DATA_COMPRESSION = ... Apply or keep compression Apply per partition
  • LOB_COMPACTION = ON (REORGANIZE) Compacts in-row LOB data REORGANIZE-only option

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.