Dynamic Management Views (DMVs) in SQL Server 2008 R2

This article explains Dynamic Management Views (DMVs) in SQL Server 2008 R2. This provides the current information about the procedures of the service and the server health. These values will help administrators to identify problems that cannot be caught in the SQL Server Profiler or Performance Monitor.

Get all the DMVs using the following queries:

SELECT name AS DMV_Name,

[type] AS DMV_Type,

type_desc

FROM sys.system_objects

WHERE name LIKE 'dm_%'

ORDER BY name

SELECT count(*) TotalDMVCount

FROM sys.system_objects

WHERE name LIKE 'dm_%'

Output

The most frequently used DMV sections are:

Database
Execution
IO
Index
SQL operating system

Database

sys.data_spaces
sys.databases
sys.database_files
sys.dm_exec_sessions
sys.dm_db_session_space_usage
sys.dm_db_partition_stats
sys.sysfiles

Example

-------------------For (page and row) count and current database files------------------

SELECT TOP 3 object_name(object_id) AS objname,

in_row_data_page_count,

in_row_reserved_page_count,

used_page_count,

reserved_page_count,

row_count

FROM sys.dm_db_partition_stats

SELECT name AS DataBaseName,

filename

FROM sys.sysfiles

Output

Execution Related

sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests
sys.dm_exec_cached_plans
sys.dm_exec_query_plans
sys.dm_exec_sql_text
sys.dm_exec_query_stats

Example

-------------------For all user section and connection------------------

SELECT session_id,

login_name,

last_request_end_time,

cpu_time

FROM sys.dm_exec_sessions

WHERE session_id >= 61

SELECT connection_id,

session_id,

client_net_address,

auth_scheme

FROM sys.dm_exec_connections

WHERE session_id >= 61

Output

I/O Related

sys.dm_io_virtual_file_stats
sys.dm_io_pending_io_requests
sys.dm_io_cluster_shared_drives

Example

-------Shows I/O stats for (data and log files) and virtual files no. of read and write

SELECT *

FROM sys.dm_io_pending_io_requests

SELECT TOP 5 db_name(database_id) DataBaseName,

file_id,

num_of_reads,

num_of_writes

FROM sys.dm_io_virtual_file_stats(NULL, NULL)

Output

Index Related

sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_db_index_operational_stats
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_columns

Example

--- Returns system_scansCounts with different date and objects fragmentation of index operations. --

DECLARE @DBName VARCHAR(50)='testnew'

DECLARE @dbid BIGINT-- Database DB Id

SET @dbid=DB_ID(@DBName)

SELECT @DBName AS DataBaseName,

system_scans,

last_system_scan

FROM sys.dm_db_index_usage_stats

WHERE database_id = @dbid

SELECT @DBName AS DataBaseName,

object_id,

avg_fragmentation_in_percent,

fragment_count

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL)

WHERE database_id = @dbid

AND fragment_count > 0

Output

SQL Server Operating System

sys.dm_os_performance_counters
sys.dm_os_schedulers
sys.dm_os_nodes
sys.dm_os_waiting_tasks
sys.dm_os_wait_stats

Example

---Returning cached page count for each database and How memory is used overall on the server, and how much memory is available----

SELECT COUNT(*)AS cached_pages_count,

db_name(database_id)

AS database_name,

database_id

FROM sys.dm_os_buffer_descriptors

WHERE database_id NOT IN ( 32767, 4, 2, 1,3, 5 )

GROUP BY db_name(database_id),

database_id

ORDER BY cached_pages_count DESC;

SELECT total_physical_memory_kb,

available_physical_memory_kb,

total_page_file_kb,

system_memory_state_desc

FROM sys.dm_os_sys_memory

Output