SQL Server Dynamic Management Views (DMVs) cheatsheet

Richard P
4 min readAug 26, 2023

These DMVs and catalog views can help you to monitor database performance and troubleshoot slow queries. This is a cheatsheet focused on DMVs that are tested on the DP-300 Administering Microsoft Azure SQL Solutions exam. This is not an exhaustive list, and mainly focuses on queries and concepts that I found are included on the exam.

DMVs are queries that return internal data about the state of the database or the instance. — Describe dynamic management views and functions

image created by author

DMV Permissions

These permissions are necessary for running DMV queries. This access would be given to a login through a role assignment. It’s helpful to only give the access that you need, such as in the case of hiring outside consultants to monitor the database.

-- Server scoped objects 
GRANT VIEW SERVER STATE TO MyUser
-- Database scoped objects
GRANT VIEW DATABASE STATE TO MyUser
-- database configs that override server configs
SELECT * FROM sys.database_scoped_configurations

I/O and Waits

These queries show how long a task has been waiting, what it is waiting on, and what type of wait is responsible. Wait types can be useful for diagnosing resource contention issues related to CPU, Memory, and tempdb allocation.

-- I/O and wait problems
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_wait_stats
-- I/O latency stats
SELECT *
FROM sys.dm_io_virtual_file_stats(DB_ID('MyDb'), NULL)
ORDER BY io_stall DESC;

Query Performance

Setting the database configuration LAST_QUERY_PLAN_STATS will capture an an actual execution plan for every query and will be stored in the plan cache. After enabling this configuration you can find the last execution plan for a query by retrieving it from the plan cache. Also, it can be useful to let SQL Server recommend missing indexes that can potentially speed up your queries.

-- enable the collection of last query plan statistics
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
-- Get last actual execution plan for all cached queries
SELECT * FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY…

--

--