What are DMVs in SQL Server and how to use them?

What are DMVs in SQL Server?

Dynamic Management Views (DMVs) are system views in SQL Server. They provide insights into the internal workings of the database engine.

They provide real-time information about the state, health, and performance of the SQL Server instance which help database administrators (DBAs) monitor, troubleshoot, and optimize SQL Server environments efficiently.

Information return by the DMVs:

DMVs return server state data that can be used to monitor the health of the server, diagnose problems, and tune performance. They provide insights into:

• System Performance: Memory usage, I/O statistics, CPU utilization.
• Execution Details: Currently executing queries, execution plan handles, and query statistics.
• Concurrency: Blocking, locking, and latching information.
• Resource Contention: Wait statistics (what threads are waiting for).
• Caching: Details on the contents and efficiency of the plan cache and buffer pool.

DMVs behave like standard database views. So, we can query them using SELECT statements to retrieve valuable data about server activity, query execution, memory usage, indexing, wait statistics, and more.

Accessing DMVs also requires specific permissions, For example, VIEW SERVER STATE for server-level DMVs or VIEW DATABASE STATE for database-level DMVs.

Categories of DMVs in SQL Server

DMVs are categorized based on their focus area:

1. Server-Scoped DMVs: These Server scoped DMVs can be used to get information about the overall SQL Server instance, such as active connections, resource usage, and wait stats. These DMVs require VIEW SERVER STATE permissions.
2. Database-Scoped DMVs: These Server scoped DMVs focus on specific database health and performance metrics like index usage and query execution within that database. These DMVs require VIEW DATABASE STATE permissions.
3. Performance and Monitoring DMVs: These DMVs focus on monitoring performance metrics.
4. Transaction and Lock DMVs: These DMVs provide insights into locking, blocking, and transactions.

Important DMVs in SQL Server

1. Performance Monitoring and Query Optimization

1. sys.dm_exec_requests:
   • Shows information about executing requests.
   • Useful for identifying blocking or long-running queries.
2. sys.dm_exec_sessions:
   • Provides information about active sessions.
   • Combine with sys.dm_exec_requests to analyze user activity.
3. sys.dm_exec_query_stats:
   • Displays aggregated performance data for cached query plans.
   • Helps identify resource-intensive queries.
4. sys.dm_exec_sql_text:
   • Retrieves the SQL text of a query using its handle.
   • Often used with sys.dm_exec_requests or sys.dm_exec_query_stats.
5. sys.dm_exec_query_plan:
   • Returns the execution plan for a query.
   • Helps in analyzing how a query is executed and identifying optimization opportunities.

2. Index and Table Statistics

1. sys.dm_db_index_physical_stats:
   • Provides fragmentation information for indexes.
   • Useful for deciding whether to rebuild or reorganize indexes.
2. sys.dm_db_index_usage_stats:
   • Tracks how indexes are used (seeks, scans, updates).
   • Helps identify unused or underutilized indexes.
3. sys.dm_db_missing_index_details:
   • Displays details of missing indexes that could improve query performance.
4. sys.dm_db_missing_index_group_stats:
   • Shows aggregated statistics for missing index groups.

3. System and Server Health

1. sys.dm_os_wait_stats:
   • Displays information about wait types and their durations.
   • Useful for diagnosing performance bottlenecks.
2. sys.dm_os_sys_memory:
   • Shows memory usage and availability.
   • Helps monitor memory pressure.
3. sys.dm_os_schedulers:
   • Provides information about schedulers and CPU usage.
   • Helps analyze CPU contention and identify idle or overloaded schedulers.
4. sys.dm_os_performance_counters:
   • Exposes SQL Server performance counter data.
   • Useful for tracking metrics like buffer cache hit ratio, page life expectancy, etc.
5. sys.dm_os_sys_info:
   • Returns a snapshot of system-level information like the number of CPUs and total memory.

4. Transactions and Locking

1. sys.dm_tran_locks:
   • Displays information about current locks.
   • Useful for analyzing blocking and deadlocks.
2. sys.dm_tran_active_transactions:
   • Provides details about active transactions.
3. sys.dm_tran_database_transactions:
   • Returns details about database-level transactions.
4. sys.dm_exec_requests (again):
   • Identifies blocking sessions and their locks.

5. TempDB and Resource Monitoring

1. sys.dm_db_task_space_usage:
   • Monitors the amount of space allocated in TempDB for tasks.
2. sys.dm_db_session_space_usage:
   • Tracks TempDB space usage by sessions.
3. sys.dm_resource_governor_workload_groups:
   • Monitors resource consumption by workload groups.
4. sys.dm_io_virtual_file_stats:
   • Provides I/O statistics for database files.
   • Helps identify bottlenecks in disk I/O.

6. Query Store

1. sys.query_store_query:
   • Shows details about queries captured in the Query Store.
2. sys.query_store_plan:
   • Displays execution plans stored in the Query Store.
3. sys.query_store_runtime_stats:
   • Provides runtime statistics for queries in the Query Store.

DMV Use Cases

Here are three common scenarios and the corresponding DMVs which we can use to diagnose them:

1. Finding Slow Queries (Performance)

The sys.dm_exec_query_stats DMV contains aggregate performance statistics for queries that are currently in the plan cache (meaning they’ve run recently). For example, below SQL query can be used to find the top 10 queries consuming the most CPU time since the last service restart.

SELECT TOP 10
    qs.total_worker_time AS TotalCPU,
    qs.execution_count,
    qs.total_elapsed_time AS TotalDuration,
    -- Get the SQL text for the query handle
    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
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

2. Diagnosing Blocking (Concurrency)

The sys.dm_exec_requests and sys.dm_os_waiting_tasks DMVs are crucial for troubleshooting live blocking issues. For example, below SQL query can be used to identify which sessions are currently blocked and who the main blocker is.

SELECT
    r.session_id AS WaitingSession,
    r.blocking_session_id AS BlockerSession,
    w.wait_type,
    w.wait_duration_ms,
    -- Get the command of the session being blocked
    (SELECT text FROM sys.dm_exec_sql_text(r.sql_handle)) AS BlockedCommand
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_os_waiting_tasks w ON r.session_id = w.session_id
WHERE r.blocking_session_id != 0;

3. Identifying Server Bottlenecks (Wait Statistics)

The sys.dm_os_wait_stats DMV shows the cumulative time SQL Server threads have spent waiting for resources. This helps identify the primary bottleneck (I/O, CPU, or Locking). For example, below SQL query can be used to find the top 5 resources that threads are waiting on.

SELECT TOP 5
    wait_type,
    wait_time_ms / 1000.0 AS TotalWaitTimeSec,
    (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceWaitTimeSec,
    waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK', 'BROKER_RECEIVE_WAITFOR', 'LAZYWRITER_SLEEP')
ORDER BY TotalWaitTimeSec DESC;

Using DMVs Effectively

Combining DMVs:

DMVs can be used in combination for deeper insights. For example:

SELECT r.session_id, r.start_time, r.status, t.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id > 50;

This query retrieves active requests along with their SQL text.

Regular Monitoring:

Automate DMV queries to capture performance trends over time.

Permissions:

Ensure proper permissions (VIEW SERVER STATE or VIEW DATABASE STATE) for accessing DMVs.

Conclusion

Dynamic Management Views (DMVs) are essential tools for monitoring, diagnosing, and optimizing SQL Server performance. Using DMVs effectively can help DBAs ensure a stable and efficient SQL Server environment.