Chapter 29. My favorite DMVs, and why
This chapter covers some of my favorite Dynamic Management Views and Dynamic Management Functions in SQL Server 2005 and SQL Server 2008. I will start out by giving some background about DMVs. Then I will list some details about the individual views and functions that I use most frequently. Finally, I will show you some interesting queries you can use to take full advantage of the information that these objects expose.
As an architect first and foremost, I have always been interested in the performance and usage information I can extract out of my SQL Server instances. In versions prior to SQL Server 2005, there was a wide range of ways to get this information, such as cryptic DBCC commands, bit flags in system tables (mostly undocumented), and system stored procedures (some unsupported). SQL Server 2005 introduced us to a new way to derive this data: Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), collectively known as DMVs.
DMVs brought about a more consistent way to obtain the existing information we were used to retrieving. They also introduced new data that we had never had access to before, or could only access via auditing, extensive tracing, or memory dumps. Consistency is important, as we finally have a relatively stable set of conventions for how the DMVs and DMFs are named, present common data types and column names, and exist in the sys schema.
