Tag: Performance Tuning

Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Identify and resolve query performance issues, including blocking and deadlocks – Part 2 (DP-800 Exam Prep)

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:

Identify the slow query.
Capture the execution plan.
Examine wait statistics.
Check index usage.
Review Query Store history.
Examine DMVs.
Optimize the query or indexes.
Test the improvement.
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:

Operator	Cost
Index Seek	5%
Nested Loop	10%
Sort	35%
Hash Match	50%

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:

Fragmentation	Recommended Action
Less than 5%	No action
5–30%	Reorganize
Greater than 30%	Rebuild

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;

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

Data Development, DP-800, Microsoft Certification, SQL August 1, 2026

Identify and resolve query performance issues, including blocking and deadlocks – Part 1 (DP-800 Exam Prep)

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

Efficient query performance is one of the most important responsibilities of a SQL developer. Regardless of whether a database is hosted in SQL Server, Azure SQL Database, Azure SQL Managed Instance, or Microsoft Fabric SQL Database, applications depend on queries executing quickly while maintaining data consistency and supporting concurrent users.

Poor-performing queries can cause excessive CPU usage, memory pressure, storage bottlenecks, long response times, and application outages. Likewise, poorly managed concurrency can result in blocking and deadlocks that significantly impact user productivity.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, candidates should understand how SQL Server manages concurrent transactions, recognize common performance issues, detect blocking and deadlocks, and apply best practices to resolve these problems.

Learning Objectives

After completing this article, you should be able to:

Explain why query performance optimization is important.
Identify common causes of poor query performance.
Understand SQL Server locking behavior.
Explain blocking and deadlocks.
Recognize how transaction isolation levels affect concurrency.
Detect blocking sessions.
Detect deadlocks.
Apply techniques to reduce blocking and deadlocks.
Troubleshoot real-world concurrency problems.

Why Query Performance Matters

Every SQL query consumes system resources. Poorly optimized queries consume more resources than necessary and may affect every user connected to the database.

Common consequences include:

Slow application response times
High CPU utilization
Excessive memory consumption
Increased disk I/O
Long-running transactions
Lock contention
Blocking
Deadlocks
Reduced scalability

Database performance is not solely about executing a single query quickly—it is about enabling thousands of users to work simultaneously without interfering with each other.

Common Causes of Poor Query Performance

Many performance problems originate from inefficient query design.

Common causes include:

Missing Indexes

Without appropriate indexes, SQL Server performs table scans rather than index seeks.

Instead of reading a few rows:

CustomerID = 1205

SQL Server may need to scan millions of rows.

Symptoms include:

High logical reads
High physical reads
Increased CPU usage
Long execution times

Poor Index Design

Too many indexes can slow writes.

Too few indexes slow reads.

Poor index design includes:

Incorrect clustered indexes
Missing covering indexes
Duplicate indexes
Unused indexes
Highly fragmented indexes

Returning More Data Than Necessary

Instead of:

			
SELECT *
FROM Sales.Orders;

Use:

			
SELECT OrderID,
       CustomerID,
       OrderDate
FROM Sales.Orders;

Benefits include:

Reduced network traffic
Less memory usage
Faster execution
Smaller execution plans

Non-SARGable Queries

SARGable means Search Argument Able.

Bad example:

WHERE YEAR(OrderDate) = 2025

Because SQL Server must calculate YEAR() for every row.

Better:

			
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'

Now an index on OrderDate can be used.

Implicit Data Type Conversions

Example:

WHERE CustomerID = '100'

if CustomerID is an integer.

SQL Server may convert every value before comparison.

Better:

WHERE CustomerID = 100

Outdated Statistics

Statistics help the optimizer estimate row counts.

Outdated statistics lead to:

Poor cardinality estimates
Incorrect join choices
Bad execution plans
Longer execution times

Parameter Sniffing

Stored procedures reuse cached execution plans.

A plan optimized for:

CustomerID = 1

may perform poorly for:

CustomerID = 999999

DP-800 candidates should understand that parameter sniffing can sometimes degrade performance and that techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, or query hints may be used selectively to address it.

Understanding Locking

SQL Server uses locks to ensure:

Data consistency
Transaction isolation
Integrity during concurrent access

Locks prevent conflicting operations from occurring simultaneously.

Example:

User A updates:

OrderID = 100

Before User A commits,

User B attempts to update the same row.

SQL Server places User B into a waiting state until User A completes.

This waiting is called blocking.

Types of Locks

Several lock types are important for the DP-800 exam.

Shared (S)

Used for reading.

Multiple users may hold Shared locks simultaneously.

Example:

SELECT

Exclusive (X)

Used for modifications.

Example:

			
UPDATE
DELETE
INSERT

