SQL Server Monitoring with Dynamic Management Views

📊 SQL Server Monitoring with Dynamic Management Views: The Complete DBA Guide

Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) are the most powerful diagnostic tools available to SQL Server DBAs. They provide real-time insights into server health, query performance, and resource utilization without the overhead of third-party monitoring tools. This comprehensive guide covers the essential DMVs every DBA should master.

🔧 Understanding DMV Categories

SQL Server organizes DMVs into logical categories based on their purpose. Understanding this organization helps you quickly locate the right tool for any diagnostic task.

DMV Category Overview

🚀 Essential Performance Monitoring DMVs

Identifying CPU-Intensive Queries

-- Top 25 queries by CPU consumption
SELECT TOP 25
    qs.total_worker_time / 1000 AS TotalCPU_ms,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS AvgCPU_ms,
    qs.total_elapsed_time / qs.execution_count / 1000 AS AvgDuration_ms,
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    qs.total_physical_reads / qs.execution_count AS AvgPhysicalReads,
    qs.creation_time,
    qs.last_execution_time,
    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 QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

Memory-Intensive Query Analysis

-- Queries with highest memory grants
SELECT TOP 25
    qs.total_grant_kb / qs.execution_count AS AvgGrantKB,
    qs.max_grant_kb,
    qs.total_used_grant_kb / qs.execution_count AS AvgUsedGrantKB,
    qs.max_used_grant_kb,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count / 1000 AS AvgDuration_ms,
    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 QueryText,
    DB_NAME(st.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_grant_kb > 0
ORDER BY qs.total_grant_kb DESC;

💡 Wait Statistics Analysis

Wait statistics reveal what SQL Server spends its time waiting for. This is often the fastest path to identifying performance bottlenecks.

Current Wait Statistics

-- Comprehensive wait statistics analysis
WITH WaitStats AS (
    SELECT
        wait_type,
        waiting_tasks_count,
        wait_time_ms,
        max_wait_time_ms,
        signal_wait_time_ms,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS percent_of_total
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        -- Filter out benign waits
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
        'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
        'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
        'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
        'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT',
        'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT',
        'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER',
        'SLEEP_BPOOL_FLUSH', 'DIRTY_PAGE_POLL',
        'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'SP_SERVER_DIAGNOSTICS_SLEEP',
        'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
    )
    AND wait_time_ms > 0
)
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    CAST(wait_time_ms / 1000.0 / 60.0 AS DECIMAL(18,2)) AS wait_time_minutes,
    resource_wait_time_ms,
    signal_wait_time_ms,
    max_wait_time_ms,
    CAST(percent_of_total AS DECIMAL(5,2)) AS percent_of_total,
    CASE
        WHEN wait_type LIKE 'LCK%' THEN 'Locking'
        WHEN wait_type LIKE 'PAGEIOLATCH%' THEN 'I/O - Data File'
        WHEN wait_type LIKE 'PAGELATCH%' THEN 'Buffer Pool Contention'
        WHEN wait_type LIKE 'LATCH%' THEN 'Non-Buffer Latch'
        WHEN wait_type LIKE 'WRITELOG%' THEN 'Transaction Log'
        WHEN wait_type LIKE 'ASYNC_NETWORK_IO%' THEN 'Network/Client'
        WHEN wait_type LIKE 'CXPACKET%' THEN 'Parallelism'
        WHEN wait_type LIKE 'CXCONSUMER%' THEN 'Parallelism (Consumer)'
        WHEN wait_type LIKE 'SOS_SCHEDULER%' THEN 'CPU Pressure'
        WHEN wait_type LIKE 'THREADPOOL%' THEN 'Worker Thread Exhaustion'
        WHEN wait_type LIKE 'MEMORY%' THEN 'Memory'
        ELSE 'Other'
    END AS wait_category
FROM WaitStats
ORDER BY wait_time_ms DESC;

Real-Time Session Wait Analysis

