This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Optimize database performance
      --> Identify and resolve query performance issues, including blocking and deadlocks

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

In Part 1, we discussed locking, blocking, deadlocks, transaction isolation levels, and concurrency controls. In this section, we will focus on the tools and techniques that SQL developers use to diagnose and resolve performance issues. These tools are frequently referenced throughout Microsoft documentation and are highly relevant for the DP-800 certification exam.

After completing this article, you should be able to:

• Interpret query execution plans.
• Use Query Store to analyze historical query performance.
• Leverage Dynamic Management Views (DMVs) to monitor database activity.
• Capture performance issues with Extended Events.
• Interpret wait statistics.
• Optimize indexes.
• Address parameter sniffing issues.
• Maintain statistics.
• Follow a structured performance tuning methodology.
• Recognize common DP-800 exam scenarios.

A Structured Performance Tuning Process

Performance tuning should follow a systematic approach rather than relying on guesswork.

A recommended workflow is:

1. Identify the slow query.
2. Capture the execution plan.
3. Examine wait statistics.
4. Check index usage.
5. Review Query Store history.
6. Examine DMVs.
7. Optimize the query or indexes.
8. Test the improvement.
9. Monitor ongoing performance.

Following a structured process helps avoid unnecessary changes that may introduce new problems.

Understanding Query Execution Plans

An execution plan is a roadmap that shows how SQL Server processes a query.

It displays:

• Order of operations
• Index usage
• Join methods
• Estimated and actual row counts
• Operator costs
• Memory grants
• Parallelism decisions

Execution plans help identify why a query is slow.

Estimated vs. Actual Execution Plans

Estimated Execution Plan

Generated before execution.

Advantages:

• No query execution required
• Useful during development
• Quick to generate

Limitations:

• Uses estimated statistics
• Does not show runtime behavior

Actual Execution Plan

Generated while the query executes.

Advantages:

• Shows actual row counts
• Displays actual execution times
• More accurate for troubleshooting

Requires executing the query.

Reading Execution Plans

Several operators frequently appear in execution plans.

Index Seek

The optimizer directly locates matching rows.

Characteristics:

• Fast
• Efficient
• Low I/O
• Preferred operation

Index Scan

Reads most or all index pages.

May be acceptable when:

• Returning many rows
• Small tables

May indicate missing indexes if unexpected.

Table Scan

Reads the entire table.

Usually indicates:

• Missing indexes
• Poor filtering
• Small tables

Large table scans often create significant I/O.

Nested Loop Join

Efficient when one input is small.

Ideal for:

• Primary key lookups
• Highly selective joins

Merge Join

Efficient when both inputs are sorted.

Often used with:

• Clustered indexes
• Ordered datasets

Hash Match

Builds hash tables.

Common for:

• Large joins
• Large aggregations

Requires considerable memory.

Cost Percentages

Execution plans assign estimated costs.

Example:

These percentages are estimates, not actual elapsed time.

Focus on expensive operators as starting points for optimization.

Warning Indicators

Execution plans may include warnings such as:

• Missing indexes
• Implicit conversions
• Hash spills
• Sort spills
• Excessive memory grants
• Parallelism skew

Warnings deserve investigation but should not automatically be implemented without testing.

Query Store

Query Store records query history over time.

It stores:

• Query text
• Execution plans
• Runtime statistics
• Resource consumption
• Plan history
• Wait statistics (supported versions)

Unlike the plan cache, Query Store persists across restarts.

Benefits of Query Store

Query Store enables developers to:

• Identify regressed queries
• Compare historical execution plans
• Detect parameter-sensitive plan changes
• Force a known good execution plan
• Analyze workload trends

Query Store is one of the most valuable performance troubleshooting features available in SQL Server and Azure SQL.

Common Query Store Reports

Useful reports include:

• Top Resource Consuming Queries
• Queries with High Duration
• Queries with High CPU
• Query Wait Statistics
• Regressed Queries
• Plan Comparison

These reports quickly identify problematic queries.

Forcing Execution Plans

