Common symptoms

  • Queries that previously took seconds now take minutes or generate timeouts
  • SSMS or the activity monitor shows sessions waiting with high wait_type
  • SQL Server Agent jobs that don't finish within the expected window
  • The server shows high and sustained CPU or I/O usage
  • Frequent blocking reports from the application
  • sys.dm_exec_requests shows many requests with status = 'suspended'

Business risks

  • Timeouts in critical business transactions: sales, payments, inventory
  • Reports and management KPIs unavailable when needed
  • Escalation to incident if locks cause cascading deadlocks
  • Integration SLA with partners or clients missed due to API delays

Detailed technical checklist

  • 1. Top 10 queries by total CPU (since last compilation)
  • 2. Missing indexes suggested by the engine The engine accumulates index suggestions by observing execution plans. High impact = greater potential benefit.
  • 3. Unused indexes (candidates for removal) Unused indexes slow writes and consume space. Verify they have been unused since more than one restart.
  • 4. Verify server memory configuration If physical_memory_in_use_kb is close to max server memory, SQL Server may be under memory pressure.
  • 5. tempdb status and contention tempdb with a single file on multi-core servers generates severe contention on system pages.
  • 6. Index fragmentation on large tables Indexes with fragmentation > 30% usually require REBUILD; between 5–30% REORGANIZE is sufficient.
  • 7. Verify stale statistics Old statistics cause the optimizer to underestimate or overestimate rows and choose inefficient plans.
  • 8. I/O per database file If a data file has high latency, the bottleneck may be in the storage, not the engine.
T-SQL 
-- Top active waits on the server
SELECT TOP 10 wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC;

When to escalate to a specialist DBA

  • Very high CXPACKET wait that doesn't improve by adjusting MAXDOP — may indicate a deeper parallelism problem
  • Dominant ASYNC_NETWORK_IO — the application consumes data very slowly and may be an architecture problem
  • Frequent deadlocks (more than 1 per hour) affecting business transactions
  • Cascading locks that don't resolve on their own and require repeated manual intervention
  • The server reaches 100% CPU or runs out of memory available for SQL Server

Frequently asked questions

What does PAGEIOLATCH_SH mean in wait stats?

It is one of the most common waits. It indicates that a query is waiting to load a data page from disk into the buffer pool (SQL Server's memory cache). If it is the dominant wait, SQL Server doesn't have enough memory to keep the most accessed data in cache, or there is a latency problem in the storage subsystem. Review max server memory, check if the server competes with other applications for RAM, and analyze I/O with sys.dm_io_virtual_file_stats.

How many tempdb files should I have?

Microsoft's standard guidance is one tempdb file per logical core up to a maximum of 8. If your server has 16 cores and tempdb has 1 file, you may be generating page allocation contention (SGAM/GAM), which manifests as PAGELATCH_EX waits on tempdb. Files must be the same initial size and be on a fast dedicated disk (preferably SSD or NVMe).

How do I know if the problem is in the query or in server configuration?

If a specific query dominates sys.dm_exec_query_stats and its execution plan has Table Scan on large tables or Hash Join where Nested Loop was expected, the problem is in the query or missing indexes. If wait stats show a dominant server-level wait type (PAGEIOLATCH_SH, excessive CXPACKET, LCK_M_X), the problem is configuration, resources or structural contention.

Should I configure MAXDOP to 1 to eliminate CXPACKET?

Not necessarily. CXPACKET indicates parallelism, which is not always bad. The problem is when unbalanced parallelism (very high CXPACKET with little real work) slows individual queries. Microsoft's recommendation is to set MAXDOP to half the cores per socket, up to 8, and adjust the Cost Threshold for Parallelism to a higher value (25–50) so that only expensive queries use parallelism.

Related resources