-- What are active sessions waiting on right now?
SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    r.cpu_time,
    r.total_elapsed_time / 1000 AS elapsed_seconds,
    r.logical_reads,
    r.writes,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,
    SUBSTRING(
        st.text,
        (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE r.statement_end_offset
        END - r.statement_start_offset) / 2) + 1
    ) AS current_statement
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id != @@SPID
  AND r.status != 'background'
ORDER BY r.wait_time DESC;

💡 Pro Tip: High signal wait time relative to total wait time indicates CPU pressure. High resource wait time indicates I/O, locking, or memory issues.

📈 Index Usage Analysis

DMVs provide powerful insights into how your indexes are being used (or not used).

Index Usage Statistics

-- Comprehensive index usage analysis
SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ISNULL(s.user_seeks, 0) AS UserSeeks,
    ISNULL(s.user_scans, 0) AS UserScans,
    ISNULL(s.user_lookups, 0) AS UserLookups,
    ISNULL(s.user_seeks + s.user_scans + s.user_lookups, 0) AS TotalReads,
    ISNULL(s.user_updates, 0) AS UserUpdates,
    CASE
        WHEN ISNULL(s.user_updates, 0) = 0 THEN 0
        ELSE CAST(ISNULL(s.user_seeks + s.user_scans + s.user_lookups, 0) AS FLOAT)
             / ISNULL(s.user_updates, 1)
    END AS ReadToWriteRatio,
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup,
    s.last_user_update,
    ps.row_count,
    CAST(ps.reserved_page_count * 8.0 / 1024 AS DECIMAL(18,2)) AS IndexSizeMB
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON i.object_id = s.object_id
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
INNER JOIN sys.dm_db_partition_stats ps
    ON i.object_id = ps.object_id
    AND i.index_id = ps.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.type > 0  -- Exclude heaps
ORDER BY TotalReads DESC;

Unused Indexes (Candidates for Removal)

-- Identify potentially unused indexes
SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ISNULL(s.user_updates, 0) AS UserUpdates,
    ISNULL(s.system_updates, 0) AS SystemUpdates,
    s.last_user_update,
    CAST(ps.reserved_page_count * 8.0 / 1024 AS DECIMAL(18,2)) AS IndexSizeMB,
    'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' +
        QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' +
        QUOTENAME(OBJECT_NAME(i.object_id)) + ';' AS DropStatement
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON i.object_id = s.object_id
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
INNER JOIN sys.dm_db_partition_stats ps
    ON i.object_id = ps.object_id
    AND i.index_id = ps.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.type > 0
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND ISNULL(s.user_seeks, 0) = 0
  AND ISNULL(s.user_scans, 0) = 0
  AND ISNULL(s.user_lookups, 0) = 0
ORDER BY ps.reserved_page_count DESC;

Missing Index Recommendations

-- Missing index analysis with impact scores
SELECT TOP 25
    CAST(migs.avg_total_user_cost * migs.avg_user_impact *
         (migs.user_seeks + migs.user_scans) AS INT) AS ImpactScore,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    DB_NAME(mid.database_id) AS DatabaseName,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS SchemaName,
    OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    'CREATE NONCLUSTERED INDEX [IX_' +
        OBJECT_NAME(mid.object_id, mid.database_id) + '_' +
        REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') +
        '] ON ' +
        QUOTENAME(DB_NAME(mid.database_id)) + '.' +
        QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id)) + '.' +
        QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id)) + ' (' +
        ISNULL(mid.equality_columns, '') +
        CASE
            WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL
            THEN ', '
            ELSE ''
        END +
        ISNULL(mid.inequality_columns, '') + ')' +
        ISNULL(' INCLUDE (' + mid.included_columns + ')', '') +
        ';' AS CreateStatement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY ImpactScore DESC;

⚠️ Blocking and Deadlock Analysis

Current Blocking Chains