Occasionally, SQL Server chooses a poor plan.

Query Store allows administrators to force a previous stable plan.

Advantages:

• Quick recovery
• No code modification
• Useful after upgrades
• Helps address parameter-sensitive regressions

Forced plans should be monitored to ensure they remain optimal as data changes.

Dynamic Management Views (DMVs)

DMVs provide real-time information about SQL Server activity.

They are essential for performance troubleshooting.

Examples include:

• Active requests
• Sessions
• Index usage
• Missing indexes
• Wait statistics
• Cached execution plans
• Memory usage

Frequently Used DMVs

sys.dm_exec_requests

Displays currently executing requests.

Useful columns include:

• session_id
• status
• wait_type
• blocking_session_id
• cpu_time
• logical_reads

Example:

SELECT
    session_id,
    status,
    cpu_time,
    logical_reads,
    blocking_session_id
FROM sys.dm_exec_requests;

sys.dm_exec_sessions

Displays connected sessions.

Useful for identifying:

• Login information
• Application names
• Client connections
• Session status

sys.dm_exec_query_stats

Provides cumulative statistics.

Includes:

• Execution count
• CPU usage
• Logical reads
• Elapsed time

Excellent for identifying expensive queries.

sys.dm_db_index_usage_stats

Shows index usage.

Useful for identifying:

• Unused indexes
• Frequently used indexes
• Missing optimization opportunities

sys.dm_db_missing_index_details

Recommends potential indexes.

Important:

These recommendations should always be evaluated carefully rather than implemented automatically.

Extended Events

Extended Events is SQL Server’s modern monitoring framework.

It replaces SQL Trace and SQL Server Profiler for most workloads.

Advantages:

• Lightweight
• Highly configurable
• Lower overhead
• Suitable for production environments

Common Extended Event Sessions

Extended Events can capture:

• Deadlocks
• Blocking
• Long-running queries
• Login failures
• Wait statistics
• Query execution
• Memory grants

The built-in system_health session captures many important diagnostic events by default.

Wait Statistics

Wait statistics show where SQL Server spends time waiting.

Rather than measuring CPU usage alone, waits reveal resource bottlenecks.

Common categories include:

• CPU
• Disk I/O
• Memory
• Locks
• Network
• Parallelism

Common Wait Types

LCK_M_*

Lock waits.

Indicate blocking.

Possible causes:

• Long transactions
• Lock contention
• Missing indexes

PAGEIOLATCH_*

Waiting for data pages from disk.

May indicate:

• Slow storage
• Large scans
• Insufficient memory

CXPACKET / CXCONSUMER

Related to parallel query execution.

May indicate:

• Large parallel queries
• Uneven workload distribution

Not always a problem.

WRITELOG

Waiting for transaction log writes.

May indicate:

• Heavy write activity
• Slow storage subsystem

SOS_SCHEDULER_YIELD

CPU scheduling wait.

May indicate CPU pressure.

Index Optimization

Indexes greatly influence performance.

Well-designed indexes reduce:

• Logical reads
• CPU usage
• Query duration
• Blocking

Poor indexes increase maintenance costs.

Clustered vs. Nonclustered Indexes

Clustered Index

• Determines physical row order.
• One per table.
• Ideal for range queries.

Nonclustered Index

• Separate structure.
• Many allowed.
• Ideal for selective lookups.

Covering Indexes

A covering index contains all columns required by a query.

Benefits include:

• Eliminates key lookups
• Reduces logical reads
• Improves performance

Example:

CREATE INDEX IX_Orders_Customer
ON Sales.Orders(CustomerID)
INCLUDE(OrderDate, TotalAmount);

Index Fragmentation

Fragmented indexes reduce performance.

Maintenance options include:

Regular maintenance improves read performance.

Parameter Sniffing

SQL Server caches execution plans.

Sometimes the first parameter value generates a plan that performs poorly for later executions.

Example:

A plan optimized for one customer with only a few orders may perform poorly when reused for a customer with millions of orders.

Potential mitigation techniques include:

