When and Why use themProvides information that was not available in previous version of sql serverProvides 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

SQL Operating System

Get a list of all DMOs

select name, type_descfrom sys.all_objects where name like 'dm%' order by name

Permissions

Server scoped-- view server state

Database scoped--view database state

Deny takes prescedencedeny state or deny select on an objectPeople should have sys admin privileges

Grant permissions

grant view server state to loginname

grant view database state to userdeny view server state to loginname

deny view database statemust create user in master first

Specific types of DMVs

database

execution

IO

Index

SQL operatng system

Database for page and row count

select object_name(object_id) as objname, * from sys.dm_db_partition_stats order by 1

select * sys.dm_io_pending_io_requests can be run when you think that io can be a bottleneck select * from sys.dm_io_virtual_file_stats (null,null)

select db_name(database_id), * from sys.dm_io_virtual_file_stats(null,null) --shows io stats for data and log files -- database id andfile id -- null returns all datadb_name is a funtion to return the name of the actualdatabase rather than database id

sys.dm_os_schedulers-- information abt processorssys.dm_os_sys_info-- info abt computer and abt resources available to and consumed by sql serversys.dm_os_sys_memory-- how memory is used overall on the server, and how much memory is available.sys.dm_os_wait_stats-- info abt all waitsDBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)Tips 1949

sys.dm_os_buffer_descriptors-- info abt all data pages that are currently in the sql server buffer poolTips 1181, 1187