Building Diagnostic Queries Using DMVs and DMFs

Dynamic management views (DMVs) and dynamic management functions (DMFs) expose relevant real-time information that can unlock the secrets of T-SQL execution and SQL Database Engine health, even on a live production server. There are hundreds of DMVs and DMFs (collectively referred to as DMVs) available in the SQL Database Engine, and while they are mostly documented, it may not be obvious how they can be used by database developers and administrators to troubleshoot performance both in production systems and during the development process.

In this chapter, we will start by enumerating some of the DMVs that are most relevant for both T-SQL developers and database administrators alike to troubleshoot T-SQL query performance. Building on this information, we will provide real-world examples to explore how to use DMVs to troubleshoot different poor-performance scenarios, as well as give us the information needed to begin building our own...