-- Identify blocking chains
WITH BlockingChain AS (
    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        s.login_name,
        s.host_name,
        DB_NAME(r.database_id) AS database_name,
        t.text AS sql_text,
        0 AS blocking_level
    FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    WHERE r.blocking_session_id = 0
      AND EXISTS (
          SELECT 1 FROM sys.dm_exec_requests r2
          WHERE r2.blocking_session_id = r.session_id
      )

    UNION ALL

    SELECT
        r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        s.login_name,
        s.host_name,
        DB_NAME(r.database_id) AS database_name,
        t.text AS sql_text,
        bc.blocking_level + 1
    FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    INNER JOIN BlockingChain bc ON r.blocking_session_id = bc.session_id
    WHERE bc.blocking_level < 10  -- Prevent infinite recursion
)
SELECT
    REPLICATE('  ', blocking_level) + CAST(session_id AS VARCHAR) AS session_hierarchy,
    session_id,
    blocking_session_id,
    wait_type,
    wait_time / 1000 AS wait_seconds,
    wait_resource,
    login_name,
    host_name,
    database_name,
    LEFT(sql_text, 500) AS sql_text_preview
FROM BlockingChain
ORDER BY blocking_level, session_id;

Lock Analysis

-- Detailed lock analysis
SELECT
    tl.resource_type,
    tl.resource_subtype,
    tl.resource_database_id,
    DB_NAME(tl.resource_database_id) AS database_name,
    tl.resource_associated_entity_id,
    CASE
        WHEN tl.resource_type = 'OBJECT'
        THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)
        ELSE NULL
    END AS object_name,
    tl.request_mode,
    tl.request_type,
    tl.request_status,
    tl.request_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.wait_time / 1000 AS wait_seconds,
    t.text AS sql_text
FROM sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON tl.request_session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE tl.request_status != 'GRANT'
   OR tl.request_session_id IN (
       SELECT blocking_session_id
       FROM sys.dm_exec_requests
       WHERE blocking_session_id != 0
   )
ORDER BY tl.request_status, wait_seconds DESC;

🔧 Memory and Buffer Pool Analysis

Buffer Pool Usage by Object

-- What's consuming buffer pool memory?
SELECT TOP 50
    OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    COUNT(*) AS BufferPages,
    COUNT(*) * 8 / 1024 AS BufferMB,
    SUM(CAST(bd.is_modified AS INT)) AS DirtyPages,
    SUM(CAST(bd.is_modified AS INT)) * 8 / 1024 AS DirtyMB
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.allocation_units au
    ON bd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions p
    ON au.container_id = p.partition_id
INNER JOIN sys.indexes i
    ON p.object_id = i.object_id
    AND p.index_id = i.index_id
WHERE bd.database_id = DB_ID()
  AND p.object_id > 100  -- Exclude system objects
GROUP BY p.object_id, i.name, i.type_desc
ORDER BY BufferPages DESC;

Memory Clerk Analysis

-- Memory allocation by clerk type
SELECT
    type AS ClerkType,
    name AS ClerkName,
    pages_kb / 1024 AS MemoryMB,
    virtual_memory_reserved_kb / 1024 AS VirtualReservedMB,
    virtual_memory_committed_kb / 1024 AS VirtualCommittedMB,
    awe_allocated_kb / 1024 AS AWEAllocatedMB
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
ORDER BY pages_kb DESC;

Plan Cache Analysis

-- Plan cache usage and efficiency
SELECT
    objtype AS ObjectType,
    COUNT(*) AS PlanCount,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS TotalSizeMB,
    AVG(usecounts) AS AvgUseCounts,
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS SingleUsePlans,
    SUM(CASE WHEN usecounts = 1 THEN CAST(size_in_bytes AS BIGINT) ELSE 0 END) / 1024 / 1024 AS SingleUsePlansMB
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY TotalSizeMB DESC;

