Database administrators managing Microsoft SQL Server in production eventually run into the same three troublemakers: deadlocks that kill transactions at the worst possible moment, wait statistics that quietly explain why “the database is slow” complaints keep coming in, and fragmented indexes that turn fast queries into slow ones over a few months. Monitoring Microsoft SQL Server effectively means tracking all three together, because they’re rarely independent problems – a missing index often causes both excessive waits and deadlocks under load.
This article covers what to actually watch, how to interpret it, and where teams tend to get SQL Server monitoring wrong.
Why deadlocks happen and how to catch them before users complain
A deadlock occurs when two or more transactions hold locks on resources the other transactions need, and neither can proceed. SQL Server’s lock monitor detects the cycle and kills one transaction (the “deadlock victim”) to break it. The app gets error 1205, and unless someone’s watching, the first sign of trouble is a support ticket.
The common myth here is that deadlocks are rare edge cases that only happen under extreme load. In reality, they show up constantly in systems with poorly ordered transactions – for example, one stored procedure that updates Orders then Inventory, and another that updates Inventory then Orders. Under moderate concurrent traffic, that’s enough to produce deadlocks daily, not just during Black Friday spikes.
Practical steps for catching them early:
Enable the system_health extended event session (on by default in modern SQL Server versions) and query it periodically for deadlock graphs rather than waiting for someone to report an error. Set up an alert on Deadlocks/sec from the SQLServer:Locks performance counter so a spike triggers a notification instead of silence. When a deadlock graph shows the same two objects colliding repeatedly, that’s a strong signal to review transaction ordering or add appropriate indexes to shorten lock duration, not just retry logic in the app.
Retry logic hides the symptom. It doesn’t fix the underlying contention, and teams that rely on it exclusively tend to see deadlock rates creep upward unnoticed until a busier month makes retries themselves start timing out.
Reading wait statistics without getting lost in the noise
Wait stats tell you what SQL Server was waiting on when queries weren’t actively running on the CPU. sys.dm_os_wait_stats accumulates this data since the last service restart or manual reset, and it’s arguably the single most useful diagnostic view on the server.
A few wait types matter more than others in day-to-day troubleshooting:
PAGEIOLATCH waits point to slow storage – the engine is waiting on data pages to be read from or written to disk. CXPACKET and CXCONSUMER waits relate to parallelism, and while some is normal, consistently high values often mean MAXDOP or cost threshold for parallelism need tuning. LCK_M_* waits indicate blocking from other sessions, which ties directly back into the deadlock conversation above. RESOURCE_SEMAPHORE waits mean queries are waiting for memory grants, often a sign of missing indexes forcing large sorts or hashes.
The mistake many admins make is looking at raw cumulative wait totals and panicking over the biggest number, without accounting for baseline. A server that’s been up for 60 days will show enormous PAGEIOLATCH totals even if storage performance is fine, simply because time adds up. What matters is the wait profile relative to a known-normal baseline and how it shifts over time – a topic covered in more depth in this piece on performance baselines. Reset wait stats after major changes (index additions, hardware upgrades) so the next measurement period reflects the new state, not history.
Index health – the slow leak most teams catch too late
Indexes degrade in two ways: fragmentation, where the logical order of pages no longer matches physical order on disk, and staleness, where statistics no longer reflect the actual data distribution. Both cause the query optimizer to make worse decisions over time.
Fragmentation above roughly 30% on an index that’s actually used in query plans is usually worth a rebuild; between 5-30%, a reorganize is often sufficient and cheaper in terms of transaction log growth. sys.dm_db_index_physical_stats gives fragmentation percentages per index, and sys.dm_db_index_usage_stats shows which indexes are actually being used versus sitting idle, consuming write overhead for no read benefit. It’s common to find production databases carrying a dozen indexes that haven’t been touched by a seek or scan in months – dropping those recovers write throughput without any query regression risk.
Outdated statistics are sneakier than fragmentation because they don’t show up in a simple percentage. A table that grew from 10,000 rows to 10 million without a statistics update can produce cardinality estimates that are off by orders of magnitude, leading the optimizer to choose a nested loop join where a hash join would be far faster. Auto-update statistics helps, but on large tables it only triggers after a substantial percentage of rows change, which can lag badly behind actual growth. Scheduling explicit statistics updates on high-churn tables, rather than relying solely on the automatic threshold, closes that gap.
Putting continuous monitoring in place
Manually querying DMVs during an incident is useful for diagnosis, but it’s reactive by definition – the problem already happened. Continuous collection of wait stats, blocking sessions, deadlock events, and index fragmentation trends turns SQL Server health into something visible on a dashboard rather than something discovered through complaints.
A lightweight agent that pulls these metrics on a schedule and correlates them with CPU, memory, and disk I/O at the OS level gives a fuller picture than DMV queries alone, since a “slow query” complaint is sometimes really a storage or memory pressure issue at the host level. Pairing SQL Server-specific metrics with general server monitoring, along with connection pool behavior covered in this guide on connection pool monitoring, closes most of the visibility gaps that lead to surprise outages. For a broader view of what to track across database engines generally, this overview of database health metrics is a useful starting point.
FAQ
How often should index fragmentation be checked?
Weekly is reasonable for most production databases, though tables with heavy insert/delete/update activity may warrant daily checks. Tie the maintenance job to actual fragmentation thresholds rather than a blanket rebuild-everything schedule, since rebuilding unfragmented indexes wastes I/O and log space for no benefit.
Can deadlocks be eliminated entirely?
Not realistically in any system with meaningful concurrency. The goal is minimizing frequency and impact – consistent transaction ordering, shorter transactions, appropriate indexing to reduce lock duration, and using READ COMMITTED SNAPSHOT isolation where it fits the workload all reduce deadlock rates significantly, even if they don’t reach zero.
Do high wait stats always mean a problem?
No. Every running instance accumulates waits – it’s a natural byproduct of concurrency and I/O. What indicates a real problem is a wait type growing disproportionately relative to its own historical baseline, or a wait category that directly correlates with reported slowness during a specific time window.
Deadlocks, wait stats, and index health aren’t three separate monitoring tasks – they’re three views into the same underlying question of how well SQL Server is handling concurrent load against its current schema and hardware. Watching all three together, against a known baseline, turns “the database feels slow” into a specific, fixable finding instead of a guessing game.