SQL Server Performance Tuning: Common Scenarios
1. Scenario: “SQL Server Suddenly Became Slow”
What Happened?
Users report:
Application is slow
Queries that used to run in seconds now take minutes
No code change reported
Why This Happens (Most Likely Causes)
Execution plan changed
Statistics became outdated
Parameter sniffing issue
Memory pressure or cache eviction
Recent index change or data growth
Step-by-Step Troubleshooting
Step 1: Check Wait Statistics
SELECT TOP 10
wait_type,
wait_time_ms / 1000.0 AS wait_time_sec
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
👉 Look for:
PAGEIOLATCH → disk issue
CXPACKET → parallelism
LCK_M_X → blocking
Step 2: Find Recently Slow Queries
SELECT TOP 10
qs.last_execution_time,
qs.total_elapsed_time / qs.execution_count AS avg_time,
st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.last_execution_time DESC;
Step 3: Compare Execution Plans (Query Store)
SELECT *
FROM sys.query_store_plan;
👉 Look for:
Plan changes
Increased cost
How to Fix
Fix 1: Update Statistics
EXEC sp_updatestats;
Fix 2: Force Good Plan (temporary)
EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 2;
Fix 3: Clear Cache (careful in production)
DBCC FREEPROCCACHE;
Lesson Learned
👉 Sudden slowness is usually due to plan regression or stale statistics, not hardware.
2. Scenario: High CPU Usage (100%)
What Happened?
CPU constantly at 90–100%
Queries slow
Server unresponsive
Why?
Missing indexes
Expensive queries
Parallelism overload
Bad execution plan
Troubleshooting Steps
Step 1: Identify Top CPU Queries
SELECT TOP 5
qs.total_worker_time,
qs.execution_count,
st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
Step 2: Check Execution Plan
Look for:
Table scans
Hash joins
Missing indexes
Fix
Add Index
CREATE INDEX idx_orders_customerid
ON orders(customer_id);
Limit Parallelism
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;
Lesson
👉 High CPU is usually caused by bad queries + missing indexes, not lack of CPU.
3. Scenario: Blocking and Long Waits
What Happened?
Queries stuck
Users complain “system frozen”
CPU low but system slow
Why?
Blocking
Long transactions
Lock escalation
Diagram
User A → locks row
User B → waits
User C → waits
→ System appears frozen
Troubleshooting
Step 1: Identify Blocking
EXEC sp_who2;
Look for:
“BLOCKED” column
Step 2: Find Blocking Query
SELECT
blocking_session_id,
session_id,
wait_type,
wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
Fix
Option 1: Kill Blocking Session
KILL 52;
Option 2: Optimize Query
Add index
Reduce transaction time
Option 3: Use Read Committed Snapshot
ALTER DATABASE db SET READ_COMMITTED_SNAPSHOT ON;
Lesson
👉 Blocking is often mistaken for “slow SQL Server”, but it’s a concurrency design issue.
4. Scenario: Deadlock Errors
What Happened?
Users see:
“Transaction was deadlocked”
Why?
Two processes:
Lock resources in different order
Diagram
Process A → locks Table1 → waits Table2
Process B → locks Table2 → waits Table1
→ Deadlock
Troubleshooting
Capture Deadlock Graph
CREATE EVENT SESSION DeadlockCapture
ON SERVER
ADD EVENT sqlserver.deadlock_graph;
Fix
Solution 1: Access objects in same order
Solution 2: Keep transactions short
Solution 3: Add indexes
Lesson
👉 Deadlocks are design problems, not hardware issues.
5. Scenario: TempDB Contention
What Happened?
Slow queries using temp tables
High waits on TempDB
Why?
Too many temp tables
Single TempDB file
Allocation contention
Troubleshooting
Check waits
SELECT *
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%';
Fix
Add multiple TempDB files
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'C:\tempdb2.ndf');
Best Practice
1 file per CPU core (up to 8)
Lesson
👉 TempDB is a hidden bottleneck in many systems.
6. Scenario: Slow Query After Deployment
What Happened?
New release deployed
Queries became slow
Why?
New query logic
Missing indexes
Parameter sniffing
Troubleshooting
Compare Old vs New Plan
Use Query Store:
SELECT *
FROM sys.query_store_query;
Fix
Add missing index
CREATE INDEX idx_new
ON sales(order_date);
Rewrite query
Lesson
👉 Always test performance before deployment.
7. Scenario: Parameter Sniffing Problem
What Happened?
Same query sometimes fast, sometimes slow
Why?
SQL Server caches execution plan based on first parameter.
Example
Query with small data → fast plan cached
Query with large data → slow using same plan
Fix
Option 1: Recompile
OPTION (RECOMPILE);
Option 2: Optimize for unknown
OPTION (OPTIMIZE FOR UNKNOWN);
Lesson
👉 Parameter sniffing is one of the most misunderstood performance issues.
8. Scenario: Index Fragmentation
What Happened?
Queries slow over time
Why?
Inserts/updates fragment indexes
Check Fragmentation
SELECT
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');
Fix
ALTER INDEX ALL ON table_name REBUILD;
Lesson
👉 Regular index maintenance is critical.
9. Scenario: Disk I/O Bottleneck
What Happened?
High query latency
PAGEIOLATCH waits
Why?
Slow disk
Heavy reads
Fix
Move to SSD
Separate data/log files
Lesson
👉 Disk speed directly impacts performance.
10. Scenario: Memory Pressure
What Happened?
Queries slow
High disk reads
Why?
Not enough RAM
Check
SELECT *
FROM sys.dm_os_sys_memory;
Fix
Increase memory
Optimize queries
Lesson
👉 Memory is the most important resource for SQL Server.
11. Scenario: Too Many Indexes
What Happened?
Slow INSERT/UPDATE
Why?
Every index must be updated
Check
SELECT *
FROM sys.dm_db_index_usage_stats;
Fix
Remove unused indexes
Lesson
👉 More indexes ≠ better performance
12. Scenario: Large Table Without Partitioning
What Happened?
Queries slow on large tables
Why?
Full scans on huge data
Fix
Partition table
Lesson
👉 Partitioning improves large dataset performance
13. Scenario: Query Using SELECT *
What Happened?
Slow queries
High memory usage
Why?
Retrieves unnecessary columns
Fix
SELECT id, name FROM users;
Lesson
👉 Always select only needed columns
14. Scenario: Implicit Conversion
What Happened?
Index not used
Why?
Different data types
Fix
Match data types
Lesson
👉 Data type mismatch kills performance
15. Scenario: Unused Statistics
What Happened?
Bad execution plan
Why?
Outdated statistics
Fix
UPDATE STATISTICS table_name;
Final Master Troubleshooting Flow (Production)
Step-by-Step Checklist
Step 1: Check waits
sys.dm_os_wait_stats
Step 2: Check CPU queries
sys.dm_exec_query_stats
Step 3: Check blocking
sp_who2
Step 4: Check indexes
sys.dm_db_index_usage_stats
Step 5: Check execution plans
Conclusion
Real-world SQL Server performance tuning is about:
Identifying symptoms
Finding root causes
Applying the correct fix
Golden Rules
Always check waits first
Fix queries before hardware
Index wisely
Monitor continuously
Test before deployment
No comments:
Post a Comment