Dynamic Management Views (DMV) and functions are a useful resource for a DBA to obtain the server and database state information. The status of internal and implementation-specific data can be obtained along with the schemas. DMVs and functions are divided into two types: server-scoped and database-scoped. Further, each of them is organized into different categories such as Database mirroring, disk, CPU, Change Data Capture, Extended Events, SQLOS, Resource Governor, and many more. In this recipe, we will go through the recommended practices and best usage processes of DMVs in the SQL Server environment.
The following steps are formed as a guidance in building best usage processes of DMVs. For the record, there are a total of 136 DMVs and functions available in SQL Server 2008 R2.
Note
The number of DMVs will be changed based on the service pack releases and in future versions of SQL Server.
1. To obtain the list of DMVs...