Only one Exclusive lock can exist on a resource.

Update (U)

Used during updates.

Prevents certain deadlock scenarios.

Typically upgraded to an Exclusive lock when data is modified.

Intent Locks

Used internally.

Examples include:

These indicate SQL Server intends to place locks at lower levels.

Schema Locks

Protect database object definitions.

Examples:

ALTER TABLE

CREATE INDEX

Lock Granularity

SQL Server can lock at multiple levels.

Row
Key
Page
Extent
Table
Database

Smaller locks improve concurrency.

Larger locks reduce overhead but may increase blocking.

Lock Escalation

SQL Server may automatically replace many row locks with a table lock.

Example:

Instead of:

20,000 row locks

SQL Server escalates to:

One table lock

Benefits:

Lower memory usage

Drawback:

More blocking

Understanding Blocking

Blocking occurs when one session waits for another session to release a lock.

Example

Session 1:

			
BEGIN TRANSACTION;
UPDATE Products
SET Price = Price * 1.05
WHERE ProductID = 5;

Transaction remains open.

Session 2:

			
SELECT *
FROM Products
WHERE ProductID = 5;

Session 2 waits.

This is normal behavior.

Blocking protects data consistency.

When Blocking Becomes a Problem

Short blocking is expected.

Long blocking causes:

Slow applications
Timeouts
User frustration
Connection pooling issues
Increased resource usage

Common causes include:

Long-running transactions
User interaction inside transactions
Large batch updates
Missing indexes
Table scans
Poor query design

Understanding Deadlocks

A deadlock occurs when two or more sessions permanently wait for each other.

Example

Session A

Locks:

Customers

Needs:

Orders

Session B

Locks:

Orders

Needs:

Customers

Neither session can continue.

SQL Server automatically detects the deadlock.

One transaction becomes the deadlock victim.

Its transaction is rolled back.

The other transaction continues.

Deadlock Example

Transaction A

			
BEGIN TRANSACTION;
UPDATE Customers
SET CreditLimit = 1000
WHERE CustomerID = 1;
UPDATE Orders
SET Status = 'Approved'
WHERE OrderID = 100;
COMMIT;

		

Transaction B

			
BEGIN TRANSACTION;
UPDATE Orders
SET Status = 'Pending'
WHERE OrderID = 100;
UPDATE Customers
SET CreditLimit = 900
WHERE CustomerID = 1;
COMMIT;

		

If both transactions execute simultaneously:

Transaction A locks Customers
Transaction B locks Orders
Each waits for the other’s lock

SQL Server detects the cycle and terminates one transaction.

Blocking vs. Deadlocks

Blocking	Deadlock
Temporary waiting	Circular waiting
Usually resolves automatically	Requires SQL Server intervention
No transaction rollback	One transaction rolled back
Normal behavior	Undesirable behavior
Caused by incompatible locks	Caused by cyclic lock dependencies

Transaction Isolation Levels

Isolation levels determine how transactions interact.

They directly affect:

Blocking
Concurrency
Consistency
Performance

READ UNCOMMITTED

Lowest isolation.

Allows dirty reads.

Almost no blocking.

Example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Advantages

Very fast

Disadvantages

Reads uncommitted data

READ COMMITTED (Default)

Most common.

Prevents dirty reads.

Allows non-repeatable reads.

Balanced performance and consistency.

REPEATABLE READ

Protects rows already read.

Increases locking.

More blocking.

SERIALIZABLE

Highest isolation.

Maximum consistency.

Most locking.

Greatest blocking potential.

SNAPSHOT Isolation

Uses row versioning.

Readers do not block writers.

Writers do not block readers.

Advantages:

High concurrency
Fewer blocking issues
Better scalability

Requires enabling snapshot isolation in the database.

Choosing the Appropriate Isolation Level

Isolation Level	Dirty Reads	Blocking	Concurrency
READ UNCOMMITTED	Yes	Very Low	Very High
READ COMMITTED	No	Moderate	Good
REPEATABLE READ	No	Higher	Moderate
SERIALIZABLE	No	Highest	Lowest
SNAPSHOT	No	Low	Excellent

Detecting Blocking

Several tools can identify blocking.

Common methods include:

SQL Server Management Studio Activity Monitor
Dynamic Management Views (DMVs)
Extended Events
SQL Server Profiler (legacy)
Azure SQL monitoring tools
Microsoft Fabric monitoring experiences

One useful DMV query is:

			
SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

		

This displays:

Waiting session
Blocking session
Wait type
Wait duration
Locked resource

Detecting Deadlocks

SQL Server automatically detects deadlocks.

Detection methods include:

