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
      --> Evaluate query performance by using query execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight

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

One of the primary responsibilities of a SQL AI Developer is ensuring that database queries execute efficiently. Slow queries can increase response times, consume excessive CPU and memory, cause blocking, reduce scalability, and negatively affect AI-powered applications that rely on timely access to data.

The DP-800 exam expects candidates to know how to:

• Analyze query execution plans
• Identify inefficient query operators
• Interpret estimated and actual execution plans
• Use Dynamic Management Views (DMVs) to monitor performance
• Use Query Store to identify and resolve performance regressions
• Use Query Performance Insight in Azure SQL Database
• Recommend performance improvements based on collected metrics

Why Query Performance Matters

Database performance directly affects application performance.

Poorly optimized queries can lead to:

• Slow application response times
• High CPU utilization
• Excessive memory consumption
• Long-running transactions
• Locking and blocking
• Deadlocks
• Reduced scalability
• Increased Azure SQL costs

For AI-enabled applications, inefficient queries can delay:

• Retrieval-Augmented Generation (RAG)
• Semantic searches
• Vector searches
• AI model inference
• Data preparation pipelines

Performance tuning is therefore an essential database development skill.

SQL Server Query Processing

Before SQL Server executes a query, it performs several steps:

1. Parse the T-SQL statement
2. Validate syntax and object names
3. Optimize the query
4. Generate an execution plan
5. Execute the plan

The Query Optimizer determines the most efficient execution strategy based on:

• Statistics
• Available indexes
• Estimated row counts
• Predicate selectivity
• Join order
• Available memory
• Parallelism

What Is an Execution Plan?

An execution plan is a graphical or textual representation of how SQL Server executes a query.

It shows:

• Operators
• Join methods
• Index usage
• Estimated cost
• Actual row counts
• Warnings
• Parallel operations

Execution plans are among the most valuable tools for diagnosing performance issues.

Estimated vs. Actual Execution Plans

SQL Server can generate two types of execution plans.

Estimated Execution Plan

Generated before execution.

Shows:

• Estimated row counts
• Estimated operator costs
• Chosen indexes
• Join methods

Does not execute the query.

In SQL Server Management Studio (SSMS):

Display Estimated Execution Plan (Ctrl + L)

Actual Execution Plan

Generated after query execution.

Shows:

• Actual row counts
• Actual execution statistics
• Actual execution time
• Memory usage
• Runtime warnings
• Actual operator behavior

Enable in SSMS:

Include Actual Execution Plan (Ctrl + M)

The DP-800 exam frequently tests the distinction between estimated and actual execution plans.

Understanding Execution Plan Operators

Execution plans contain operators representing individual processing steps.

Common operators include:

Index Seek vs. Index Scan

One of the most frequently tested concepts.

Index Seek

Efficient.

Reads only qualifying rows.

Example:

SELECT *
FROM Customers
WHERE CustomerID = 125;

If an index exists on CustomerID:

Execution Plan:

Index Seek

Index Scan

Reads many or all index pages.

Example:

SELECT *
FROM Customers
WHERE YEAR(OrderDate)=2025;

Because the function prevents index usage, SQL Server often performs an Index Scan.

A scan is not always bad. If a query retrieves most rows in a table, a scan may be the most efficient choice.

Table Scans

A table scan reads every row.

Usually indicates:

• Missing indexes
• Non-selective predicates
• Small tables
• Poor query design

Table scans on very large tables often signal optimization opportunities.

Join Operators

SQL Server selects join algorithms based on estimated costs.

Nested Loops

Best for:

• Small inputs
• Indexed lookups

Merge Join

Best for:

• Large sorted datasets

Requires sorted input.

Hash Match

Best for:

• Large unsorted datasets

Uses more memory but often performs well for analytical workloads.

Cost Percentage

Execution plans display estimated operator costs.

Example:

Hash Match
85%
Index Seek
10%
Sort
5%

Important exam point:

Cost percentages are optimizer estimates—not actual elapsed execution time.

Execution Plan Warnings

Execution plans may display warnings such as:

• Missing indexes
• Implicit conversions
• Spills to tempdb
• Missing statistics
• Excessive memory grants

Warnings often identify the root cause of performance issues.

Missing Index Recommendations

Execution plans sometimes recommend indexes.

Example:

Missing Index (Impact 98%)

These recommendations can significantly improve performance but should be evaluated carefully rather than implemented automatically, because they don’t consider overall workload or maintenance costs.

Dynamic Management Views (DMVs)

DMVs expose real-time information about SQL Server’s internal state.

They are invaluable for monitoring:

• Active requests
• Query statistics
• Index usage
• Wait statistics
• Sessions
• Transactions
• Memory usage
• Cached execution plans

Common Performance DMVs

sys.dm_exec_query_stats

Provides cumulative statistics for cached query plans.

Useful columns include:

• Total CPU time
• Total logical reads
• Total elapsed time
• Execution count

Example:

SELECT TOP 10
    total_worker_time,
    execution_count
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;

sys.dm_exec_sql_text()

Returns the SQL text associated with cached plans.

Often joined with:

sys.dm_exec_query_stats

sys.dm_exec_query_plan()

Returns XML execution plans.

Useful for automated analysis.

sys.dm_exec_requests

Shows currently executing requests.

Useful for identifying:

• Blocking
• Long-running queries
• Wait types

sys.dm_exec_sessions

Shows active user sessions.

Useful for monitoring connected users.

sys.dm_os_wait_stats

Displays cumulative wait statistics.

Common waits include:

• PAGEIOLATCH
• CXPACKET
• LCK_M_X
• WRITELOG

Wait statistics often reveal the primary performance bottleneck.

sys.dm_db_index_usage_stats

Shows how indexes are used.

Helps identify:

• Unused indexes
• Frequently used indexes
• Missing optimization opportunities

Query Store

Query Store is one of SQL Server’s most valuable performance features.

Introduced in SQL Server 2016.

It automatically captures:

• Query text
• Execution plans
• Runtime statistics
• Wait statistics
• Plan history

Unlike DMVs, Query Store persists data across server restarts.

Benefits of Query Store

Query Store helps developers:

• Identify slow queries
• Detect regressions
• Compare execution plans
• Force known-good execution plans
• Analyze historical performance
• Monitor workload changes

It is widely used for production performance tuning.

Query Store Architecture

Query Store stores:

• Query text
• Multiple execution plans
• Runtime statistics
• Wait statistics
• Historical performance

This historical information makes it much easier to diagnose intermittent issues.

Detecting Query Regressions

A query regression occurs when a query suddenly becomes slower.

Common causes include:

• Updated statistics
• New indexes
• Parameter sniffing
• Schema changes
• Data growth

Query Store can compare previous and current execution plans to identify regressions.

Forcing Execution Plans

If SQL Server selects an inefficient plan, Query Store allows administrators to force a previously successful plan.

Benefits include:

• Immediate performance stabilization
• Reduced troubleshooting time

Forced plans should still be monitored because future schema or workload changes may make a different plan more appropriate.

Query Store Wait Statistics

Modern versions of SQL Server also capture wait statistics per query.

Examples include:

• CPU waits
• Lock waits
• I/O waits
• Memory waits

This makes troubleshooting significantly easier.

Query Performance Insight

Query Performance Insight is an Azure SQL Database performance monitoring feature available in the Azure portal.

It provides visual dashboards that display:

• Top resource-consuming queries
• CPU utilization
• Duration
• Execution count
• Database workload trends
• Historical performance

It simplifies performance analysis without requiring T-SQL queries.

Benefits of Query Performance Insight

Advantages include:

• Visual performance analysis
• Historical trends
• Easy identification of expensive queries
• Azure portal integration
• Supports Azure SQL Database

It is especially useful for cloud database administrators.

Common Performance Problems

Missing Indexes

Symptoms:

• Table scans
• High logical reads

Solution:

Create appropriate indexes after evaluating workload impact.

Outdated Statistics

Symptoms:

• Poor execution plans
• Incorrect row estimates

Solution:

Update statistics.

UPDATE STATISTICS Sales;

Parameter Sniffing

Occurs when SQL Server caches an execution plan optimized for one parameter value that performs poorly for others.

Possible solutions include:

• Query Store plan forcing
• OPTION (RECOMPILE)
• OPTIMIZE FOR
• Query rewriting

Implicit Conversions

Example:

WHERE CustomerID='100'

if CustomerID is an integer.

Implicit conversions may prevent index seeks.

Use matching data types whenever possible.

Excessive Key Lookups

Frequent Key Lookup operators may indicate that a covering index would improve performance.

Best Practices

Use Actual Execution Plans

Actual plans reveal runtime behavior and often expose problems that estimated plans cannot.

Review Missing Index Recommendations Carefully

Evaluate:

• Existing indexes
• Maintenance overhead
• Duplicate indexes

Do not automatically implement every recommendation.

Monitor Query Store Regularly

Review:

• Regressions
• Forced plans
• Runtime statistics
• Wait statistics

Monitor Wait Statistics

Focus on the largest waits rather than individual slow queries.

Wait analysis often identifies system-wide bottlenecks.

Update Statistics

Accurate statistics enable the optimizer to generate better execution plans.

Remove Unused Indexes

Too many indexes:

• Increase storage
• Slow inserts
• Slow updates
• Slow deletes

DMVs help identify unused indexes.

Keep Statistics Current

Automatic statistics are helpful but may not always update quickly enough for rapidly changing data.

Performance Tuning Workflow

A common performance tuning process is:

1. Identify a slow query.
2. Capture the actual execution plan.
3. Review Query Store history.
4. Check DMVs for CPU, I/O, and wait statistics.
5. Identify inefficient operators.
6. Evaluate indexing opportunities.
7. Update statistics if needed.
8. Test improvements.
9. Monitor results.

