Monitoring and Troubleshooting

For emergencies when the server is unresponsive.

46.1 Dynamic Management Views (DMVs): Essentials

sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_exec_connections

sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_query_plan, sys.dm_exec_procedure_stats

sys.dm_os_wait_stats, sys.dm_os_waiting_tasks, sys.dm_exec_session_wait_stats

sys.dm_tran_locks, sys.dm_tran_active_transactions, sys.dm_tran_session_transactions

sys.dm_db_index_usage_stats, sys.dm_db_index_physical_stats, sys.dm_db_missing_index_*

sys.dm_io_virtual_file_stats, sys.dm_io_pending_io_requests

sys.dm_os_memory_clerks, sys.dm_os_buffer_descriptors, sys.dm_os_sys_memory, sys.dm_exec_query_memory_grants

sys.dm_os_schedulers, sys.dm_os_ring_buffers, sys.dm_os_performance_counters, sys.dm_os_sys_info

46.2 Wait Statistics Analysis

Reading data pages from disk (memory pressure or slow I/O, missing indexes) Slow log disk, too many small commits CXPACKET / CXCONSUMER

Parallelism (tune MAXDOP and cost threshold) Waiting for memory grants

In-memory page contention (tempdb, last-page insert) Client not consuming results fast enough Worker thread exhaustion (critical) Synchronous AG replica latency

SELECT TOP (15) wait_type, wait_time_ms / 1000.0 AS wait_s, waiting_tasks_count,
       signal_wait_time_ms / 1000.0 AS signal_s,
       100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','BROKER_TASK_STOP','LAZYWRITER_SLEEP','SQLTRACE_BUFFER_FLUSH',
                        'XE_TIMER_EVENT','REQUEST_FOR_DEADLOCK_SEARCH','CHECKPOINT_QUEUE','WAITFOR',
                        'BROKER_TO_FLUSH','HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL',
                        'SP_SERVER_DIAGNOSTICS_SLEEP','XE_DISPATCHER_WAIT','LOGMGR_QUEUE')
ORDER BY wait_time_ms DESC;
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

46.3 Extended Events (XEvents)

Built-in sessions: system_health, AlwaysOn_health, telemetry_xevents

Targets: event_file, ring_buffer, histogram, event_counter, pair_matching

CREATE EVENT SESSION SlowQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
    (ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.username)
     WHERE duration > 1000000)          -- > 1 second (microseconds)
ADD TARGET package0.event_file (SET filename = N'E:\XE\SlowQueries.xel', max_file_size = 100)
WITH (STARTUP_STATE = ON);
ALTER EVENT SESSION SlowQueries ON SERVER STATE = START;

SELECT CAST(event_data AS XML) FROM sys.fn_xe_file_target_read_file('E:\XE\SlowQueries*.xel', NULL, NULL, NULL);

46.4 Other Monitoring Tools

Activity Monitor, Performance Monitor (PerfMon) counters:

Batch Requests/sec, SQL Compilations/sec, Page Life Expectancy, Buffer cache hit ratio, Processor Queue Length, Avg Disk sec/Read

SQL Server error log: EXEC xp_readerrorlog 0, 1, N'error';, sp_cycle_errorlog

Default trace (legacy), SQL Server Profiler (deprecated) Performance Dashboard reports, Query Store reports

Community tools: sp_WhoIsActive, First Responder Kit (sp_Blitz, sp_BlitzFirst, sp_BlitzCache, sp_BlitzIndex, sp_BlitzLock)

Commercial tools: SolarWinds SQL Sentry, Redgate Monitor, Idera, Quest Spotlight

Azure: Database Watcher, Azure Monitor, SQL Insights

46.5 Troubleshooting Scenarios

Server is slow Wait stats → top queries (CPU, reads) → blocking → resource counters

sys.dm_exec_query_stats by worker time, recompiles, parallelism, missing indexes

PLE, RESOURCE_SEMAPHORE, memory clerks, max server memory I/O bottleneck

sys.dm_io_virtual_file_stats latency, PAGEIOLATCH, missing indexes Log full (error 9002)

log_reuse_wait_desc, open transactions, log backups Session space usage, version store, spills

Error log, SQL Browser, firewall, protocol, SPN/Kerberos Error log, DBCC CHECKDB, restore from backup

46.6 Dedicated Administrator Connection (DAC)

For emergencies when the server is unresponsive. Enable remote DAC with sp_configure 'remote admin connections', 1.

sqlcmd -S ADMIN:MyServer -E