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:

• IS
• IX
• SIX

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

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

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