SQL Server Monitoring

2.1 CPU Utilization

High CPU usage can indicate inefficient queries, insufficient hardware, or other resource-intensive processes. Monitor the overall CPU usage and the CPU usage by SQL Server processes specifically.

Key counters to watch:

• Processor\% Processor Time (_Total)
• SQLServer:General Statistics\User Connections

2.2 Memory Usage

SQL Server relies heavily on memory for caching and query processing. Monitor buffer cache hit ratio, page life expectancy, and overall system memory usage.

• SQLServer:Buffer Manager\Buffer cache hit ratio (Aim for 99%+)
• SQLServer:Buffer Manager\Page life expectancy (Varies by workload, but a low number indicates memory pressure)
• Memory\Available MBytes

2.3 Disk I/O Performance

Slow disk I/O is a common performance bottleneck. Monitor disk latency, queue length, and throughput for both data and log files.

• PhysicalDisk\% Disk Time
• PhysicalDisk\Avg. Disk sec/Read and Avg. Disk sec/Write (Latency)
• PhysicalDisk\Avg. Disk Queue Length

Consider separating data and log files onto different physical drives or logical units for better performance.

2.4 Network Activity

High network traffic can point to large result sets being transferred, inefficient applications, or network saturation. Monitor network interface bytes sent/received and packets per second.

• Network Interface\Bytes Total/sec

2.5 Wait Statistics

Wait statistics are invaluable for diagnosing performance problems. They tell you what SQL Server is waiting for (e.g., I/O, locks, CPU, memory). Analyzing common wait types can pinpoint specific issues.

Key wait types to investigate:

• PAGEIOLATCH_*: Waiting for data pages to be read from disk.
• WRITELOG: Waiting for log records to be flushed to disk.
• CXPACKET / CXCONSUMER: Parallelism waits, which can sometimes indicate inefficient query plans.
• LCK_*: Lock waits, indicating blocking.
• RESOURCE_SEMAPHORE: Waiting for memory grants.

You can query wait statistics using DMVs:

SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM
    sys.dm_os_wait_stats
WHERE
    wait_type NOT IN (
        'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TASK_STOP',
        'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE',
        'CHKPT', 'CLR_CPU', 'CLR_SEMAPHORE', 'DBMIRROR_DBM_EVENT',
        'DBMIRROR_EVENTS_QUEUE', 'DBMIRROR_WORKER_QUEUE', 'DBMIRRORING_CMD',
        'DIRTY_PAGE_TABLE_LOCK', 'DISPATCHER_QUEUE_SEMAPHORE', 'EXECSYNC',
        'FSAGENT', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'FT_IFTSHC_MUTEX',
        'HADR_BROKER_TASK', 'HADR_DEFERRED_COMPLETION_QUEUE', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        'HADR_LOGCAPTURE_WAIT', 'HADR_NOTIFICATION_DEQUEUE', 'HADR_TIMER_TASK',
        'HADR_WORK_QUEUE', 'KILLED_QUERIES_IN_QUEUE', 'LAZYWRITER_SLEEP',
        'LOGMGR_QUEUE', 'MEMORY_ALLOCATION_EXT', 'ONDEMAND_TASK_QUEUE',
        'PARALLEL_REDO_DRAIN_WORKER', 'PARALLEL_REDO_LOG_CACHE', 'PARALLEL_REDO_TRAN_LIST',
        'PARALLEL_REDO_WORKER', 'PREEMPTIVE_OS_FLUSHFILEBUFFERS', 'PREEMPTIVE_XE_GETTARGETSTATE',
        'PWAIT_ALL_COMPONENTS_INITIALIZED', 'PWAIT_DIRECTLOGCONSUMER', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        'QDS_ASYNC_QUEUE', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'QDS_SHUTDOWN_QUEUE',
        'REDO_THREAD_PENDING_WORK', 'REQUEST_FOR_DEADLOCK_SEARCH', 'RESOURCE_QUEUE',
        'RPS_LIST', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'SQLTRACE_BUFFER_FLUSH',
        'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'SQLTRACE_WAIT_ENTRIES', 'WAIT_FOR_RESULTS',
        'WAIT_XTP_RECOVERY', 'WAIT_XTP_HOST_WAIT', 'WAIT_XTP_OFFLINE_IFACE',
        'XE_DISPATCHER_JOIN', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT'
    )
    ORDER BY
        wait_time_ms DESC;
                    

2.6 Query Performance

Slowly executing queries are a primary cause of poor application performance. Monitor query execution times, CPU per query, reads per query, and identify long-running or resource-intensive queries.

Use Dynamic Management Views (DMVs) like sys.dm_exec_query_stats and sys.dm_exec_sql_text to analyze query performance.

SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_time_ms,
    qs.total_elapsed_time / 1000.0 AS total_elapsed_time_ms,
    qs.total_worker_time / qs.execution_count / 1000.0 AS avg_cpu_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_physical_reads / qs.execution_count AS avg_physical_reads,
    qs.execution_count,
    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 statement_text,
    OBJECT_NAME(st.object_id) AS object_name
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE
    qs.total_elapsed_time > 0 -- Exclude queries that haven't run or completed very quickly
ORDER BY
    avg_elapsed_time_ms DESC;
                    

3.1 Dynamic Management Views (DMVs)

DMVs offer real-time information about the server's state, performance, and health. They are a powerful tool for querying detailed operational data.

Examples include sys.dm_os_wait_stats, sys.dm_exec_query_stats, sys.dm_db_index_physical_stats, and sys.dm_os_performance_counters.

3.2 Performance Monitor (PerfMon)

Windows Performance Monitor allows you to collect performance data from SQL Server and other system components using performance counters. You can set up data collector sets to log performance data over time for historical analysis.

Key SQL Server counters include:

• SQLServer:General Statistics
• SQLServer:Buffer Manager
• SQLServer:SQL Statistics
• SQLServer:Locks
• SQLServer:Databases

3.3 Extended Events (XE)

Extended Events is a flexible and scalable event-tracing system that provides more granular control and lower overhead than SQL Server Profiler. It's the recommended replacement for Profiler.

You can create event sessions to capture specific events (e.g., query completions, errors, deadlocks) and write them to various targets (e.g., event files, ring buffers).

-- Example: Creating a basic Extended Events session to capture errors
CREATE EVENT SESSION [ErrorCapture] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(package0.client_app_name,package0.client_hostname,package0.user_name)
    WHERE ([severity]=(20) OR [severity]=(21) OR [severity]=(22) OR [severity]=(23) OR [severity]=(24)))
ADD TARGET package0.event_file(SET filename=N'ErrorCapture.xel',max_file_size=(10),max_rollover_files=(5))
GO

ALTER EVENT SESSION [ErrorCapture] ON SERVER STATE = START;
GO

-- To stop the session:
-- ALTER EVENT SESSION [ErrorCapture] ON SERVER STATE = STOP;
-- DROP EVENT SESSION [ErrorCapture] ON SERVER;
                    

3.4 SQL Server Profiler (Legacy)

While still available, SQL Server Profiler is generally considered a legacy tool. It can be resource-intensive and is best used for troubleshooting specific issues in development or test environments, rather than continuous production monitoring.

3.5 Third-Party Tools

Many commercial and open-source tools offer advanced monitoring, alerting, and performance tuning capabilities for SQL Server, often with more user-friendly interfaces and richer feature sets.

Popular examples include:

• SolarWinds Database Performance Analyzer
• Quest Foglight for SQL Server
• Redgate SQL Monitor
• dbWatch