Monitoring SQL Server Performance and Health

Effective monitoring is crucial for maintaining the health, performance, and availability of your SQL Server instances. This section covers key aspects of monitoring, including performance counters, Extended Events, SQL Server Agent alerts, and third-party tools.

Key Monitoring Areas

1. Performance Counters

SQL Server exposes a rich set of performance counters through Windows Management Instrumentation (WMI) and the Performance Monitor tool. These counters provide real-time insights into various aspects of server operations.

Essential Performance Counters:

You can use the Performance Monitor tool (perfmon.msc) to view and log these counters.


    -- Example: Querying DMV for connection count
    SELECT cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'User Connections'
    AND object_name = 'SQLServer:General Statistics';

2. Extended Events (XEvents)

Extended Events are a flexible and powerful tracing system for SQL Server, replacing SQL Trace and Profiler for most scenarios. They allow you to capture detailed information about specific events occurring on the server.

Commonly Monitored Events:

Tip: Start with a predefined template like 'System Health' or 'Sp_whoisactive' for common monitoring needs.

You can create and manage Extended Events sessions using SQL Server Management Studio (SSMS) or T-SQL scripts.


    -- Example: Creating a simple Extended Events session for errors
    CREATE EVENT SESSION [MonitorErrors] ON SERVER
    ADD EVENT sqlserver.error_reported(
        ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name)
        WHERE ([severity=(16)] OR [severity]=(17) OR [severity]=(18) OR [severity]=(19) OR [severity]=(20) OR [severity]=(21) OR [severity]=(22) OR [severity]=(23) OR [severity]=(24) OR [severity]=(25))) -- Capture errors from severity 16 up
    ADD TARGET package0.event_file(NAME=N'MonitorErrors', FILENAME=N'\\server\share\MonitorErrors.xel', MAX_FILE_SIZE=(50), MAX_ROLLOVER_FILES=(5))
    WITH (MAX_MEMORY=4096KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_INTERVAL=30 SECONDS, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON)
    GO

    ALTER EVENT SESSION [MonitorErrors] STATE=START;
    GO

3. SQL Server Agent Alerts and Operators

SQL Server Agent can be configured to respond to specific events or performance thresholds by triggering alerts. These alerts can notify administrators via email, pager, or execute a predefined job.

Common Alert Configurations:

Tip: Configure operators (email addresses/pager info) and alert destinations in SQL Server Agent to ensure timely notifications.

4. Dynamic Management Views (DMVs) and Functions (DMFs)

SQL Server provides a wealth of DMVs and DMFs that offer deep insights into the server's internal state. These are invaluable for real-time diagnostics and historical analysis.

Useful DMVs for Monitoring:

5. System Health Session

SQL Server automatically creates a 'System Health' Extended Events session that runs by default. This session captures critical system-level events like deadlocks, errors, and waits, providing a valuable baseline for troubleshooting.

6. Third-Party Monitoring Tools

For comprehensive enterprise-level monitoring, consider using specialized third-party tools. These tools often provide:

Best Practices for Monitoring

Warning: Overly aggressive or poorly configured monitoring can itself impact server performance. Be judicious in what you collect and how often.