-- Ad-hoc plan cache bloat analysis
SELECT TOP 25
    plan_handle,
    usecounts,
    size_in_bytes / 1024 AS SizeKB,
    cacheobjtype,
    objtype,
    st.text AS QueryText
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE objtype = 'Adhoc'
  AND usecounts = 1
ORDER BY size_in_bytes DESC;

⚠️ Warning: Single-use ad-hoc plans can bloat the plan cache. Consider enabling "Optimize for Ad hoc Workloads" server option.

📊 I/O Performance Analysis

File-Level I/O Statistics

-- I/O latency by database file
SELECT
    DB_NAME(vfs.database_id) AS DatabaseName,
    mf.name AS FileName,
    mf.type_desc AS FileType,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.num_of_bytes_read / 1024 / 1024 AS MBRead,
    vfs.num_of_bytes_written / 1024 / 1024 AS MBWritten,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    vfs.io_stall AS TotalIOStall_ms,
    CASE
        WHEN vfs.num_of_reads > 0
        THEN vfs.io_stall_read_ms / vfs.num_of_reads
        ELSE 0
    END AS AvgReadLatency_ms,
    CASE
        WHEN vfs.num_of_writes > 0
        THEN vfs.io_stall_write_ms / vfs.num_of_writes
        ELSE 0
    END AS AvgWriteLatency_ms,
    CASE
        WHEN (vfs.num_of_reads + vfs.num_of_writes) > 0
        THEN vfs.io_stall / (vfs.num_of_reads + vfs.num_of_writes)
        ELSE 0
    END AS AvgLatency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
INNER JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall DESC;

I/O Latency Thresholds

✅ Creating a Monitoring Dashboard

Comprehensive Server Health Check

-- SQL Server Health Dashboard
SET NOCOUNT ON;

PRINT '========================================';
PRINT 'SQL SERVER HEALTH CHECK - ' + CONVERT(VARCHAR, GETDATE(), 120);
PRINT '========================================';
PRINT '';