Extended Events
System Health session
SQL Server Profiler (legacy)
Azure SQL Intelligent Insights
Deadlock graphs
SQL Server error logs (when configured)

Deadlock graphs visually display:

Victim process
Lock owners
Waiting processes
Resources involved

These graphs are invaluable for identifying the exact sequence of events that caused the deadlock.

Best Practices to Prevent Blocking and Deadlocks

Microsoft recommends several strategies to minimize concurrency issues:

Keep transactions as short as possible.
Commit or roll back transactions promptly.
Access tables in a consistent order across all applications.
Create appropriate indexes to reduce scan times.
Avoid user interaction while a transaction is open.
Use the lowest appropriate isolation level for the workload.
Consider Snapshot Isolation or Read Committed Snapshot Isolation (RCSI) for read-heavy environments.
Break large updates into smaller batches.
Regularly maintain indexes and statistics.
Monitor blocking trends and deadlock frequency proactively.

Real-World Troubleshooting Scenarios

Scenario 1: Long-Running Transaction

A reporting application begins a transaction and leaves it open while waiting for user input. Meanwhile, hundreds of users attempting to update the same data experience delays.

Resolution: Redesign the application so that user interaction occurs before the transaction begins or after it commits, minimizing the transaction’s duration.

Scenario 2: Deadlocks During Order Processing

Two stored procedures update the Customers and Orders tables but access them in different sequences.

Resolution: Standardize the order in which tables are accessed (for example, always update Customers before Orders) to eliminate the circular dependency that causes deadlocks.

Scenario 3: Blocking Caused by Table Scans

A frequently executed query scans millions of rows because no suitable index exists. The scan holds locks long enough to block other sessions.

Resolution: Create an appropriate nonclustered index and rewrite the query to be SARGable so that SQL Server can perform index seeks instead of table scans.

DP-800 Exam Tips

Understand the difference between blocking and deadlocks.
Know how transaction isolation levels affect concurrency and locking behavior.
Recognize that blocking is a normal mechanism to preserve consistency, whereas deadlocks are abnormal conditions that SQL Server resolves by selecting a victim transaction.
Be familiar with common lock types, including Shared, Exclusive, Update, Intent, and Schema locks.
Know that Snapshot Isolation and Read Committed Snapshot Isolation (RCSI) use row versioning to reduce reader-writer blocking.
Understand that long-running transactions, missing indexes, inconsistent object access order, and poor query design are common causes of blocking and deadlocks.
Be comfortable using DMVs and monitoring tools to identify blocking sessions before moving on to advanced analysis with execution plans and Query Store (covered in Part 2).

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Evaluate query performance by using query execution plans, dynamic management views (DMVs), Query Store, and Query Performance Insight (DP-800 Exam Prep)

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:

Parse the T-SQL statement
Validate syntax and object names
Optimize the query
Generate an execution plan
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:

Operator	Purpose
Table Scan	Reads every row in a table
Clustered Index Scan	Scans an entire clustered index
Index Seek	Efficiently locates matching rows
Key Lookup	Retrieves additional columns from a clustered index
Nested Loops	Efficient join for small result sets
Merge Join	Efficient for sorted data
Hash Match	Efficient for large unsorted datasets
Sort	Orders rows
Compute Scalar	Calculates expressions
Filter	Applies predicates

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:

Identify a slow query.
Capture the actual execution plan.
Review Query Store history.
Check DMVs for CPU, I/O, and wait statistics.
Identify inefficient operators.
Evaluate indexing opportunities.
Update statistics if needed.
Test improvements.
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

Business Intelligence, Data Warehousing, Performance Tuning February 22, 2015

OBIEE Performance Tuning

This post describes a few tips and things to keep in mind for OBIEE Performance Tuning.

Be Proactive when possible
The need to performance tune can be proactive (tune before a major issue arises) or reactive (tune after a problem is reported by users for example). It is best to be proactive – so performance tuning should be built into your OBIEE maintenance schedule. For example, OBIEE’s Usage Tracking functionality should be used regularly to identify reports whose performance can be improved and then performance steps should be carried out on the worst performers.

Iterative Process – change one thing or set of things at a time
One of the first things to keep in mind is that performance tuning is an iterative process. And there is typically no one silver bullet that will resolve all your performance problems. You may need to analyze and make changes to multiple parts of the system, but you want to make the changes methodically. It is best to change one parameter or setting at the same time (or one related set of parameters). Adjust and test the settings for that one parameter/setting (or set of parameters) before moving on to another. If you change too much at one time, you may have a difficulty determining what is helping from what is hurting your efforts.

Fix user complaints first, worst performers next, and then the next bad performers down the list
Another thing to keep in mind, tune what users are reporting first, then tune the worst problems second, then move on to the next.

