DMV 'Dynamic management views' in SQL Server

What are DMVs?

•Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance

•Introduced in SQL Server 2005 •Two Types

–Server-scoped

–Database-scoped

•Dynamic Management Objects (DMO)

–Dynamic Management Views (DMV)

•Can select like a view

–Dynamic Management Functions (DMF)

•Requires input parameters like a function

When and why use?

•Provides information that was not available in previous versions of SQL

•Provides a simpler way to query the data just like any other view versus using DBCC commands or system stored procedures.

Types of DMVs

•Change Data Capture

•Common Language Runtime

•Database Mirroring •Database

•Execution

•Full-Text Search

•I/O

•Index

•Object

•Query Notifications

•Replication

•Resource Governor

•Security

•Service Broker

•SQL Server Extended Events

•SQL Server Operating System

•Transaction

Get list of all DMOs

SELECT name, type_desc

FROM sys.all_objects

WHERE name LIKE 'dm%'

ORDER BY name

Permissions

•Server-scoped –VIEW SERVER STATE

•Database-scoped –VIEW DATABASE STATE

•DENY takes precedence –DENY state or –DENY SELECT on an object

GRANT / DENY Permissions

• GRANT VIEW SERVER STATE TO loginName

• GRANT VIEW DATABASE STATE TO user

• DENY VIEW SERVER STATE TO loginName

• DENY VIEW DATABASE STATE TO user

• Must create user in MASTER first

DENY SELECT ON sys.dm_os_sys_info TO loginName

Specific Types

•Database

• sys.dm_db_partition_stats

(page and row count information)

•Execution

• sys.dm_exec_sessions

(Information about all active user connections and internal tasks)

• sys.dm_exec_connections

(Information about the connections established)

• sys.dm_exec_requests

(Information about each request that is executing (includes all system

processes))

•Execution – Query Plans

• sys.dm_exec_sql_text (DMF)

(Returns text of SQL batch)

• sys.dm_exec_query_plan (DMF)

(Returns the showplan in XML format)

• sys.dm_exec_query_stats

(Statistics for cached query plans)

• sys.dm_exec_cached_plans

(Each query plan that is cached)

•IO

• sys.dm_io_pending_io_requests

(Shows pending I/O requests)

• sys.dm_io_virtual_file_stats

(Shows I/O stats for data and log files)

•Index

• sys.dm_db_index_operational_stats (DMF)

(Shows I/O, locking and access information such as inserts, deletes, updates…)

• sys.dm_db_index_physical_stats (DMF)

(Shows index storage and fragmentation information)

• sys.dm_db_index_usage_stats (DMV)

(Shows how often indexes are used and for what type of operation)

•Missing Indexes

– 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

•SQL Operating System

• sys.dm_os_schedulers

(Information about processors)

• sys.dm_os_sys_info

(Information about the computer and about resources available to and consumed by SQL Server)

• sys.dm_os_sys_memory

(Memory information from the operating system)

• sys.dm_os_wait_stats

(Information about all the waits encountered by threads that executed)

•Clear Wait Stats

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

• sys.dm_os_buffer_descriptors

(Information about all the data pages that are currently in the SQL Server buffer pool)

I hope this information will be useful for you. Please write your comments and related question. I will be happy to answer them.