SQL Server Dynamic Management Views (DMVs) Guide

Dynamic Management Views (DMVs) are powerful tools in SQL Server that allow database administrators and developers to gain insight into the internal state of the server instance and databases. They provide real-time information about various aspects of SQL Server, including performance, memory usage, I/O operations, query execution, and more.

What are Dynamic Management Views (DMVs)?

DMVs are special views provided by SQL Server that return server state information. They are dynamic because the data they expose reflects the current activity and statistics since the last SQL Server restart or a specific counter reset. This dynamic nature makes them invaluable for:

Performance Troubleshooting: Identifying bottlenecks, long-running queries, and resource contention.
Monitoring: Tracking server health, resource utilization, and user activity.
Diagnosis: Pinpointing specific issues like deadlocks, blocking, and I/O latency.
Capacity Planning: Understanding resource consumption trends over time (when combined with historical data collection).

DMVs are typically prefixed with sys.dm_. Some related functions, called Dynamic Management Functions (DMFs), are also available and require parameters (e.g., sys.dm_os_buffer_descriptors (database_id)).

Categories of DMVs

DMVs can be broadly categorized based on the type of information they provide:

Operating System (OS) Related: Information about the SQL Server OS layer, including memory, CPU, I/O, and wait statistics.
Execution Related: Details about currently executing requests, sessions, and connections, as well as cached query plans and statistics.
I/O Related: Statistics on file I/O, disk latency, and buffer pool usage.
Index Related: Information on index usage, missing indexes, and index fragmentation.
Transaction Related: Details about active transactions, locks, and deadlocks.
Database Related: Statistics about database files, space usage, and object-specific information.
Availability Group Related (AlwaysOn AGs): Health and operational state of AlwaysOn Availability Groups.

Key DMVs and Their Purpose

Here's a list of some of the most commonly used and important DMVs, along with their purpose and a basic example query.

1. Operating System (OS) Related DMVs

sys.dm_os_wait_stats
- Purpose: Returns information about all the waits encountered by threads that executed. This is crucial for identifying performance bottlenecks, as high waits in certain categories indicate resource contention (CPU, I/O, memory, locks).
- Example:
```
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM
    sys.dm_os_wait_stats
ORDER BY
    wait_time_ms DESC;
```
sys.dm_os_performance_counters
- Purpose: Exposes performance counter information maintained by the server. Similar to Windows Performance Monitor counters but accessible directly within SQL Server.
- Example:
```
SELECT
    counter_name,
    cntr_value
FROM
    sys.dm_os_performance_counters
WHERE
    counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec', 'SQL Re-Compilations/sec');
```
sys.dm_os_latch_stats
- Purpose: Returns information on all latch waits encountered by threads. Latches are lightweight synchronization objects used by SQL Server to protect in-memory data structures. High latch contention can indicate memory or concurrency issues.
- Example:
```
SELECT
    latch_class,
    wait_time_ms,
    waiting_requests_count
FROM
    sys.dm_os_latch_stats
WHERE
    wait_time_ms > 0
ORDER BY
    wait_time_ms DESC;
```
sys.dm_os_sys_info
- Purpose: Provides a variety of server-level configuration and resource information, such as CPU count, physical memory, and SQL Server process affinity.
- Example:
```
SELECT
    cpu_count,
    physical_memory_kb / 1024 AS physical_memory_mb,
    sql_memory_model_desc
FROM
    sys.dm_os_sys_info;
```

2. Execution Related DMVs

sys.dm_exec_requests

Purpose: Returns information about each request that is executing on SQL Server. This is essential for identifying active queries, their status, wait types, and blocking chains.

Example:

SELECT
    session_id,
    status,
    command,
    blocking_session_id,
    wait_type,
    wait_time,
    last_wait_type,
    cpu_time,
    reads,
    writes,
    logical_reads,
    start_time,
    sql_handle,
    statement_start_offset,
    statement_end_offset
FROM
    sys.dm_exec_requests
WHERE
    session_id != @@SPID; -- Exclude current session

sys.dm_exec_sessions

Purpose: Returns one row per authenticated session on the SQL Server instance. Provides details like login name, host name, program name, and session status.

Example:

SELECT
    session_id,
    login_name,
    host_name,
    program_name,
    status,
    total_elapsed_time,
    cpu_time,
    memory_usage
FROM
    sys.dm_exec_sessions
WHERE
    is_user_process = 1; -- Show user sessions only

sys.dm_exec_connections
- Purpose: Returns information about the physical connections established to the SQL Server instance. Useful for seeing connection details like network protocol, authentication method, and last read/write times.
- Example:
```
SELECT
    session_id,
    connect_time,
    net_transport,
    protocol_type,
    client_net_address,
    num_reads,
    num_writes,
    last_read,
    last_write
FROM
    sys.dm_exec_connections;
```

