Introduction
Troubleshooting SQL Server in production requires more than knowing SQL commands. When a production issue occurs, a DBA needs to quickly understand what is happening, where the problem is occurring, what evidence should be collected, and what action should be taken.
A production issue could be anything from a slow query or blocking session to high CPU, memory pressure, transaction log growth, failed backups, database corruption, or application connection failures.
This SQL Server Production Troubleshooting Cheat Sheet provides a quick reference for some of the most common production problems and the first checks you should perform.
The most important principle is:
Don’t immediately fix the symptom. First identify the root cause.
SQL Server Production Troubleshooting Cheat Sheet
| Problem | First Check | Common Root Cause | Typical Action |
|---|---|---|---|
| High CPU | Top CPU queries | Inefficient queries/plans | Tune queries |
| Slow Query | Query Store + execution plan | Plan regression, blocking, I/O | Investigate query |
| Blocking | Blocking chain | Long transaction | Resolve root blocker |
| Deadlock | Deadlock graph | Conflicting transactions | Tune transaction/query |
| Memory Pressure | Memory DMVs | Grants, workload, configuration | Identify memory consumer |
| High I/O | File statistics + waits | Scans, storage latency | Tune query/storage |
| Tempdb Growth | Tempdb usage | Spills, version store, temp objects | Find consumer |
| Log Full | log_reuse_wait_desc | Active transaction/log backup | Resolve reuse issue |
| Disk Full | File/disk usage | Data/log growth | Free/expand capacity |
| Job Failure | SQL Agent history | Script, permission, file issue | Fix failed step |
| Backup Failure | Backup history | Space, permissions, destination | Correct failure |
| Login Failure | Error log | Authentication/login issue | Verify credentials/access |
| Connection Failure | Network + SQL Server | Firewall, DNS, service | Trace connectivity |
| Query Timeout | Blocking + waits | Resource or query issue | Find bottleneck |
| Plan Regression | Query Store | Plan change | Compare plans |
| Statistics Issue | Statistics + estimates | Poor estimates | Review statistics |
| Fragmentation | Index DMVs | Physical fragmentation | Maintenance if justified |
| Corruption | DBCC CHECKDB | Storage/database corruption | Recovery strategy |
| Service Down | Error log | Service/OS/storage issue | Diagnose startup/service |
| Deployment Regression | Query Store + deployment timeline | Code/schema/index change | Rollback or fix |
1. SQL Server CPU Is Very High
Symptoms
You may see:
- CPU utilization above normal levels
- Slow application response
- High CPU-related waits
- Queries taking longer than usual
First check
Identify the queries consuming the most 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,
st.text AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;Investigate
Look for:
- Inefficient execution plans
- Large scans
- Expensive joins
- Sort operations
- Excessive query executions
- Poor cardinality estimates
- Functions that prevent efficient access
Solution
Identify the highest-impact queries and analyze their execution plans before considering hardware changes.
High CPU is a symptom. Find the workload responsible for it.
2. A Query Suddenly Became Slow
A query that normally takes one second may suddenly start taking 30 seconds.
First checks
Use Query Store and compare:
- Previous execution plan
- Current execution plan
- CPU
- Duration
- Logical reads
- Execution count
Possible causes
- Execution plan change
- Statistics changes
- Data distribution changes
- Parameter sensitivity
- Blocking
- Increased workload
Solution
Compare the good and bad plans and determine what changed.
3. SQL Server Has Blocking
Blocking occurs when one session is waiting for a resource locked by another session.
Check current blocking:
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;Investigate
Find:
- Head blocker
- Blocking session
- Transaction duration
- SQL statement
- Locks being held
Solution
Don’t automatically kill the blocked session.
First determine why the blocking transaction is holding locks.
4. SQL Server Has Deadlocks
A deadlock occurs when transactions wait for resources held by each other.
Example:
Transaction A
|
| locks Table A
↓
waits for Table B
Transaction B
|
| locks Table B
↓
waits for Table AFirst check
Capture the deadlock graph using Extended Events or review available deadlock information.
Common solutions
- Keep transactions short
- Access objects in a consistent order
- Improve indexing
- Reduce unnecessary locking
- Review application transaction design
The deadlock victim is not necessarily the root cause.
5. SQL Server Is Experiencing Memory Pressure
Symptoms
- Slow queries
- Large memory grants
- Unpredictable query performance
- Increased paging or external memory pressure
Check system memory:
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;Investigate
Look for:
- Large sorts
- Hash operations
- Memory-intensive queries
- Poor cardinality estimates
- Excessive concurrent queries
- SQL Server memory configuration
Solution
Find the memory consumer before simply adding more memory.
6. SQL Server Disk I/O Is Very High
First check
Review database file I/O 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) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id;Possible causes
- Large scans
- Missing indexes
- Inefficient queries
- Slow storage
- Heavy data operations
- Tempdb I/O
Solution
Determine whether the bottleneck originates from the query, database design, or storage subsystem.
7. Tempdb Is Growing Rapidly
Tempdb can grow because of:
- Temporary tables
- Internal worktables
- Sort spills
- Hash spills
- Version store
- Long-running transactions
- Snapshot-based workloads
Check 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 the workload consuming tempdb.
Increasing tempdb size may provide capacity, but it does not necessarily address the underlying cause.
8. Transaction Log Is Full
One of the first checks should be:
SELECT
name,
log_reuse_wait_desc
FROM sys.databases
WHERE name = DB_NAME();Possible reasons include:
LOG_BACKUPACTIVE_TRANSACTIONREPLICATIONAVAILABILITY_REPLICA
Solution
Determine why log space cannot be reused.
If the database is using the FULL recovery model, verify that appropriate transaction log backups are occurring.
Don’t use repeated log shrinking as the solution to recurring log growth.
9. Database Is Running Out of Disk Space
Check database files:
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;Investigate
Determine whether the space is being consumed by:
- Data files
- Transaction logs
- Tempdb
- Backup files
- Other operating-system files
Solution
After identifying the consumer, take the appropriate capacity-management action.
10. Database Is in Recovery
This can happen after:
- SQL Server restart
- Server failure
- Database restart
- Large transaction rollback
Check:
SELECT
name,
state_desc,
recovery_model_desc
FROM sys.databases;Important
Don’t repeatedly restart SQL Server to try to speed up recovery.
Recovery may take time depending on database size, workload, I/O performance, and transaction activity.
11. SQL Server Agent Job Failed
Check:
SQL Server Agent → Jobs → View History
You can also query:
SELECT
j.name,
h.run_date,
h.run_time,
h.run_status,
h.message
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobhistory AS h
ON j.job_id = h.job_id
WHERE h.step_id = 0
ORDER BY h.instance_id DESC;Common causes
- Permission problems
- Missing files
- Network problems
- Credential issues
- Script errors
- Database availability problems
Solution
Read the failed step’s error message before making changes.
12. SQL Server Backup Failed
Common causes include:
- Insufficient disk space
- Destination unavailable
- Permission problems
- Network storage failure
- Database state problems
Check recent backup history:
SELECT TOP (20)
database_name,
backup_start_date,
backup_finish_date,
type,
backup_size,
physical_device_name
FROM msdb.dbo.backupset AS bs
LEFT JOIN msdb.dbo.backupmediafamily AS bmf
ON bs.media_set_id = bmf.media_set_id
ORDER BY backup_finish_date DESC;Solution
Determine whether the problem is related to:
SQL Server → Permissions → Network → Storage → Destination
13. Database Backup Is Taking Too Long
A backup taking longer than usual may indicate:
- Storage contention
- High I/O activity
- Large database growth
- Network bottlenecks
- Backup destination problems
- Concurrent workloads
Compare the current backup with historical backup duration and throughput.
A large backup does not automatically indicate a SQL Server performance problem.
14. Database Restore Is Taking Too Long
During disaster recovery, restore performance is critical.
Investigate:
- Backup size
- Restore destination
- Storage throughput
- Network throughput
- Database file configuration
- Recovery/redo activity
Regularly test restores instead of assuming that backups will restore within the required recovery window.
A backup that has never been tested through restore is not a complete recovery strategy.
15. Users Cannot Log In
First determine whether the problem is authentication or connectivity.
Check SQL logins:
SELECT
name,
is_disabled
FROM sys.sql_logins;Possible causes
- Incorrect credentials
- Disabled login
- Login does not exist
- Authentication configuration
- Permission problems
- Microsoft Entra authentication issues
Review the SQL Server error log for the specific login failure.
16. Application Cannot Connect to SQL Server
Use this troubleshooting sequence:
Application
↓
DNS
↓
Network
↓
Firewall
↓
SQL Server
↓
Port
↓
Authentication
↓
DatabasePossible causes
- SQL Server service stopped
- Firewall problem
- Incorrect server/instance
- DNS failure
- Network issue
- Authentication failure
- Connection pool issue
Avoid restarting SQL Server before understanding the failure.
17. Queries Are Timing Out
A timeout does not automatically mean that SQL Server itself is the problem.
Investigate:
- Blocking
- Deadlocks
- CPU
- I/O
- Memory
- Execution plan
- Waits
- Network
- Application timeout configuration
A useful sequence is:
Query Timeout
↓
Blocking?
↓
Waits?
↓
CPU / I/O / Memory?
↓
Execution Plan?
↓
Query Store?
↓
Root Cause18. Execution Plan Has Changed
Suppose a query normally runs in:
500 msand suddenly starts taking:
30 secondsQuery Store may reveal that the execution plan changed.
For example:
Previous:
Index Seek + Nested Loops
Current:
Index Scan + Hash MatchInvestigate
- Statistics changes
- Data distribution
- Index changes
- Parameter sensitivity
- Query changes
- Configuration changes
Solution
Compare the plans and validate the best solution using actual performance measurements.
19. Cardinality Estimates Are Incorrect
One of the most important execution-plan checks is:
Estimated Rows
VS
Actual RowsExample:
Estimated Rows: 10
Actual Rows: 500,000This large difference can influence:
- Join selection
- Memory grants
- Access methods
- Sorts
- Parallelism
Possible causes
- Statistics
- Data distribution
- Parameter sensitivity
- Complex predicates
- Cardinality estimation limitations
Investigate the specific query and statistics rather than assuming that updating all statistics will solve the problem.
20. Index Fragmentation Is High
Check fragmentation using:
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'
) AS ips
JOIN sys.indexes AS 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;Important
High fragmentation doesn’t automatically mean that an index needs to be rebuilt.
Consider:
- Index size
- Workload
- Fragmentation
- Query patterns
- Maintenance window
- Logging requirements
Index maintenance should be workload-driven, not percentage-driven alone.
21. Database Corruption Is Suspected
If corruption is suspected, use:
DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS;Possible symptoms
- Corruption errors
- Query failures
- I/O errors
- DBCC errors
If corruption is confirmed, evaluate recovery from a known-good backup.
Avoid immediately using repair options because some repair operations can involve data loss.
22. SQL Server Service Has Stopped
Check:
- SQL Server service
- SQL Server Error Log
- Windows Event Viewer
- Disk space
- Service account permissions
- Recent configuration changes
- Storage availability
Possible causes include:
- Disk problems
- Service account issues
- Permission changes
- Corrupt files
- Storage problems
- OS issues
The SQL Server Error Log is one of the most important sources of information when SQL Server fails to start.
23. SQL Server Has High Wait Statistics
Wait statistics can help identify where SQL Server is spending time.
A basic query is:
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;Possible categories include:
- CPU-related waits
- I/O waits
- Locking waits
- Memory-related waits
- Parallelism-related waits
- Transaction log waits
- Network-related waits
Important
Don’t automatically assume that the wait with the highest value is the problem.
Interpret waits in the context of the workload and the time period being investigated.
24. Application Reports Intermittent Slowness
Intermittent problems are often harder to troubleshoot than consistently slow queries.
For example:
10:00 AM → 2 seconds
10:05 AM → 30 seconds
10:10 AM → 2 secondsPossible causes include:
- Blocking
- Resource contention
- Plan changes
- Parameter sensitivity
- Storage latency
- Network problems
- Concurrent workloads
Best approach
Capture information while the problem is occurring.
Use:
Query Store
+
DMVs
+
Execution Plans
+
Wait Statistics
+
Blocking Information
+
CPU
+
I/OHistorical information from Query Store can be particularly valuable.
25. Production Deployment Caused Performance Problems
A deployment can introduce:
- New queries
- Stored procedure changes
- Index changes
- Schema changes
- Removed indexes
- Application code changes
Create a timeline:
Deployment
↓
Performance degradation
↓
Query Store
↓
Plan comparison
↓
Code / Index / Schema comparison
↓
Root Cause
↓
Fix or Rollback
↓
ValidationDon’t assume that the database is responsible simply because the application is reporting a database-related error.
SQL Server Production Troubleshooting Workflow
When a production incident occurs, use a consistent process.
Step 1: Identify the symptom
Examples:
- SQL Server CPU is high
- Query is slow
- Users are blocked
- Database is unavailable
- Transaction log is full
- Job failed
- Backup failed
Step 2: Determine the scope
Ask:
Is the problem affecting one query, one database, multiple databases, or the entire server?
This question can dramatically narrow your investigation.
Step 3: Collect evidence
Capture:
- Error message
- Query text
- Execution plan
- CPU
- Duration
- Logical reads
- Waits
- Blocking
- Memory
- I/O
- Recent changes
Step 4: Identify the root cause
Ask:
Why is this happening?
Don’t immediately ask:
What can I change?
Step 5: Apply the safest fix
Prefer changes that are:
- Tested
- Controlled
- Reversible
- Documented
- Appropriate for the production environment
Step 6: Validate
Compare performance before and after the change.
For example:
Before Fix
CPU: 85%
Duration: 30 sec
Logical Reads: 500,000
After Fix
CPU: 30%
Duration: 2 sec
Logical Reads: 15,000The actual metrics will depend on the workload, but the principle is always the same:
Measure the result.
SQL Server Production Troubleshooting Tools
A production DBA should be comfortable with these tools.
| Tool | What It Helps Diagnose |
|---|---|
| Query Store | Query performance history and plan changes |
| Execution Plans | Query execution behavior |
| DMVs | Current server and query activity |
| Extended Events | Specific production events |
| STATISTICS IO | Logical and physical I/O |
| STATISTICS TIME | CPU and elapsed time |
| SQL Server Agent | Job failures |
| SQL Server Error Log | Errors and server events |
| DBCC CHECKDB | Database integrity |
| Performance Monitor | Windows/server resources |
Essential SQL Server Troubleshooting Commands
Check active requests
SELECT
session_id,
status,
command,
wait_type,
wait_time,
blocking_session_id,
DB_NAME(database_id) AS database_name
FROM sys.dm_exec_requests;Check databases
SELECT
name,
state_desc,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases;Check CPU-intensive queries
SELECT TOP (10)
total_worker_time / 1000 AS total_cpu_ms,
execution_count,
total_elapsed_time / 1000 AS total_elapsed_ms
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC;Check waits
SELECT TOP (20)
wait_type,
waiting_tasks_count,
wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;Check I/O
SELECT *
FROM sys.dm_io_virtual_file_stats(NULL, NULL);SQL Server Production Troubleshooting Golden Rules
Rule 1: Don’t restart SQL Server immediately
A restart may temporarily remove the symptom but can also remove valuable diagnostic evidence.
Rule 2: Don’t kill sessions blindly
Understand the transaction and the impact before terminating a session.
Rule 3: Don’t rebuild every index
Fragmentation isn’t automatically the cause of a performance problem.
Rule 4: Don’t create every missing-index recommendation
Evaluate the complete workload and existing indexes.
Rule 5: Don’t shrink the transaction log as a routine fix
Find the reason preventing log reuse.
Rule 6: Don’t immediately increase hardware
First determine whether the workload or query is responsible for the resource pressure.
Rule 7: Don’t rely only on execution-plan cost percentages
Estimated cost is not the same as actual elapsed time.
Rule 8: Always capture evidence
Production troubleshooting should be evidence-driven.
Rule 9: Check recent changes
Many production incidents begin after:
- Application deployment
- Database deployment
- Configuration change
- Index change
- Statistics update
- Infrastructure change
Rule 10: Always validate the fix
A change isn’t successful simply because the error disappeared.
Measure the workload after the change.
SQL Server Production Troubleshooting Decision Tree
When someone says:
“SQL Server is slow.”
Start with these questions:
SQL Server is slow
↓
Is everything affected?
/ \
YES NO
↓ ↓
Check resources Identify query
↓ ↓
CPU / Memory / I/O Query Store
↓ ↓
Waits Execution Plan
↓ ↓
Blocking Estimates / Reads
↓ ↓
Root Cause ←───────┘
↓
Fix
↓
Measure
↓
ValidateSQL Server Production Troubleshooting Cheat Sheet
High CPU
Find top CPU-consuming queries.
Check: Top CPU queries → Query Store → Execution Plans → Waits
Blocking
Find the head blocker.
Check: Blocking chain → Head blocker → Transaction → Locks
Deadlock
Capture and analyze the deadlock graph.
Check: Deadlock graph → Competing transactions → Access order
Slow Query
Check Query Store and execution plan.
Check: Query Store → Plan → Estimates → Reads → Waits
Memory Pressure
Identify memory consumers and grants.
Check: Memory → Grants → Queries → OS memory
High I/O
Check query reads and file latency.
Check: Logical reads → File latency → Storage → Scans
Tempdb Growth
Identify objects, version store, and spills.
Check: User objects → Internal objects → Version store → Spills
Log Full
Use log_reuse_wait_desc.
Check: log_reuse_wait_desc → Transactions → Log backups
Disk Full
Identify which file or process is consuming space.
Check: Data files → Log files → Tempdb → Backup files
Agent Job Failure
Check SQL Server Agent history.
Check: Job history → Failed step → Error message
Backup Failure
Check destination, space, permissions, and errors.
Check: Destination → Space → Permissions → SQL error
Login Failure
Check authentication and SQL error logs.
Check: Login → Password → Authentication → Error log
Connection Failure
Check DNS, network, firewall, port, and authentication.
Check: DNS → Network → Firewall → SQL Server → Authentication
Database Corruption
Run DBCC CHECKDB and evaluate recovery options.
Check: DBCC CHECKDB → Backups → Recovery strategy
Deployment Regression
Compare the performance timeline before and after deployment.
Check: Timeline → Query Store → Plan → Code/index changes
Query Timeout
Check blocking, waits, CPU, I/O, and execution plan.
Plan Regression
Compare Query Store plans.
Poor Estimates
Compare estimated vs actual rows.
Fragmentation
Check whether it actually affects workload performance.
Service Down
Check SQL Server Error Log and Windows Event View
Summary
Production SQL Server troubleshooting is not about memorizing hundreds of commands.
It is about developing a structured troubleshooting mindset.
When an incident occurs, remember:
Identify → Collect Evidence → Analyze → Find Root Cause → Fix → Validate
The most effective SQL Server DBAs don’t immediately rebuild indexes, kill sessions, restart services, shrink databases, or increase hardware.
They first ask:
What changed?
What is affected?
What does the evidence tell me?
What is the root cause?
What is the safest fix?
Did the fix actually improve the situation?
That approach turns production troubleshooting from guesswork into a repeatable engineering process.
Quick DBA Mindset
Don’t fix the symptom. Find the reason behind the symptom.
Don’t guess. Measure.
Don’t change first. Investigate first.
Don’t stop when the error disappears. Validate the result.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
