SQL Server Interview Question #140

How do you troubleshoot a slow SQL Server query?

Indexes & Query Performance Senior Advanced

Detailed Explanation

A systematic approach is more reliable than immediately adding indexes. First reproduce and measure the problem, then inspect the actual execution plan and runtime metrics.

Check logical reads, CPU time, elapsed time, row-count estimates versus actual rows, scans, expensive lookups, sorts, spills, blocking, parameter sensitivity, implicit conversions, stale statistics, non-SARGable predicates, and missing or redundant indexes.

Useful tools and techniques include the actual execution plan, SET STATISTICS IO, SET STATISTICS TIME, Query Store, Extended Events, DMVs, and controlled workload testing. The goal is to identify the actual bottleneck and fix its cause rather than optimizing based on assumptions.

Code Example

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT Id, Name, Price
FROM dbo.Products
WHERE CategoryId = 30
  AND IsActive = 1;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;