Query Performance: Plans, DMVs, and Query Store

Finding the slow queries

Execution plans

Every query goes through the query optimiser, which generates an execution plan — the step-by-step instructions for retrieving data.

-- Show estimated plan (does not execute the query)
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Orders WHERE CustomerId = 42;
GO
SET SHOWPLAN_XML OFF;

-- Show actual plan (executes and shows real row counts)
SET STATISTICS XML ON;
SELECT * FROM Orders WHERE CustomerId = 42;
SET STATISTICS XML OFF;

What to look for in a plan

Red Flag	What It Means	Fix
Table Scan	No useful index — reading every row	Create an appropriate index
Key Lookup	Index found the row but needs extra columns	Add INCLUDE columns to make a covering index
Sort with high cost	Sorting in memory (or spilling to disk)	Add an index with the ORDER BY column
Nested Loops on large tables	Row-by-row processing	Consider Hash Join or Merge Join (may need statistics update)
Thick arrows	Large number of rows flowing between operators	Filter earlier, add WHERE predicates
Yellow warning triangle	Implicit conversion, missing stats, memory grant warning	Fix data type mismatches, update statistics

Dynamic Management Views (DMVs)

DMVs expose real-time server internals. Key DMVs for performance:

-- Top 10 queries by total CPU time
SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
        (CASE WHEN qs.statement_end_offset = -1 THEN LEN(qt.text)
         ELSE qs.statement_end_offset/2 END - qs.statement_start_offset/2) + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;

DMV	What It Shows
sys.dm_exec_query_stats	Aggregated query performance (CPU, reads, duration)
sys.dm_exec_requests	Currently executing requests (blocking, waits)
sys.dm_exec_sessions	Active sessions and their state
sys.dm_db_index_usage_stats	Which indexes are being used (and which are not)
sys.dm_db_missing_index_details	Indexes the optimiser wishes it had
sys.dm_os_wait_stats	Server-wide wait statistics (what the server spends time waiting for)

Query Store

Query Store captures query plans and performance metrics over time. It is the key tool for detecting plan regressions — when a query suddenly gets a worse execution plan.

-- Enable Query Store
ALTER DATABASE [MyDB] SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1024,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    QUERY_CAPTURE_MODE = AUTO
);

Query Store capabilities

• Track query performance over time (CPU, duration, reads, writes per plan)
• Detect regressions — compare current performance to a baseline
• Force plans — pin a known-good execution plan to a query
• View plan history — see all plans a query has used and when each was active

💡 Exam tip: Database configurations that affect performance

The exam may ask you to “recommend database configurations.” Key settings:

• MAXDOP (max degree of parallelism) — controls how many CPUs a query can use
• Cost threshold for parallelism — minimum plan cost before parallelism kicks in
• Memory grants — how much memory queries can request for sorts and hashes
• Compatibility level — newer levels enable better optimiser features (batch mode, adaptive joins)
• Auto-tuning (Azure SQL) — automatically forces better plans and creates missing indexes
• READ_COMMITTED_SNAPSHOT — reduces blocking (covered in the previous module)

Query Performance Insight (Azure SQL)

For Azure SQL Database, Query Performance Insight provides a visual dashboard in the Azure portal:

• Top queries by CPU, data IO, or log IO
• Query duration trends over time
• Recommendation integration with Azure SQL Advisor

Question

What is the difference between an estimated and actual execution plan?

Click or press Enter to reveal answer

Answer

An estimated plan shows the optimiser's predicted path WITHOUT executing the query (fast, no data impact). An actual plan EXECUTES the query and shows real row counts, memory grants, and actual vs estimated differences. Use actual plans for diagnosing performance issues.

Click to flip back

Question

What does Query Store do?

Click or press Enter to reveal answer

Answer

Query Store captures query execution plans and performance metrics over time. It enables: tracking query regressions (when a plan changes for the worse), comparing plan performance, forcing known-good plans, and historical analysis. It is the primary tool for plan regression detection.

Click to flip back

Question

Which DMV shows missing indexes?

Click or press Enter to reveal answer

Answer

sys.dm_db_missing_index_details shows indexes the query optimiser wished it had during query compilation. It includes the table, equality columns, inequality columns, and included columns. Use it alongside sys.dm_db_missing_index_group_stats for impact estimates.

Click to flip back

Knowledge Check

After a weekend deployment, Priya notices that a critical report query at Vault Bank went from 2 seconds to 45 seconds. The query has not changed. What is the most likely cause and best diagnostic tool?

AThe table grew over the weekend — check row countsBA plan regression — use Query Store to compare before/after plansCThe server is overloaded — check CPU utilisationDAn index was dropped — check sys.indexes

Check Answer

Next up: SQL Database Projects: Build and Validate — manage your database schema as code with SQL Database Projects.