Troubleshooting Slow Queries
By Alex Rivera • September 10, 2025
Table of Contents
Overview
Slow‑running queries can degrade the performance of any application. This guide walks you through a systematic approach to identify, analyze, and fix problematic queries in SQL Server, Azure SQL Database, and PostgreSQL.
Common Causes
- Missing or outdated indexes
- Statistical skew
- Blocking or deadlocks
- Suboptimal query plans
- Parameter sniffing
- Excessive row scans
Diagnostic Steps
- Capture the query text and execution statistics.
- Check the execution plan for warnings (e.g., missing index, scans).
- Inspect wait stats and identify blocking sessions.
- Review index usage statistics.
- Analyze parameter values that cause plan regressions.
Step 1 – Capture Query
SELECT TOP (1) query_hash, query_plan_hash, total_elapsed_time, execution_count
FROM sys.dm_exec_query_stats
ORDER BY total_elapsed_time DESC;Step 2 – Show Execution Plan
SET STATISTICS XML ON;
-- Run your query here
SET STATISTICS XML OFF;Indexing Tips
Use the missing index suggestions in the plan, but validate each before creation.
| Column(s) | Include(s) | Rationale |
|---|---|---|
| CustomerID, OrderDate | TotalAmount | Filters on CustomerID and date range |
Query Rewrites
Sometimes a small rewrite can produce a drastically better plan.
-- Original
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023;
-- Rewritten
SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';Monitoring & Alerts
Set up automatic alerts for queries that exceed a threshold.
CREATE EVENT SESSION SlowQueries
ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text, sqlserver.tsql_stack)
WHERE (duration > 5000)
);
ALTER EVENT SESSION SlowQueries ON SERVER STATE = START;