Introduction

With the inception of SQL Server 2005, Microsoft has introduced a very helpful feature in SQL Server known as Dynamic Management Views(DMV) and Dynamic Management Functions(DMF). These views and functions are used to retrieve internal statistics of an SQL Server instance for performance monitoring. They provide real-time statistics about a variety of the internal working details of the SQL Server that can be used for performance analysis to identify performance bottlenecks and hardware bottlenecks, and tune the performance of SQL Server. Some of the performance issues can be identified and the necessary steps to be taken can be determined easily just by looking at the statistics returned by DMVs and DMFs. This feature is a real blessing for database administrators.

All DMVs and DMFs are located in the sys schema and all DMVs and DMFs have a common naming convention, which is dm_*. This prefix is generally followed by a category prefix to which a DMV or DMF belongs, which is...