sys.dm_exec_sql_text (sql_handle) (DMF)

Purpose: Returns the text of the SQL batch or object given a sql_handle. Often used in conjunction with sys.dm_exec_requests or sys.dm_exec_query_stats to retrieve the actual query text.

Example:

SELECT
    SUBSTRING(st.text, (er.statement_start_offset / 2) + 1,
              ((CASE er.statement_end_offset
                  WHEN -1 THEN DATALENGTH(st.text)
                  ELSE er.statement_end_offset
              END - er.statement_start_offset) / 2) + 1) AS statement_text
FROM
    sys.dm_exec_requests AS er
CROSS APPLY
    sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE
    er.session_id = <session_id_from_dm_exec_requests>; -- Replace with an actual session_id

sys.dm_exec_query_plan (plan_handle) (DMF)

Purpose: Returns the showplan XML for the query plan specified by the plan_handle. This is essential for analyzing how a query is executed.

Example:

SELECT
    qp.query_plan
FROM
    sys.dm_exec_requests AS er
CROSS APPLY
    sys.dm_exec_query_plan(er.plan_handle) AS qp
WHERE
    er.session_id = <session_id_from_dm_exec_requests>; -- Replace with an actual session_id

sys.dm_exec_query_stats

Purpose: Returns aggregate performance statistics for cached query plans. This DMV provides insights into frequently executed queries, their average execution times, CPU usage, I/O, etc.

Example:

SELECT TOP 10
    qs.total_worker_time AS total_cpu_time,
    qs.total_elapsed_time,
    qs.total_logical_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,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
    qs.total_worker_time DESC;

3. I/O Related DMVs

sys.dm_io_virtual_file_stats (database_id, file_id) (DMF)

Purpose: Returns I/O statistics for data and log files. Crucial for identifying I/O bottlenecks at the file level.

Example:

SELECT
    DB_NAME(database_id) AS database_name,
    file_id,
    num_reads,
    num_writes,
    io_stall_read_ms,
    io_stall_write_ms,
    io_stall AS total_io_stall_ms,
    size_on_disk_bytes / 1024 / 1024 AS size_on_disk_mb
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL); -- NULL, NULL for all databases and files

sys.dm_os_buffer_descriptors

Purpose: Returns information about all the data pages currently in the buffer pool. Helps understand memory pressure and which objects are consuming the most memory.

Example:

SELECT
    COUNT(*) AS cached_pages_count,
    obj.name AS object_name,
    idx.name AS index_name,
    SUM(bd.free_space_in_bytes) / 1024.0 / 1024.0 AS total_free_space_mb
FROM
    sys.dm_os_buffer_descriptors AS bd
INNER JOIN
    sys.allocation_units AS au ON bd.allocation_unit_id = au.allocation_unit_id
INNER JOIN
    sys.partitions AS p ON au.container_id = p.hobt_id
INNER JOIN
    sys.indexes AS idx ON p.index_id = idx.index_id AND p.object_id = idx.object_id
INNER JOIN
    sys.objects AS obj ON idx.object_id = obj.object_id
WHERE
    bd.database_id = DB_ID() -- Current database
    AND obj.is_ms_shipped = 0 -- Exclude system objects
GROUP BY
    obj.name, idx.name
ORDER BY
    cached_pages_count DESC;

sys.dm_os_volume_stats (database_id, file_id) (DMF)
- Purpose: Returns information about the operating system volume (drive) on which the specified database files are stored. Provides details like total space, available space, and logical/physical drive mappings.
- Example:
```
SELECT
    volume_mount_point,
    total_bytes / 1024 / 1024 / 1024 AS total_space_gb,
    available_bytes / 1024 / 1024 / 1024 AS available_space_gb
FROM
    sys.dm_os_volume_stats(DB_ID(), 1); -- For the current database's primary data file
```

4. Index Related DMVs

sys.dm_db_index_usage_stats

Purpose: Returns counts of different types of operations performed on indexes (seeks, scans, lookups, updates). Helps identify unused indexes for potential removal or heavily used indexes for optimization.

Example:

SELECT
    OBJECT_NAME(ius.object_id) AS table_name,
    idx.name AS index_name,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    last_user_seek,
    last_user_scan,
    last_user_lookup,
    last_user_update
FROM
    sys.dm_db_index_usage_stats AS ius
INNER JOIN
    sys.indexes AS idx ON ius.object_id = idx.object_id AND ius.index_id = idx.index_id
WHERE
    OBJECTPROPERTY(ius.object_id, 'IsUserTable') = 1 -- Only user tables
    AND ius.database_id = DB_ID()
