SQL Server Monitoring Performance

Overview

Effective performance monitoring is essential for maintaining SQL Server health, diagnosing bottlenecks, and ensuring optimal resource utilization. This guide introduces the core monitoring features, how to interpret their output, and how to integrate them into an ongoing performance strategy.

Built‑in Tools

Dynamic Management Views (DMVs)

DMVs provide real‑time insight into the internal state of the engine. Below are some of the most frequently used views for performance monitoring.

-- Current running queries
SELECT
    r.session_id,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE r.statement_end_offset END
          - r.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;
-- Index usage statistics
SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id
    AND i.index_id = s.index_id
WHERE s.database_id = DB_ID()
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) DESC;

Best Practices

  1. Collect baseline performance metrics during normal operation.
  2. Set up alerts for CPU, memory, and I/O thresholds using sp_configure or Azure Monitor.
  3. Regularly review wait statistics (sys.dm_os_wait_stats) and address top waits.
  4. Maintain up‑to‑date statistics and index fragmentation levels.
  5. Implement Query Store to capture plan regressions and runtime trends.

Sample Queries

Use these queries to quickly identify performance hotspots.

-- Top 10 CPU consuming queries
SELECT TOP 10
    qs.total_worker_time/1000 AS CPU_ms,
    qs.execution_count,
    qs.total_elapsed_time/1000 AS TotalTime_ms,
    SUBSTRING(st.text,qs.statement_start_offset/2,
        (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max),st.text)) * 2
            ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;
-- Identify blocking sessions
SELECT
    blocking_session_id,
    session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
ORDER BY wait_time DESC;

Further Resources