DP-800 Exam Tips

Remember these key points for the exam:

• Actual execution plans contain runtime statistics, while estimated execution plans do not execute the query.
• An Index Seek is generally more efficient than an Index Scan when retrieving a small subset of rows.
• DMVs provide real-time diagnostic information but generally reset when SQL Server restarts or the execution plan cache is cleared.
• Query Store retains historical query performance information across restarts.
• Query Store can detect query regressions and force a previous execution plan.
• Query Performance Insight provides Azure portal dashboards for Azure SQL Database performance analysis.
• Execution plan cost percentages are optimizer estimates, not measurements of actual elapsed time.
• Missing index recommendations should be evaluated carefully rather than applied automatically.

Practice Exam Questions

Question 1

A database administrator wants to determine how SQL Server actually executed a query, including runtime row counts and operator statistics. Which tool should be used?

A. Actual Execution Plan
B. Estimated Execution Plan
C. Query Performance Insight
D. sys.dm_db_index_usage_stats

Correct Answer: A

Explanation: The Actual Execution Plan executes the query and records runtime information such as actual row counts, memory usage, and operator performance. Estimated plans only predict how the query will execute.

Question 2

A query retrieves a single customer by using a highly selective indexed column. Which execution plan operator would typically provide the best performance?

A. Table Scan
B. Clustered Index Scan
C. Index Seek
D. Hash Match

Correct Answer: C

Explanation: An Index Seek efficiently navigates directly to the qualifying rows within an index, minimizing I/O and improving performance for selective queries.

Question 3

Which Dynamic Management View (DMV) provides cumulative performance statistics for cached query plans?

A. sys.dm_exec_query_stats
B. sys.dm_exec_sessions
C. sys.dm_db_index_usage_stats
D. sys.dm_exec_requests

Correct Answer: A

Explanation: sys.dm_exec_query_stats stores cumulative statistics such as total worker time, logical reads, elapsed time, and execution count for cached query plans.

Question 4

A developer wants to analyze historical query performance and compare execution plans before and after a deployment. Which feature should be used?

A. Activity Monitor
B. SQL Server Profiler
C. Dynamic Management Views
D. Query Store

Correct Answer: D

Explanation: Query Store stores historical query text, execution plans, runtime statistics, and wait statistics, allowing developers to compare performance across deployments.

Question 5

Which statement about Query Store is true?

A. It only stores data until SQL Server restarts.
B. It automatically captures query history and execution plans.
C. It replaces execution plans entirely.
D. It only works with Azure SQL Database.

Correct Answer: B

Explanation: Query Store automatically captures query text, execution plans, runtime statistics, and historical performance data. Unlike many DMVs, its data persists across restarts.

Question 6

Which Azure SQL Database feature provides graphical dashboards that identify high-resource queries and workload trends?

A. Database Mail
B. Query Performance Insight
C. SQL Trace
D. Extended Events

Correct Answer: B

Explanation: Query Performance Insight provides Azure portal dashboards that visualize CPU usage, query duration, execution counts, and historical performance trends.

Question 7

An execution plan displays a warning indicating a “Missing Index (Impact 96%).” What is the best course of action?

A. Immediately create the recommended index without review.
B. Ignore the recommendation because SQL Server recommendations are unreliable.
C. Evaluate the recommendation alongside the overall workload before deciding whether to implement it.
D. Rebuild every existing index first.

Correct Answer: C

Explanation: Missing index recommendations are useful starting points, but developers should consider existing indexes, maintenance overhead, and workload characteristics before implementation.

Question 8

Which situation most commonly causes an Index Scan instead of an Index Seek?

A. Searching by a primary key value
B. Filtering with a function applied to an indexed column, such as YEAR(OrderDate)
C. Using an equality predicate on an indexed column
D. Retrieving a single row by a unique index

Correct Answer: B

Explanation: Applying functions to indexed columns often makes predicates non-SARGable, preventing efficient index seeks and causing SQL Server to scan the index instead.

Question 9

A developer wants to identify currently executing queries that are waiting on locks or consuming excessive resources. Which DMV is most appropriate?

A. sys.dm_exec_requests
B. sys.dm_exec_query_plan
C. sys.dm_db_index_usage_stats
D. sys.dm_os_wait_stats

Correct Answer: A

Explanation: sys.dm_exec_requests displays currently executing requests, including wait types, blocking information, CPU usage, elapsed time, and execution status.

Question 10

Why are database statistics important for query optimization?

A. They permanently eliminate table scans.
B. They encrypt execution plans.
C. They reduce transaction log size.
D. They help the Query Optimizer estimate row counts and choose efficient execution plans.

Correct Answer: D

Explanation: SQL Server relies on statistics to estimate data distribution and row counts. Accurate statistics allow the Query Optimizer to select efficient join methods, indexes, and execution strategies, resulting in better overall query performance.

Go to the DP-800 Exam Prep Hub main page