Introduction
Troubleshooting SQL Server in a production environment is very different from troubleshooting a development database.
In production, a slow query can affect thousands of users. A blocking session can delay critical transactions. A failed SQL Server Agent job can affect reporting, and a sudden increase in CPU, memory, or storage utilization can quickly become a major production incident.
The most important skill for a SQL Server DBA is not just knowing individual commands. It is knowing what to check first, how to identify the root cause, and how to apply the safest solution.
In this article, we will look at 25 real-world SQL Server production troubleshooting scenarios, along with practical troubleshooting steps and solutions.
SQL Server Production Troubleshooting Quick Reference
| # | Production Problem | First Things to Check |
|---|---|---|
| 1 | SQL Server CPU is very high | DMVs, Query Store, execution plans |
| 2 | Query suddenly became slow | Query Store, plan changes, waits |
| 3 | Blocking sessions | sys.dm_exec_requests, blocking chain |
| 4 | Deadlocks | Extended Events, deadlock graph |
| 5 | SQL Server memory pressure | Memory DMVs, grants, OS memory |
| 6 | High disk I/O | STATISTICS IO, file statistics, waits |
| 7 | Tempdb is growing rapidly | Tempdb usage, version store, spills |
| 8 | Transaction log is full | log_reuse_wait_desc, transactions |
| 9 | Database is running out of disk | File usage and growth |
| 10 | Database is stuck in recovery | Recovery status, log, I/O |
| 11 | SQL Server Agent job failed | Job history and error message |
| 12 | Backup failed | Backup history, disk, permissions |
| 13 | Database backup is taking too long | I/O, compression, database size |
| 14 | Restore is taking too long | I/O and restore throughput |
| 15 | Login failures | Authentication and error logs |
| 16 | Connection failures | Network, firewall, SQL Server |
| 17 | Query timeout | Blocking, CPU, I/O, waits |
| 18 | Execution plan changed | Query Store |
| 19 | Poor statistics | Statistics and cardinality estimates |
| 20 | High index fragmentation | Fragmentation and workload |
| 21 | Database corruption | DBCC CHECKDB |
| 22 | SQL Server service stopped | Windows/Event logs, SQL error log |
| 23 | High wait statistics | Wait analysis |
| 24 | Intermittent application slowness | Query, blocking, waits, network |
| 25 | Deployment caused performance problems | Query Store, plans, code/index changes |
1. SQL Server CPU Utilization Is Very High
Production symptom
The application becomes slow and CPU utilization reaches 90–100%.
What should you check?
First identify which queries are consuming CPU.
SELECT TOP (10)
qs.total_worker_time / 1000 AS total_cpu_ms,
qs.execution_count,
qs.total_worker_time / NULLIF(qs.execution_count,0) / 1000 AS avg_cpu_ms,
DB_NAME(st.dbid) AS database_name,
SUBSTRING(
st.text,
(qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset) / 2) + 1
) AS query_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;Possible causes
- Poor execution plan
- Missing or inappropriate indexes
- Excessive scans
- Inefficient joins
- Large sorts
- Scalar functions
- Excessive query executions
- Parameter sensitivity
Solution
Identify the highest-impact queries and examine their execution plans.
Don’t immediately add indexes or increase CPU.
First identify what is consuming the CPU.
2. A Query That Was Fast Yesterday Is Suddenly Slow
This is one of the most common production incidents.
Possible causes
- Execution plan changed
- Statistics changed
- Data distribution changed
- Parameter sensitivity
- Index changes
- Increased concurrency
- Blocking
- Resource pressure
First tool to check
Query Store
Compare the previous execution plan with the current execution plan.
Also compare:
- CPU
- Duration
- Logical reads
- Execution count
Solution
If a plan regression is confirmed, investigate the reason for the plan change.
Depending on the situation, possible solutions include:
- Statistics maintenance
- Query tuning
- Index changes
- Query Store plan forcing
- Query Store hints
- Query rewrite
3. Production Database Has Blocking
Symptom
Users report that queries are hanging or taking unusually long.
Check active requests:
SELECT
session_id,
blocking_session_id,
status,
wait_type,
wait_time,
DB_NAME(database_id) AS database_name,
command
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;A typical blocking chain might look like:
Session 51
↓
holds lock
Session 72
↓
waiting
Session 85
↓
waitingCommon causes
- Long-running transactions
- Uncommitted transactions
- Large updates/deletes
- Poor indexing
- Application transaction problems
Solution
Don’t simply kill the blocked session.
First identify:
Who is blocking whom and why?
Then investigate the head blocker.
4. SQL Server Is Experiencing Deadlocks
A deadlock occurs when two or more sessions wait for resources held by each other.
For example:
Transaction A
locks Table A
↓
waits for Table B
Transaction B
locks Table B
↓
waits for Table ASQL Server detects the deadlock and chooses one transaction as the victim.
Troubleshooting
Capture the deadlock graph using Extended Events or review the system_health session where available.
Common solutions
- Access objects in a consistent order
- Keep transactions short
- Improve indexing
- Reduce unnecessary locking
- Avoid user interaction inside transactions
- Review application transaction design
Don’t treat the deadlock victim as the root cause. Find the competing transactions.
5. SQL Server Is Experiencing Memory Pressure
Symptoms
- Queries become slow
- Memory grants increase
- Paging may increase
- Query performance becomes unpredictable
Check SQL Server memory configuration and current usage.
SELECT
total_physical_memory_kb / 1024 AS total_physical_memory_mb,
available_physical_memory_kb / 1024 AS available_physical_memory_mb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;Also investigate queries requesting large memory grants.
Possible causes
- Large sorts
- Hash operations
- Poor cardinality estimates
- Excessive concurrent queries
- Inappropriate SQL Server max memory configuration
- External memory pressure
Solution
Identify the root cause before simply increasing memory.
6. Disk I/O Is Very High
Symptoms
- Queries are slow
- Storage latency increases
PAGEIOLATCH_*waits may increase- Backup operations may also slow down
Check database file statistics:
SELECT
DB_NAME(vfs.database_id) AS database_name,
mf.physical_name,
vfs.num_of_reads,
vfs.io_stall_read_ms,
vfs.num_of_writes,
vfs.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id;Possible causes
- Excessive table/index scans
- Missing indexes
- Poor queries
- Slow storage
- Large data operations
- Tempdb I/O
Solution
Start at the query level before concluding that storage needs to be upgraded.
7. Tempdb Is Growing Rapidly
Possible causes
- Large temporary tables
- Hash spills
- Sort spills
- Version store activity
- Long-running transactions
- Snapshot isolation
- Index operations
Check tempdb usage:
USE tempdb;
SELECT
SUM(user_object_reserved_page_count) AS user_object_pages,
SUM(internal_object_reserved_page_count) AS internal_object_pages,
SUM(version_store_reserved_page_count) AS version_store_pages
FROM sys.dm_db_file_space_usage;Solution
Identify what is consuming tempdb rather than simply making tempdb larger.
Look for:
- Large queries
- Spills
- Long transactions
- Version store usage
8. Transaction Log Is Full
Symptom
Applications receive errors indicating that the transaction log is full.
Check:
SELECT
name,
log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();Common log_reuse_wait_desc values include:
LOG_BACKUPACTIVE_TRANSACTIONREPLICATIONAVAILABILITY_REPLICA
If the database uses the FULL recovery model, regular transaction log backups are normally required.
Solution
First determine why the log cannot be reused.
Don’t simply shrink the log.
Shrinking the log is usually not a real solution to recurring log growth.
9. Database Is Running Out of Disk Space
Check database file sizes and growth configuration:
SELECT
DB_NAME(database_id) AS database_name,
name,
physical_name,
size * 8 / 1024 AS size_mb,
growth,
is_percent_growth
FROM sys.master_files
ORDER BY size DESC;Common causes
- Data growth
- Transaction log growth
- Tempdb growth
- Backups stored locally
- Old files/logs
- Incorrect autogrowth configuration
Solution
Identify the consumer first.
Then:
- Free unnecessary space
- Move files if appropriate
- Configure sensible autogrowth
- Expand storage
- Establish capacity monitoring
10. Database Is Stuck in Recovery
This often happens after:
- SQL Server restart
- Server crash
- Database restart
- Large transaction rollback
First step
Check database state:
SELECT
name,
state_desc,
recovery_model_desc
FROM sys.databases;For an actively recovering database, monitor recovery rather than immediately taking drastic action.
Important
Don’t repeatedly restart SQL Server hoping that recovery will complete faster.
Large transactions can require significant time to roll forward or roll back.
11. SQL Server Agent Job Failed
First check
Open:
SQL Server Agent → Jobs → View History
Or query job history:
SELECT
j.name,
h.run_date,
h.run_time,
h.run_status,
h.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
WHERE h.step_id = 0
ORDER BY h.instance_id DESC;Common causes
- Permission changes
- Missing files
- Network failures
- Credential problems
- Agent service issues
- Database availability problems
- Script errors
Solution
Read the job step error message before changing anything.
12. SQL Server Backup Failed
Backups can fail because of:
- Insufficient disk space
- Permission problems
- Network storage issues
- Backup destination unavailable
- Database state problems
Check backup history:
SELECT TOP (20)
database_name,
backup_start_date,
backup_finish_date,
type,
backup_size,
physical_device_name
FROM msdb.dbo.backupset bs
LEFT JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_finish_date DESC;Solution
Determine whether the failure is caused by:
SQL Server → Storage → Network → Permissions
before retrying repeatedly.
13. Database Backup Is Taking Too Long
Possible causes
- Large database
- Slow storage
- High I/O contention
- Backup compression settings
- Concurrent workloads
- Network destination
Check:
Database size
Backup size
Backup duration
Backup throughput
Storage latencyA large backup doesn’t necessarily mean SQL Server is unhealthy.
Look at backup throughput and historical duration.
14. Database Restore Is Taking Too Long
During disaster recovery, every minute matters.
Investigate:
- Backup size
- Storage performance
- Network throughput
- Restore destination
- Number and size of files
- Recovery/redo activity
Don’t assume the restore is stuck simply because it takes time.
For large databases, establish expected restore duration through regular testing.
A backup strategy isn’t complete until restore performance is tested.
15. Users Cannot Log In
First determine
Is the failure:
Authentication?
or
Connectivity?
Common authentication causes include:
- Incorrect password
- Login disabled
- Login doesn’t exist
- Permission problem
- Authentication mode issue
- Microsoft Entra configuration problems
Check SQL Server error logs for relevant login failures.
For SQL logins:
SELECT
name,
is_disabled
FROM sys.sql_logins;16. Applications Cannot Connect to SQL Server
Check in this order
Application
↓
DNS / Name Resolution
↓
Network
↓
Firewall
↓
SQL Server Listener
↓
Authentication
↓
DatabasePossible causes
- SQL Server service stopped
- Network connectivity
- Firewall
- Incorrect server/instance name
- Port configuration
- DNS problems
- Authentication failures
- Connection pool issues
Avoid immediately restarting SQL Server.
17. Application Queries Are Timing Out
A query timeout is a symptom, not necessarily a database problem.
Possible causes:
- Blocking
- Deadlocks
- CPU pressure
- I/O pressure
- Poor execution plan
- Memory pressure
- Network problems
- Application timeout configuration
Troubleshooting sequence
Timeout
↓
Check blocking
↓
Check waits
↓
Check CPU
↓
Check I/O
↓
Check execution plan
↓
Check Query Store
↓
Identify root cause18. Execution Plan Suddenly Changed
This is a particularly important production scenario.
Suppose:
Monday:
Query = 500 ms
Tuesday:
Query = 45 secondsQuery Store may show:
Old Plan → Index Seek + Nested Loops
New Plan → Scan + Hash MatchPossible causes
- Statistics update
- Data distribution change
- Index changes
- Parameter sensitivity
- Configuration changes
- Query changes
Solution
Compare plans and runtime statistics.
Don’t force a plan permanently unless you’ve established that it is appropriate for the workload.
19. Poor Statistics Are Causing Bad Performance
SQL Server’s optimizer relies heavily on statistics to estimate row counts.
Imagine:
Estimated Rows: 20
Actual Rows: 2,000,000That estimation error can lead to an inappropriate execution plan.
Check statistics
SELECT
s.name AS statistics_name,
s.auto_created,
s.user_created,
s.is_temporary
FROM sys.stats s
WHERE s.object_id = OBJECT_ID('dbo.Sales');Solution
Investigate statistics freshness and data distribution.
Updating statistics may help, but don’t assume that every slow query is caused by stale statistics.
20. Index Fragmentation Is High
A common DBA mistake is to see high fragmentation and immediately rebuild every index.
First ask:
Is the fragmentation actually affecting performance?
Check fragmentation:
SELECT
OBJECT_NAME(ips.object_id) AS table_name,
i.name AS index_name,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(
DB_ID(), NULL, NULL, NULL, 'LIMITED'
) ips
JOIN sys.indexes i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE ips.index_id > 0
ORDER BY ips.avg_fragmentation_in_percent DESC;Solution
Consider:
- Fragmentation
- Page count
- Query workload
- Maintenance window
- Logging impact
- Edition/features available
Don’t rebuild an index simply because its fragmentation percentage looks high.
21. Database Corruption Is Suspected
Possible symptoms:
- Corruption errors
- Query failures
- I/O errors
- DBCC errors
Run:
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS;Critical rule
If corruption is confirmed:
Restore from a known-good backup whenever possible.
Don’t immediately jump to repair options.
Repair operations can result in data loss.
22. SQL Server Service Has Stopped
First checks
- SQL Server service status
- Windows Event Viewer
- SQL Server Error Log
- Storage availability
- Recent configuration changes
- OS issues
- Resource exhaustion
If SQL Server won’t start, inspect the SQL Server error log for the startup failure.
Possible causes include:
- Disk full
- Corrupt files
- Permission changes
- Service account problems
- Port/configuration conflicts
- Storage problems
23. SQL Server Has High Wait Statistics
Wait statistics can help explain where SQL Server is spending time.
A simplified query:
SELECT TOP (20)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;Important
Don’t blindly troubleshoot the first wait in the list.
Some waits are expected or benign.
You need to understand:
- What workload was running?
- When did the waits occur?
- Are the waits abnormal?
- What queries are responsible?
Common wait categories include:
CPU-related
I/O-related
Locking
Memory
Parallelism
Transaction log
Network24. Application Reports Intermittent Slowness
This can be harder than a permanently slow query.
The application might report:
“Sometimes the page takes 2 seconds, sometimes 30 seconds.”
Possible causes:
- Blocking
- Plan changes
- Parameter sensitivity
- Resource contention
- Connection pool behavior
- Network latency
- Concurrent workload
- Storage latency
Best approach
Capture information while the problem is happening.
Check:
Query Store
+
DMVs
+
Blocking
+
Wait statistics
+
CPU
+
I/O
+
Execution plansHistorical information is particularly valuable for intermittent problems.
25. Production Deployment Caused Performance Problems
This is a common real-world scenario.
A deployment may introduce:
- New query
- New index
- Removed index
- Schema change
- Stored procedure change
- Statistics change
- Application code change
Example
Before deployment:
Query duration: 800 msAfter deployment:
Query duration: 25 secondsTroubleshooting
Build a timeline:
Deployment
↓
Performance degradation
↓
Query Store
↓
Plan comparison
↓
Code/index/schema comparison
↓
Root cause
↓
Rollback or fix
↓
ValidateDon’t assume the database is responsible just because the symptoms appear in SQL Server.
Production Troubleshooting Golden Rules
1. Don’t restart SQL Server as your first response
A restart can make symptoms disappear temporarily while destroying useful diagnostic evidence.
2. Don’t kill sessions without understanding them
A session may be performing a critical transaction.
Find the head blocker and understand what it is doing.
3. Don’t rebuild every index
Fragmentation isn’t automatically the cause of poor performance.
4. Don’t blindly update statistics
Understand which statistics and which query are causing the problem.
5. Don’t create every missing-index recommendation
Missing-index suggestions don’t understand your entire workload.
6. Don’t shrink the transaction log to solve recurring growth
Find out why log reuse is being prevented.
7. Don’t immediately increase hardware
Scaling CPU, memory, or storage can hide a query or design problem.
8. Don’t rely only on execution-plan cost percentages
Estimated cost isn’t actual elapsed time.
9. Always capture evidence
Before making a change, collect:
Query
Execution Plan
CPU
Duration
Logical Reads
Waits
Blocking
Relevant error messages10. Measure after the fix
The troubleshooting cycle isn’t:
Problem → Change
It is:
Problem → Evidence → Root Cause → Change → Measurement → Validation
A Practical Production Troubleshooting Framework
For every SQL Server incident, use the following five-step framework.
Step 1: Identify the symptom
Examples:
CPU = 100%
Query timeout
Blocking
Database unavailable
Job failed
Disk fullStep 2: Collect evidence
Don’t make changes yet.
Collect:
- DMVs
- Query Store data
- Execution plans
- Error messages
- Wait statistics
- Resource metrics
Step 3: Identify the root cause
Ask:
Why is this happening?
Not:
What can I change quickly?
Step 4: Apply the smallest appropriate fix
Production changes should be:
- Controlled
- Reversible where possible
- Tested
- Documented
Step 5: Validate
Compare:
Before
↓
Change
↓
AfterMeasure:
- Duration
- CPU
- Logical reads
- Waits
- Blocking
- Application response time
SQL Server Production Troubleshooting Cheat Sheet
Final Thoughts
SQL Server production troubleshooting is a combination of technical knowledge, diagnostic discipline, and good judgment.
The biggest mistake is to jump directly to a solution:
“CPU is high → add CPU.”
“Query is slow → rebuild indexes.”
“Log is full → shrink the log.”
“Blocking exists → kill the session.”
These actions may sometimes help, but they don’t necessarily solve the underlying problem.
A better approach is:
Observe → Measure → Diagnose → Fix → Validate
If you develop the habit of using Query Store, execution plans, DMVs, wait statistics, Extended Events, STATISTICS IO/TIME, and SQL Server logs together, you can troubleshoot a much wider range of production incidents systematically.
SQL Server Production Troubleshooting Quick Revision
PRODUCTION ISSUE
↓
Identify
↓
Collect
Evidence
↓
┌─────────────┼─────────────┐
↓ ↓ ↓
Query Blocking Resource
↓ ↓ ↓
Query Store Locks CPU / I/O /
Plan Analysis Deadlocks Memory
│ │ │
└─────────────┼─────────────┘
↓
Root Cause
↓
Fix
↓
Measure
↓
ValidateThe DBA mindset
Don’t fix the symptom. Find the reason behind the symptom.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
