SQLDBA - SQL DBA Alert: Queries Suddenly Running Slow? Your Deep Dive Playbook

 As a SQL Server DBA, you’ll hear: “The app is slow!” or “Query took 2 seconds, now 2 minutes!” 😬

Before diving in, follow this structured troubleshooting approach—to not just fix, but understand and prevent it.

🔍 Step 1: Immediate Triage: Global or Specific?

✅ Check if slowness is server wide or a few queries
Find top resource consumers:

SELECT TOP 10 total_elapsed_time/1000 AS ElapsedMS,
execution_count,
total_logical_reads,
(SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS Query
FROM sys.dm_exec_query_stats
ORDER BY total_elapsed_time DESC;

📈 Step 2: Live Monitoring: What’s Running Now?
Use Activity Monitor, sp_whoisactive, or:

SELECT * FROM sys.dm_exec_requests WHERE status = 'running';
Watch for:
Blocking
Wait types (sys.dm_os_waiting_tasks)
TempDB usage
CPU pressure

⏳ Step 3: Wait Stats: The Bottleneck Clues
Get current waits:

SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
Patterns to note:
PAGEIOLATCH_* = Disk I/O
CXPACKET = Parallelism
LCK_* = Locking
SOS_SCHEDULER_YIELD = CPU

🧠 Step 4: Query Plans: Right Plan or Sniffed?
Use Query Store or:

SELECT qp.query_plan qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
Look for:
Parameter sniffing
Stats issues
Index scans
Row estimation errors

🛠️ Step 5: Indexes & Stats: Healthy or Not?
Check index fragmentation:

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED');
Rebuild/reorganize as needed
Update stats:
EXEC sp_updatestats;

⚙️ Step 6: Blocking & Deadlocks
Run:
EXEC sp_who2;
SELECT * FROM sys.dm_tran_locks;
Capture deadlocks via Extended Events
Kill blockers carefully

🧪 Step 7: TempDB & I/O Checks
Monitor TempDB:
SELECT * FROM sys.dm_db_file_space_usage;
Check disk latency:
sys.dm_io_virtual_file_stats
Confirm enough TempDB files

🔄 Step 8: Recent Changes
Ask:
Any recent deployments?
Stats updated?
Indexes dropped?

🛡️ Step 9: Quick Mitigations
Use RECOMPILE for sniffing
Add missing indexes (evaluate impact!)
Restart heavy agent jobs
Kill blockers (with caution)

🧰 Step 10: Prevent Future Issues
Enable Query Store
Use Extended Events
Set up alerts & monitoring
Automate index/stats jobs