Database Reference
In-Depth Information
Building best usage processes of Dynamic
Management Views
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.
How to do it...
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.
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 and functions, let us execute the TSQL:
USE master
GO
SELECT name,type_desc,create_date,is_ms_shipped
FROM sys.system_objects WHERE name like 'DM[_]%'
ORDER BY name, type_desc
2.
Once we obtain the list of all DMVs and functions, it is essential to group the
essential DMVs that are used in day-to-day operations. Use the TSQL as follows:
--Get a list of grouped DMVs
SELECT name as [DMV/DMF Name],
type_desc as [Type],
[GroupName] =
CASE
WHEN name LIKE 'dm_exec%' THEN 'Query/Session Execution'
WHEN name LIKE 'dm_os%' THEN 'SQL Server Operating System'
WHEN name LIKE 'dm_io%' THEN 'Disk I/O'
WHEN name LIKE 'dm_tran%' THEN 'Database Transactions and locks'
WHEN name LIKE 'dm_db_%index%' THEN 'Indexes'
WHEN name LIKE 'dm_db%' THEN 'Database and database objects'
WHEN name LIKE 'dm_db_mirror%' THEN 'Database Mirroring'
WHEN name LIKE 'dm_resou%' THEN 'Resource Governor'
Search WWH ::
Custom Search