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:

1. Database
2. Execution
3. IO
4. Index
5. 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

GO

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

GO

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

GO

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;

GO

SELECT total_physical_memory_kb,

available_physical_memory_kb,

total_page_file_kb,

system_memory_state_desc

FROM sys.dm_os_sys_memory

Output