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
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_configurationsI/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.