Team Effort – problem could be anywhere along the technology stack
Performance problems could be anywhere along the technology stack:
• OBIEE
• Database
• Server
• Network
Due to that span of technology, performance tuning is a team effort. OBIEE Admins and Developers, DBAs, and ETL Developers can all be key to solving performance issues.
Logs from all components may need to be reviewed depending on the scenario.

Try to isolate or narrow-down the source of the problem
For example, run the report SQL directly on the database and see if you have the same problem. If there is no issue when run directly on that the database, then you have eliminated the database as the problem.
Determine if other applications have been also been experiencing slowness which could indicate the possibility of a network problem.

If your users have reported an issue, then you need to get as much details as possible about the performance problems they are experiencing. When did this start happening? Is it just one report or many? Is it localized to one business area or multiple? Is it all the time or sometimes? Knowing this will help you to know where to focus.

Other questions to ask as you try to identify the source of the problem include but not limited to:
Has anything changed? If reports were running fine, but are now slow, the first thing to ask is …
When the issue start? Determining exactly when it started might be helpful when correlating with other system or company activity)
What has changed recently? Has there been any system changes, data changes, database updates, network changes, etc. (even if they seem unrelated)? For example, rolling into a new calendar year will cause new “Year” value(s) to be included in the data and can impact performance if statistics are not gathered.
Is there a possibility that an index was dropped and not recreated as expected?

Use OBIEE’s Usage Tracking information to analyze specific reports, analyze long running reports, or frequently run reports. You will want to capture and analyze the SQL from these reports to determine what can be done to improve their performance.

Database
DBAs can monitor the system in real-time, use various tools, or review logs for information that can be helpful in the tuning effort. Tools such as Oracle Enterprise Manager (EM) or SQL Tuning Advisor can be used to identify, analyze and tune high-load SQL.
OBIEE Usage Tracking can also be used to identify high-load SQL.
Without getting into much detail, these are some database features that could be used to help improve performance:
• Gather Statistics
• Results Cache database feature
• Partitioning

Servers
The System Admins can monitor the server resources to determine if there is an issue there.
• Use fast disk for the OBIEE cache and/or temporary files.

OBIEE-specific performance tuning tips

• OBIEE Caching
Are the tables being used set to cacheable?
Is caching turned on at the application level?
You may consider seeding the cache daily.
CACHE Settings:
o MAX_ROWS_PER_CACHE_ENTRY
o MAX_CACHE_ENTRY_SIZE
o MAX_CACHE_ENTRIES
o ——————-
o USE_ADVANCED_HIT_DETECTION

• Use Aggregation: Aggregate data when applicable
o You can use Aggregate tables or materialized views to realize this benefit.
o Aggregate Fact tables and corresponding Aggregate Dimensions.
o Make sure aggregation rules are applied to Fact table measures.
o Don’t necessarily merge all measures into a single fact.

• Joins and Indexes
o Do not create unnecessary joins.
o Verify that the joins on the tables being investigated are appropriate.
o Performance Indexing could be helpful. Again, this is an iterative process.

• Prompts and Filters
o Use LOV tables to drive prompt values when possible, instead of building prompts from large transactional data tables.
o Force filter selection / entry by making prompt values required. Do not allow open ended run of reports.

• Filter out unneeded data. If there is a significant amount of data that is not being used in one or more tables (especially if they are frequently used), then that data should be filtered out by the ETL before it gets joined in SQL, and then has to be filtered out in the RPD or at the report level.

• Enter the “Number of Elements at this level” value in the logical level in hierarchies.
• Also ensure that all logical level keys are unique.

• Avoid function in the where clause when possible.

• Be careful of sub-queries.

• Check out the features of the OBIEE Performance Monitor
http://server:port/analytics/saw.dll?Perfmon (enter your OBI server and port)

• When possible, do comparison analysis to determine for example, why is this report running fine, but this other seemingly similar report is not.

• Use fast disk for the OBIEE cache and/or temporary files.

Sometimes a complete overhaul might be required
Review the users’ workflow and determine if new and improved queries can be written or if the number of queries can be reduced.
Present information from a summary level first, and then provide increasing levels of details as requested by users through drill down or navigation. Basically, present detailed information only when necessary, and minimize the amount of detail provided at a time by filtering on user selections.

Oracle’s OBIEE Performance Tuning Guide
Apply recommendations from the “Best Practices Guide for Infrastructure Tuning Oracle® Business Intelligence Enterprise Edition 11g Release”. I would recommend applying 1 – 3 changes or set of changes at a time; don’t apply everything at the same time because if there is a problem, it will be more difficult to determine which change caused it.
https://blogs.oracle.com/proactivesupportEPM/entry/wp_obiee_tuning_guide