Study guide

9.3 Index Changes and Execution Plan Review

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

9.3 Index Changes and Execution Plan Review

Key Takeaways Explain This Topic AI

Missing index DMVs (sys.dm_db_missing_index_details joined with _group_stats and _index_group) reveal indexes the optimizer would have used; they are suggestions, not mandates, and must be de-duplicated before creation

sys.dm_db_index_usage_stats exposes unused and rarely used indexes; user_seeks, user_scans, and user_lookups near zero since the last service restart indicate drop candidates, but beware of monthly/annual workloads

Execution plan operators tell a story - scans signal missing indexes, key lookups signal missing covering columns, sorts signal missing supporting indexes or SARGability problems, and hash joins often signal missing join indexes

Estimated plans are the optimizer's prediction from statistics; actual plans include runtime row counts and reveal statistics skew and parameter sniffing - always compare when diagnosing regression

Forcing a plan via Query Store pins a known good shape for parameter-sensitive queries, but the durable fix is usually an index change or a query hint such as OPTION (RECOMPILE or OPTIMIZE FOR UNKNOWN)

Last updated: August 2026

Missing Index DMVs

When the optimizer cannot find a useful index, it records a missing-index suggestion in memory-only DMVs. These persist only until the service restarts, so on a running server they reflect activity since the last restart, not the lifetime of the database. The three views join on index_handle and group_handle :

sys.dm_db_missing_index_details : one row per missing-index feature, with the equality, inequality, and included columns the optimizer wanted, plus an estimated average user impact (a percentage).

sys.dm_db_missing_index_group_stats : aggregated stats per group - user_seeks, user_scans, avg_user_impact, last_user_seek - ranking candidates by impact and frequency.

sys.dm_db_missing_index_groups : the join table mapping groups to details.

  • A typical ranking query: SELECT TOP (20)
  • mig.index_group_handle, mid.index_handle,
  • mid.equality_columns, mid.inequality_columns, mid.included_columns,
  • migs.user_seeks, migs.user_scans, migs.avg_user_impact, migs.last_user_seek,
  • mid.statement AS [object]
  • FROM sys.dm_db_missing_index_group_stats migs
  • JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
  • JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
  • ORDER BY migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

Treat the suggestions as input, not output. The optimizer does not de-duplicate overlapping suggestions, does not know about existing indexes that almost cover the request, and cannot tell you whether a suggested index hurts write throughput on a busy OLTP table. Before creating one, merge overlapping suggestions, check for existing indexes that already cover most columns, and weigh the read benefit against write overhead. The estimated impact percentage is a heuristic, not a measurement - always verify with SET STATISTICS IO ON before and after.

Unused Indexes

Every nonclustered index costs writes: each insert, update, or delete must maintain every index. sys.dm_db_index_usage_stats exposes per-index read and write counters since the last service restart. An index whose user_seeks , user_scans , and user_lookups are all zero (or very low) since the last restart is a drop candidate. SELECT OBJECT_NAME(i.object_id) AS [object], i.name AS index_name,

  • ius.user_seeks, ius.user_scans, ius.user_lookups,
  • ius.user_updates, ius.last_user_seek
  • FROM sys.indexes i
  • LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
  • WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND ius.database_id = DB_ID()
  • ORDER BY ius.user_updates DESC, COALESCE(ius.user_seeks + ius.user_scans + ius.user_lookups, 0) ASC;

Cautions the exam tests: counters reset to zero on service restart, so a monthly batch job whose index shows zero since yesterday's restart is not unused - it just has not run yet. A unique index backing a constraint ( is_unique_constraint = 1 ) should never be dropped just because it is unused for reads; it exists to enforce correctness. And user_updates counts writes, not just row updates; an index with high user_updates and zero reads is the strongest drop candidate.

Index Types and When to Use Each

Index type Best for Watch out for

Clustered (CI) Range scans, ordered retrieval by the clustered key; one per table Wide keys bloat every nonclustered index; pick a narrow, ever-increasing key