• OPTION (RECOMPILE)
• OPTIMIZE FOR
• Local variables (used judiciously)
• Query Store plan forcing
• Query redesign

Understanding parameter sniffing is an important DP-800 objective.

Statistics Maintenance

Statistics help SQL Server estimate row counts.

Outdated statistics lead to:

• Poor cardinality estimates
• Incorrect join selection
• Poor execution plans

Maintenance options include:

UPDATE STATISTICS Sales.Orders;

or

EXEC sp_updatestats;

Automatic statistics updates are generally sufficient for many workloads, but large or highly volatile databases may benefit from scheduled maintenance.

Intelligent Performance Features

Modern SQL Server and Azure SQL include intelligent features such as:

• Automatic tuning
• Automatic plan correction
• Automatic index recommendations
• Intelligent Insights (Azure SQL)
• Automatic statistics updates

These features assist administrators but should complement—not replace—performance analysis and testing.

Common Performance Optimization Techniques

When troubleshooting slow queries:

• Retrieve only required columns.
• Avoid SELECT *.
• Use appropriate indexes.
• Write SARGable predicates.
• Keep transactions short.
• Avoid cursors when set-based operations are possible.
• Maintain indexes and statistics.
• Reduce unnecessary sorting.
• Limit large result sets.
• Batch large modifications.

Performance Troubleshooting Checklist

When investigating a slow query:

☐ Is an appropriate index available?

☐ Is SQL Server performing an Index Seek or Table Scan?

☐ Are statistics current?

☐ Are implicit conversions occurring?

☐ Is blocking present?

☐ Is parameter sniffing affecting performance?

☐ Are waits indicating CPU, I/O, or locking problems?

☐ Does Query Store show a regression?

☐ Can the query be rewritten more efficiently?

☐ Has the improvement been tested before deployment?

Real-World Scenario 1: Missing Index

A customer search query takes 18 seconds.

Execution plan shows:

• Table Scan
• Missing Index recommendation

Resolution:

Create an appropriate nonclustered index and validate the improvement with the actual execution plan.

Real-World Scenario 2: Parameter-Sensitive Plan

A stored procedure runs quickly for most customers but very slowly for one large customer.

Investigation shows a cached plan optimized for a small data set.

Resolution:

Evaluate parameter-sensitive plan optimization techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, Query Store plan forcing, or redesigning the query, depending on the workload.

Real-World Scenario 3: Blocking Chain

Users report intermittent timeouts.

DMVs reveal:

Session 52 blocks Session 63.

Session 63 blocks Session 81.

Session 81 blocks Session 96.

Root cause:

A long-running transaction remained open while waiting for application logic.

Resolution:

Reduce transaction duration and ensure commits occur as quickly as possible.

DP-800 Exam Tips

• Understand the differences between Estimated and Actual execution plans.
• Know when Index Seeks are preferred over Table Scans.
• Be familiar with Query Store, including plan history, runtime statistics, and plan forcing.
• Recognize the most commonly used DMVs for monitoring active requests, sessions, query performance, and index usage.
• Understand how Extended Events have largely replaced SQL Trace and SQL Server Profiler for production monitoring.
• Learn to interpret common wait types such as LCK_M_*, PAGEIOLATCH_*, CXPACKET, WRITELOG, and SOS_SCHEDULER_YIELD.
• Understand the role of statistics, parameter sniffing, covering indexes, and fragmentation in query performance.
• Remember that Microsoft recommends making tuning decisions based on evidence from execution plans and monitoring tools, rather than assumptions.

Key Takeaways

Performance tuning is an iterative process that combines analysis, measurement, and optimization. SQL Server provides a rich set of diagnostic tools—including execution plans, Query Store, DMVs, Extended Events, wait statistics, and index analysis—that help developers identify and resolve bottlenecks. For the DP-800 exam, you should be comfortable selecting the appropriate diagnostic tool, interpreting its results, and recommending effective solutions to improve query performance while maintaining scalability and concurrency.

Go to the DP-800 Exam Prep Hub main page