-- Instance Information
PRINT '-- INSTANCE INFORMATION --';
SELECT
    @@SERVERNAME AS ServerName,
    @@VERSION AS SQLVersion,
    (SELECT value_in_use FROM sys.configurations WHERE name = 'max server memory (MB)') AS MaxMemoryMB,
    (SELECT cntr_value FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Target Server Memory (KB)') / 1024 AS TargetMemoryMB,
    (SELECT cntr_value FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Total Server Memory (KB)') / 1024 AS CurrentMemoryMB;

-- CPU Pressure Check
PRINT '';
PRINT '-- CPU PRESSURE CHECK --';
SELECT
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdleCPU,
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLServerCPU,
    100 - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
        - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS OtherCPU
FROM (
    SELECT TOP 1 CONVERT(XML, record) AS record
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
      AND record LIKE '%<SystemHealth>%'
    ORDER BY timestamp DESC
) AS x;

-- Memory Pressure Check
PRINT '';
PRINT '-- MEMORY PRESSURE --';
SELECT
    (total_physical_memory_kb / 1024) AS TotalPhysicalMemoryMB,
    (available_physical_memory_kb / 1024) AS AvailablePhysicalMemoryMB,
    system_memory_state_desc AS MemoryState
FROM sys.dm_os_sys_memory;

-- Page Life Expectancy
PRINT '';
PRINT '-- PAGE LIFE EXPECTANCY --';
SELECT
    object_name,
    counter_name,
    cntr_value AS PageLifeExpectancy
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer Manager%';

-- Active Sessions Summary
PRINT '';
PRINT '-- ACTIVE SESSIONS --';
SELECT
    COUNT(*) AS TotalSessions,
    SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) AS RunningSessions,
    SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS SleepingSessions,
    SUM(CASE WHEN status = 'suspended' THEN 1 ELSE 0 END) AS SuspendedSessions
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

-- Blocking Check
PRINT '';
PRINT '-- BLOCKING CHECK --';
SELECT
    COUNT(*) AS BlockedSessions,
    MAX(r.wait_time) / 1000 AS MaxWaitSeconds
FROM sys.dm_exec_requests r
WHERE r.blocking_session_id != 0;

PRINT '';
PRINT '========================================';
PRINT 'HEALTH CHECK COMPLETE';
PRINT '========================================';

🔧 Automated DMV Collection

Create a Baseline Collection Job

-- Create baseline statistics table
CREATE TABLE dbo.DMVBaseline (
    CollectionTime DATETIME2 DEFAULT SYSDATETIME(),
    MetricCategory VARCHAR(50),
    MetricName VARCHAR(100),
    MetricValue DECIMAL(18,2),
    AdditionalInfo NVARCHAR(MAX)
);

-- Stored procedure for baseline collection
CREATE OR ALTER PROCEDURE dbo.CollectDMVBaseline
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CollectionTime DATETIME2 = SYSDATETIME();

    -- Wait Statistics
    INSERT INTO dbo.DMVBaseline (CollectionTime, MetricCategory, MetricName, MetricValue, AdditionalInfo)
    SELECT
        @CollectionTime,
        'WaitStats',
        wait_type,
        wait_time_ms,
        CONCAT('WaitingTasks:', waiting_tasks_count, ',SignalWait:', signal_wait_time_ms)
    FROM sys.dm_os_wait_stats
    WHERE wait_time_ms > 0
      AND wait_type NOT LIKE '%SLEEP%'
      AND wait_type NOT LIKE '%QUEUE%';

    -- PLE
    INSERT INTO dbo.DMVBaseline (CollectionTime, MetricCategory, MetricName, MetricValue)
    SELECT
        @CollectionTime,
        'Memory',
        'PageLifeExpectancy',
        cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Page life expectancy'
      AND object_name LIKE '%Buffer Manager%';

    -- Batch Requests/sec
    INSERT INTO dbo.DMVBaseline (CollectionTime, MetricCategory, MetricName, MetricValue)
    SELECT
        @CollectionTime,
        'Workload',
        'BatchRequests',
        cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Batch Requests/sec';

    -- CPU Usage
    INSERT INTO dbo.DMVBaseline (CollectionTime, MetricCategory, MetricName, MetricValue)
    SELECT
        @CollectionTime,
        'CPU',
        'SQLServerCPU',
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
    FROM (
        SELECT TOP 1 CONVERT(XML, record) AS record
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
        ORDER BY timestamp DESC
    ) AS x;

    PRINT 'Baseline collection completed at ' + CONVERT(VARCHAR, @CollectionTime, 120);
END;
GO

📈 Performance Trending

-- Analyze wait statistics trends
SELECT
    CAST(CollectionTime AS DATE) AS CollectionDate,
    MetricName AS WaitType,
    AVG(MetricValue) AS AvgWaitMs,
    MAX(MetricValue) AS MaxWaitMs,
    MIN(MetricValue) AS MinWaitMs
FROM dbo.DMVBaseline
WHERE MetricCategory = 'WaitStats'
  AND CollectionTime >= DATEADD(DAY, -7, GETDATE())
GROUP BY CAST(CollectionTime AS DATE), MetricName
HAVING AVG(MetricValue) > 1000
ORDER BY CollectionDate DESC, AvgWaitMs DESC;

Conclusion

Dynamic Management Views are essential tools for every SQL Server DBA. Key takeaways:

• Wait Statistics are your first stop for performance troubleshooting
• Index DMVs help identify missing indexes and remove unused ones
• Memory DMVs reveal buffer pool efficiency and memory pressure
• I/O DMVs pinpoint storage bottlenecks
• Regular baseline collection enables trend analysis

Master these DMVs, and you'll have the visibility needed to keep your SQL Server environments running at peak performance.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in SQL Server, SQL optimization, and enterprise database solutions.

Related Articles

• SQL Server Query Plan Analysis
• SQL Server Extended Events Monitoring
• SQL Server Performance Tuning Guide

Need help with SQL Server optimization? Contact us for expert database consulting.