Chapter 47. How to use Dynamic Management Views

Glenn Berry

This chapter covers how to use Dynamic Management Views (DMVs) to easily uncover detailed information about your SQL Server workload that can help you diagnose and correct many common SQL Server performance issues. This type of information is extremely valuable, especially in a high-volume production online transaction processing (OLTP) situation, where poor database performance can have a severe impact on your business.

Why should I use DMV queries?

Dynamic Management Views (and Dynamic Management Functions) were introduced in SQL Server 2005, and enhanced in SQL Server 2008. Back in the pre–SQL Server 2005 days, it was much more difficult to find out what was happening inside of SQL Server when you encountered performance issues. Older versions of SQL Server were more like a “black box,” which made you work much harder to find bottlenecks. Fortunately, tracking down bottlenecks is much easier now.

Setting up security to run DMV queries

Looking at top waits at the instance level

Looking for CPU pressure and what’s causing it

Finding I/O pressure in SQL Server

SQL Server memory pressure

SQL Server index usage

Detecting blocking in SQL Server

Summary