SQL Server Performance Review

Start with Query Store

Query Store keeps per-plan runtime statistics inside the database, so it answers “what changed?” after the event. It is on by default for new databases in SQL Server 2022 and in Azure SQL Database; on older versions, check sys.database_query_store_options first. This ranks queries by total duration over the last seven days, which is usually more useful than the slowest single execution:

WITH totals AS (
  SELECT p.query_id,
         SUM(rs.count_executions)                            AS executions,
         SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS total_duration_ms,
         SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS total_cpu_ms,
         SUM(rs.avg_logical_io_reads * rs.count_executions)  AS total_logical_reads,
         COUNT(DISTINCT p.plan_id)                           AS plan_count
  FROM sys.query_store_plan AS p
  JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS i
    ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
  WHERE i.start_time >= DATEADD(day, -7, SYSUTCDATETIME())
  GROUP BY p.query_id
)
SELECT TOP (20) t.*, LEFT(qt.query_sql_text, 200) AS query_text
FROM totals AS t
JOIN sys.query_store_query AS q       ON q.query_id = t.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
ORDER BY t.total_duration_ms DESC;

Durations and CPU in Query Store are stored in microseconds; the query converts them to milliseconds. A plan_count above one is the first sign of a plan change worth comparing.

Compare plans for one query

SELECT p.query_id, p.plan_id, p.is_forced_plan,
       MIN(rs.first_execution_time) AS first_seen,
       MAX(rs.last_execution_time)  AS last_seen,
       SUM(rs.count_executions)     AS executions,
       SUM(rs.avg_duration * rs.count_executions)
         / NULLIF(SUM(rs.count_executions), 0) / 1000.0 AS avg_duration_ms
FROM sys.query_store_plan AS p
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE p.query_id = @query_id
GROUP BY p.query_id, p.plan_id, p.is_forced_plan
ORDER BY last_seen DESC;

If an older plan was clearly better, sp_query_store_force_plan can pin it while the cause is fixed. Treat forcing as a temporary, monitored change: a forced plan can fail if an index it depends on is dropped, and it hides the reason the optimizer chose differently. Check force_failure_count in sys.query_store_plan afterwards.

Parameter sensitivity

One cached plan, compiled for the first parameter value, can be poor for other values: the SQL Server form of the problem in the PostgreSQL generic-plan lab. SQL Server 2022 at database compatibility level 160 adds Parameter Sensitive Plan optimization, which can cache more than one plan variant for a qualifying query; variants appear in sys.query_store_query_variant. Before reaching for OPTION (RECOMPILE) or OPTIMIZE FOR, compare the plans and row counts for a small and a large parameter value and weigh the extra compile cost against the gain. The parameter sniffing lab measures PSP, RECOMPILE, OPTIMIZE FOR UNKNOWN and a covering index on the same data.

Find the head of a blocking chain

SELECT r.session_id, r.blocking_session_id, r.wait_type,
       r.wait_time AS wait_ms, r.wait_resource,
       LEFT(t.text, 120) AS running_sql
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

SELECT s.session_id, s.status, s.open_transaction_count,
       s.program_name, s.host_name, s.login_name,
       DATEDIFF(second, s.last_request_end_time, SYSDATETIME()) AS idle_s
FROM sys.dm_exec_sessions AS s
WHERE s.session_id IN (SELECT blocking_session_id
                       FROM sys.dm_exec_requests
                       WHERE blocking_session_id <> 0);

The blocker is often a session with an open transaction and no running request, so it does not appear in sys.dm_exec_requests itself; open_transaction_count, program_name and idle time point at the application path. Under the default read-committed isolation, readers wait on writers (LCK_M_S). Read Committed Snapshot Isolation removes most reader-writer blocking at the cost of row versions in tempdb and extra write overhead, and it changes what concurrent readers see, so it needs testing rather than switching on during an incident.

A common ORM cost: implicit conversion

EF Core sends .NET strings as nvarchar parameters unless the property is mapped as non-Unicode. Against a varchar column with a SQL Server collation, the comparison converts the column instead of the parameter. In a lab on SQL Server 2022 with an index on a varchar(10) column, the same five-row lookup did an index seek with 3 logical reads when the parameter was varchar, and an index scan with 495 logical reads and CONVERT_IMPLICIT in the plan when it was nvarchar. Mapping the property with IsUnicode(false) or the matching column type removes the conversion. Look for CONVERT_IMPLICIT warnings in the plans of the top Query Store statements.

What a review needs

• Query Store access for the affected database, or exports of the queries above for the problem window
• Actual execution plans for the dominant or regressed statements
• Wait statistics and blocking snapshots captured while the problem is happening
• Version, edition, compatibility level, and recent deploys, index or statistics changes

SQL Server reviews are scoped individually after a short fit review. See capabilities for how the method carries across platforms.

SQL Server articles

My SQL Server tutorials are published on MSSQLServer.dev: window functions, CTEs, isolation levels, row versioning, temporal tables, change tracking and In-Memory OLTP. This site keeps the production-performance material, such as the parameter sniffing lab.