ORDER BY
    (ius.user_seeks + ius.user_scans + ius.user_lookups) DESC;

sys.dm_db_index_physical_stats (database_id, object_id, index_id, partition_number, mode) (DMF)

Purpose: Returns size and fragmentation information for the data and indexes of the specified table or view. Essential for index maintenance planning.

Example:

SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    idx.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips
INNER JOIN
    sys.indexes AS idx ON ips.object_id = idx.object_id AND ips.index_id = idx.index_id
WHERE
    ips.avg_fragmentation_in_percent > 30 -- Highly fragmented
    AND ips.index_id > 0 -- Exclude heaps
    AND OBJECTPROPERTY(ips.object_id, 'IsUserTable') = 1;

sys.dm_db_missing_index_details
- Purpose: Returns detailed information about missing indexes that the query optimizer has identified as beneficial.
sys.dm_db_missing_index_groups
- Purpose: Returns information about groups of missing indexes.

sys.dm_db_missing_index_columns (index_handle) (DMF)

Purpose: Returns information about missing index columns.

Example (for missing indexes):

SELECT
    mid.database_id,
    DB_NAME(mid.database_id) AS database_name,
    mid.object_id,
    OBJECT_NAME(mid.object_id) AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' + REPLACE(REPLACE(REPLACE(mid.equality_columns, '[', ''), ']', ''), ', ', '_') + ISNULL(REPLACE(REPLACE(REPLACE(mid.inequality_columns, '[', ''), ']', ''), ', ', '_'), '') + ' ON ' + mid.statement + ' (' + mid.equality_columns + ISNULL(', ' + mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM
    sys.dm_db_missing_index_groups AS migs
INNER JOIN
    sys.dm_db_missing_index_details AS mid ON migs.index_handle = mid.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    migs.user_seeks DESC;

Note: The create_index_statement is a simplified example and might need adjustments for complex scenarios.

5. Transaction Related DMVs

sys.dm_tran_locks

Purpose: Returns information about currently active lock manager resources. Useful for diagnosing blocking and deadlocks.

Example:

SELECT
    request_session_id,
    resource_type,
    resource_database_id,
    DB_NAME(resource_database_id) AS database_name,
    resource_associated_entity_id,
    request_mode,
    request_status,
    request_owner_type
FROM
    sys.dm_tran_locks
WHERE
    request_session_id != @@SPID;

sys.dm_tran_session_transactions

Purpose: Returns information about transactions associated with sessions.

Example:

SELECT
    session_id,
    transaction_id,
    is_user_transaction,
    is_local
FROM
    sys.dm_tran_session_transactions;

6. Database Related DMVs

sys.dm_db_file_space_usage

Purpose: Returns space usage information for each file in the database.

Example:

SELECT
    DB_NAME(database_id) AS database_name,
    file_id,
    total_pages * 8 / 1024 AS total_size_mb,
    used_pages * 8 / 1024 AS used_space_mb,
    unallocated_extent_pages * 8 / 1024 AS unallocated_space_mb
FROM
    sys.dm_db_file_space_usage;

sys.dm_db_partition_stats

Purpose: Returns page and row count information for each partition in the current database.

Example:

SELECT
    OBJECT_NAME(ps.object_id) AS object_name,
    ps.index_id,
    idx.name AS index_name,
    ps.partition_number,
    ps.in_row_data_page_count,
    ps.row_count,
    ps.reserved_page_count * 8 / 1024 AS reserved_space_mb
FROM
    sys.dm_db_partition_stats AS ps
INNER JOIN
    sys.indexes AS idx ON ps.object_id = idx.object_id AND ps.index_id = idx.index_id
WHERE
    OBJECTPROPERTY(ps.object_id, 'IsUserTable') = 1
ORDER BY
    reserved_space_mb DESC;

7. Availability Group Related DMVs (for AlwaysOn AGs)

sys.dm_hadr_database_replica_states

Purpose: Returns a row for each database that is participating in an AlwaysOn Availability Group, providing information about its health and synchronization state.

Example:

SELECT
    drs.database_id,
    DB_NAME(drs.database_id) AS database_name,
    hars.replica_server_name,
    drs.synchronization_state_desc,
    drs.suspend_reason_desc,
    drs.redo_queue_size
FROM
    sys.dm_hadr_database_replica_states AS drs
INNER JOIN
    sys.dm_hadr_availability_replica_states AS hars ON drs.replica_id = hars.replica_id;

sys.dm_hadr_availability_replica_states
- Purpose: Returns a row for each availability replica in an AlwaysOn Availability Group, providing health information for each local and remote replica.
- Example:
```
SELECT
    replica_server_name,
    role_desc,
    connected_state_desc,
    synchronization_health_desc,
    last_connect_error_timestamp
FROM
    sys.dm_hadr_availability_replica_states;
```

Important Considerations

Permissions: To query most DMVs, you need VIEW SERVER STATE permission on the server. For database-specific DMVs, VIEW DATABASE STATE permission on the database is required.
Dynamic Data: The data in DMVs is real-time and often cumulative since the last SQL Server service restart. For historical analysis, you need to collect and store this data over time (e.g., using SQL Server Agent jobs, Extended Events, or a monitoring solution).
Performance Impact: While DMVs are designed to be efficient, querying very large DMVs frequently, or using complex joins, can have a minor performance impact, especially on heavily loaded servers. Use them judiciously.
Documentation: Always refer to the official Microsoft documentation for the most up-to-date and complete information on each DMV and its columns.

By leveraging DMVs, SQL Server professionals can gain deep insights into their database systems, proactively identify and resolve performance issues, and ensure the smooth operation of their critical applications.

SQL Server Dynamic Management Views (DMVs) and Functions (DMFs) Summary

DMV/DMF Name	Purpose
`sys.dm_os_wait_stats`	Returns information about all the waits encountered by threads, crucial for identifying performance bottlenecks.
`sys.dm_os_performance_counters`	Exposes performance counter information maintained by the server, similar to Windows Performance Monitor counters.
`sys.dm_os_latch_stats`	Returns information on all latch waits encountered by threads, indicating memory or concurrency issues.
`sys.dm_os_sys_info`	Provides server-level configuration and resource information (CPU count, physical memory, SQL Server process affinity).
`sys.dm_exec_requests`	Returns information about each request currently executing, essential for identifying active queries, status, wait types, and blocking.
`sys.dm_exec_sessions`	Returns one row per authenticated session, providing details like login name, host name, program name, and session status.
`sys.dm_exec_connections`	Returns information about physical connections, including network protocol, authentication, and last read/write times.
`sys.dm_exec_sql_text (sql_handle)`	Returns the text of the SQL batch or object for a given `sql_handle`.
`sys.dm_exec_query_plan (plan_handle)`	Returns the showplan XML for a query plan specified by the `plan_handle`, essential for execution plan analysis.
`sys.dm_exec_query_stats`	Returns aggregate performance statistics for cached query plans, providing insights into frequently executed queries.
`sys.dm_io_virtual_file_stats (database_id, file_id)`	Returns I/O statistics for data and log files, crucial for identifying I/O bottlenecks at the file level.
`sys.dm_os_buffer_descriptors`	Returns information about data pages in the buffer pool, helping understand memory pressure and object memory consumption.
`sys.dm_os_volume_stats (database_id, file_id)`	Returns information about the operating system volume (drive) where database files are stored, including space and logical/physical mappings.
`sys.dm_db_index_usage_stats`	Returns counts of operations performed on indexes (seeks, scans, lookups, updates), useful for identifying unused or heavily used indexes.
`sys.dm_db_index_physical_stats (database_id, object_id, index_id, partition_number, mode)`	Returns size and fragmentation information for data and indexes, essential for index maintenance planning.
`sys.dm_db_missing_index_details`	Returns detailed information about missing indexes identified by the query optimizer as beneficial.
`sys.dm_db_missing_index_groups`	Returns information about groups of missing indexes.
`sys.dm_db_missing_index_columns (index_handle)`	Returns information about specific columns within missing indexes.
`sys.dm_tran_locks`	Returns information about currently active lock manager resources, useful for diagnosing blocking and deadlocks.
`sys.dm_tran_session_transactions`	Returns information about transactions associated with sessions.
`sys.dm_db_file_space_usage`	Returns space usage information for each file in the database.
`sys.dm_db_partition_stats`	Returns page and row count information for each partition in the current database.
`sys.dm_hadr_database_replica_states`	Returns health and synchronization state for each database participating in an AlwaysOn Availability Group.
`sys.dm_hadr_availability_replica_states`	Returns health information for each availability replica (local and remote) in an AlwaysOn Availability Group.

N.R.DAS

LINKS

Navigation

Azure Network Ques || Azure AD Ques || Cybersecurity || Azure AD, AD vs ADConnect || Azure Storage 30 Ques.

SQL DMV's Guide

SQL Server Dynamic Management Views (DMVs) Guide

What are Dynamic Management Views (DMVs)?

Categories of DMVs

Key DMVs and Their Purpose

1. Operating System (OS) Related DMVs

2. Execution Related DMVs

3. I/O Related DMVs

4. Index Related DMVs

5. Transaction Related DMVs

6. Database Related DMVs

7. Availability Group Related DMVs (for AlwaysOn AGs)

Important Considerations

SQL Server Dynamic Management Views (DMVs) and Functions (DMFs) Summary

No comments: