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.

Best Practices for Preventing Performance Problems

The DP-800 exam emphasizes preventing problems rather than simply reacting to them.

Good database design, indexing, and application coding practices significantly reduce blocking, deadlocks, and poor query performance.

Design Tables Properly

Avoid:

• excessively wide rows
• unnecessary nullable columns
• poor normalization
• over-normalization requiring many joins

Good schema design leads to:

• smaller pages
• fewer logical reads
• shorter lock durations

Use Appropriate Data Types

Poor choices increase memory usage.

Instead of:

NVARCHAR(MAX)

use

NVARCHAR(50)

when appropriate.

Benefits include:

• reduced I/O
• better index efficiency
• improved cache utilization

Keep Transactions Short

One of the biggest causes of blocking is long-running transactions.

Bad:

BEGIN TRAN;
UPDATE Sales
SET Amount = Amount * 1.05;
WAITFOR DELAY '00:05:00';
COMMIT;

Locks remain active for five minutes.

Better:

BEGIN TRAN;
UPDATE Sales
SET Amount = Amount * 1.05;
COMMIT;

Commit Frequently

Instead of updating millions of rows in one transaction:

UPDATE LargeTable
SET Status = 'Complete';

process smaller batches.

Example:

WHILE 1=1
BEGIN
UPDATE TOP (1000) LargeTable
SET Status='Complete'
WHERE Status='Pending';
IF @@ROWCOUNT=0
BREAK;
END

Benefits:

• shorter locks
• reduced log growth
• less blocking

Create Effective Indexes

Missing indexes often lead to:

• table scans
• excessive logical reads
• blocking
• CPU spikes

Create indexes on:

• frequently filtered columns
• join columns
• ORDER BY columns

Example:

CREATE INDEX IX_OrderDate
ON Sales(OrderDate);

Avoid Too Many Indexes

Indexes improve reads.

Indexes slow:

• INSERT
• UPDATE
• DELETE

Every modification updates every affected index.

Balance read performance against write performance.

Maintain Indexes

Over time indexes fragment.

Use:

ALTER INDEX ALL
ON Sales
REBUILD;

or

ALTER INDEX ALL
ON Sales
REORGANIZE;

Generally:

• REORGANIZE for moderate fragmentation
• REBUILD for heavy fragmentation

Write Efficient Queries

Avoid:

SELECT *

Use:

SELECT CustomerID,
       CustomerName

Benefits:

• less network traffic
• narrower execution plans
• smaller memory grants

Filter Early

Instead of processing entire tables:

SELECT *
FROM Sales;

Use:

SELECT *
FROM Sales
WHERE OrderDate >= '2025-01-01';

Avoid Functions on Indexed Columns

Bad:

WHERE YEAR(OrderDate)=2025

This prevents index seeks.

Better:

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

Use EXISTS Instead of IN When Appropriate

Example:

WHERE EXISTS
(
SELECT *
FROM Orders
WHERE Orders.CustomerID=Customers.CustomerID
)

Often performs better on large datasets.

Parameter Sniffing

Parameter sniffing occurs when SQL Server optimizes a stored procedure using the first parameter values it receives.

Example:

EXEC GetOrders 1;

The plan is cached.

Later:

EXEC GetOrders 100000;

The same plan may perform poorly.

Possible solutions:

• OPTION (RECOMPILE)
• OPTIMIZE FOR
• local variables
• Query Store plan forcing

Monitor Wait Statistics

Wait statistics reveal what SQL Server spends time waiting on.

Common waits include:

Query:

SELECT *
FROM sys.dm_os_wait_stats;

Wait statistics help identify the true bottleneck before making changes.

Monitor Resource Usage

Useful DMVs include:

CPU:

sys.dm_exec_query_stats

Memory:

sys.dm_os_memory_clerks

Locks:

sys.dm_tran_locks

Sessions:

sys.dm_exec_sessions

Requests:

sys.dm_exec_requests

Query Store Best Practices

Enable Query Store on production databases.

Benefits:

• captures historical plans
• tracks regressions
• compares runtime statistics
• forces known good plans

Avoid disabling Query Store unless troubleshooting specific issues.

Azure SQL Automatic Performance Features

Azure SQL Database provides automatic tuning.

Features include:

• Automatic index creation
• Automatic index removal
• Automatic plan correction
• Automatic plan regression detection

These features reduce administrative effort.

Common DP-800 Exam Tips

Know the differences between:

Summary

To excel in the DP-800 exam, you should be able to:

• Interpret execution plans and identify expensive operators.
• Use Query Store to identify regressions and force stable plans.
• Query DMVs to diagnose slow-running queries, blocking, waits, and resource consumption.
• Recognize and resolve blocking by shortening transactions, adding indexes, or using appropriate isolation levels.
• Detect deadlocks with Extended Events, deadlock graphs, and system health sessions.
• Understand common wait types and how they relate to CPU, I/O, memory, and locking issues.
• Apply indexing, statistics maintenance, and efficient query-writing techniques to prevent performance problems.
• Explain how Azure SQL automatic tuning can improve query performance and reduce administrative overhead.
• Identify parameter sniffing scenarios and select appropriate mitigation strategies.

Practice Exam Questions

Question 1

A stored procedure performs well for some parameter values but poorly for others because SQL Server reuses a cached execution plan. Which performance issue is occurring?

A. Lock escalation

B. Parameter sniffing

C. Deadlocking

D. Page compression

Answer: B

Explanation:
Parameter sniffing occurs when SQL Server generates and caches an execution plan based on the first parameter values used. Subsequent executions with significantly different parameter values may reuse an inefficient plan, resulting in poor performance.

Question 2

A database administrator wants to reduce blocking caused by long-running UPDATE statements that affect millions of rows. Which approach is most effective?

A. Increase the database compatibility level

B. Disable Query Store

C. Process updates in smaller batches and commit frequently

D. Force all queries to use parallel execution

Answer: C

Explanation:
Breaking large modifications into smaller batches shortens transaction duration, releases locks more quickly, reduces transaction log growth, and minimizes blocking for other sessions.

Question 3

Which query is more likely to prevent SQL Server from performing an index seek on an indexed OrderDate column?

A.

WHERE OrderDate >= '2025-01-01'

B.

WHERE OrderDate BETWEEN '2025-01-01' AND '2025-12-31'

C.

WHERE YEAR(OrderDate) = 2025

D.

WHERE OrderDate < '2026-01-01'

Answer: C

Explanation:
Applying a function such as YEAR() to an indexed column makes the predicate non-SARGable, often preventing SQL Server from using an index seek and forcing an index or table scan instead.

Question 4

Which DMV provides information about current lock resources held by transactions?

A. sys.dm_exec_query_stats

B. sys.dm_os_wait_stats

C. sys.dm_exec_sessions

D. sys.dm_tran_locks

Answer: D

Explanation:
sys.dm_tran_locks displays active lock information, including lock types, resources, and owning sessions, making it valuable when investigating blocking.

Question 5

Why should developers avoid using SELECT * in production queries whenever possible?

A. It always causes deadlocks.

B. It automatically disables indexes.

C. It retrieves unnecessary columns, increasing I/O and network traffic.

D. It prevents Query Store from capturing execution statistics.

Answer: C

Explanation:
Selecting only the required columns reduces disk reads, network traffic, memory usage, and execution costs while allowing SQL Server to generate more efficient execution plans.

Question 6

A SQL Server database contains heavily fragmented indexes after months of frequent updates. Which maintenance task should typically be performed when fragmentation is high?

A. Update statistics only

B. Rebuild the indexes

C. Shrink the database

D. Clear the plan cache

Answer: B

Explanation:
An index rebuild recreates the index structure, removes fragmentation, and updates index statistics. It is generally recommended when fragmentation is significant.

Question 7

A developer notices frequent LCK_M_X waits in SQL Server. What do these waits indicate?

A. CPU saturation

B. Memory allocation failures

C. Sessions waiting for exclusive locks

D. Network latency

Answer: C

Explanation:
LCK_M_X wait types indicate sessions waiting to acquire exclusive locks that are currently held by other transactions, suggesting blocking.

Question 8

Which Azure SQL feature can automatically detect a query plan regression and restore a previously better-performing execution plan?

A. Intelligent Insights

B. Automatic Plan Correction

C. Azure Monitor Alerts

D. Elastic Jobs

Answer: B

Explanation:
Automatic Plan Correction, part of Azure SQL automatic tuning, identifies query regressions and can force a previously efficient execution plan automatically.

Question 9

Which practice best helps prevent blocking in high-concurrency OLTP systems?

A. Keep transactions as short as possible.

B. Disable indexes during business hours.

C. Increase page size.

D. Use SELECT * in all reporting queries.

Answer: A

Explanation:
Short transactions reduce the amount of time locks are held, allowing other sessions to access data sooner and minimizing blocking.

Question 10

A DBA wants to determine whether SQL Server is primarily waiting on disk I/O, locking, or CPU scheduling before making performance changes. Which diagnostic information should be examined first?

A. Database file sizes

B. Transaction log backup history

C. Wait statistics

D. Server collation settings

Answer: C

Explanation:
Wait statistics provide a high-level overview of where SQL Server spends its time waiting, making them one of the best starting points for diagnosing performance bottlenecks before making tuning decisions.

Go to the DP-800 Exam Prep Hub main page