Nonclustered (NCI) Point lookups on a column not in the clustered key Each NCI carries the clustered key as a row locator

Covering (included columns) Queries whose SELECT list and WHERE are all covered by the index Adding INCLUDE columns grows the leaf row; balance width against lookup elimination

Filtered Indexes over a small, queryable subset (e.g., WHERE status = 'Open') Must match the query's predicate or the optimizer will not use it

Columnstore Analytical scans, star joins, fact tables Not for OLTP point updates; use clustered columnstore for fact tables

Covering indexes eliminate key lookups : when a nonclustered index satisfies a query's WHERE but not its SELECT list, the engine looks up each remaining column from the clustered index (or heap) - one lookup per row, often the dominant cost. Adding the missing columns as INCLUDE columns makes the index covering and removes the lookup. This is one of the highest-leverage optimizations on the exam.

Filtered indexes are powerful but picky: the query's predicate must be a subset of (or equal to) the index filter for the optimizer to choose it. A filtered index WHERE status = 'Open' is usable by WHERE status = 'Open' AND priority = 1 but not by WHERE status IN ('Open', 'Pending') .

Reading Execution Plans

Execution plans are read right to left ; the rightmost operator is the first to execute. The operators to recognize on sight:

Index Seek / Clustered Index Seek : efficient; uses the index b-tree to find rows. Good.

Index Scan / Clustered Index Scan : reads the whole structure. Bad unless the table is small or the query needs most rows.

Key Lookup : fetches missing columns from the clustered index per row. The classic covering-index signal.

RID Lookup : same as a key lookup, on a heap.

Sort : memory-consuming; often indicates a missing supporting index that could deliver ordered data.

Hash Match (Join) : builds a hash table in memory; good for large unsorted inputs; bad when one input is small and indexed (a nested loop would be cheaper).

Merge Join : requires sorted inputs; efficient when both inputs are already ordered via indexes.

Nested Loops : best when the outer input is small and the inner input has an index seek.

Table Spool / Index Spool : caches intermediate results; occasionally useful, often a sign of a re-executed subquery.

Compute Scalar : evaluates an expression; cheap unless it wraps a function that disables SARGability.

Estimated plans are produced by the optimizer from statistics without executing the query; actual plans include runtime row counts, actual row counts versus estimated, and actual recompiles. A wide gap between estimated and actual rows is the signature of stale statistics or parameter sniffing .

SET STATISTICS IO and TIME

SET STATISTICS IO ON returns logical reads, physical reads, read-ahead reads, and lob logical reads per table referenced by the query - the most reliable measure of query cost. SET STATISTICS TIME ON returns parse, compile, and CPU time. Reduce logical reads (the number of 8 KB pages touched in the buffer pool) as the primary objective; CPU time and duration are secondary signals.

Parameter Sniffing, Plan Regression, and Forcing Plans

Parameter sniffing is the optimizer using the first parameter values it sees to compile a plan that is then reused for all future parameter values - good when distributions are uniform, bad when they are skewed. The classic symptom: a stored procedure runs fast for some parameter values and slowly for others, and the plan shows an inappropriate join or scan for the slow values. Remedies:

OPTION (RECOMPILE) : forces a fresh plan every execution; best for ad hoc parameter sets where compile cost is small relative to execution cost.

OPTIMIZE FOR UNKNOWN : uses an averaged cardinality estimate instead of a specific parameter value, trading a perfect plan for a stable one.

Local variables inside the procedure defeat sniffing at the cost of using density estimates.

Query Store plan forcing : pin the plan that works across the most parameter distributions, especially when automatic plan correction is involved.

Query hints via USE PLAN or query store hints : more invasive, generally reserved for stubborn regressions.

The durable progression the exam rewards: first diagnose with Query Store and the plan, then prefer a query hint or index change that fixes the root cause, and use plan forcing as a stabilizer while the durable fix is built.