Dynamic Management View Categorization in SQL Server
By : Kasim Wirama, MCDBA, MCITP
Dynamic management view (DMV) is one of system object in SQL Server. It is the way how you look at SQL Server metadata. It is introduced since SQL Server 2005 and it is used in SQL Server 2008 with no exception. There is not only dynamic management view as system object, but also there are catalog view (CV), dynamic management function (DMF) and system administration function.
There are a bunch of them, and I was hardly able to memorize them when SQL Server 2005 was freshly released. And after I observe them for more than a year in Books Online, I find a pattern about naming convention to help me memorizing them.
Here is the list of DMV/DMF prefixes:
1. Sys.dm_db_mirroring_*
It is for database mirroring.
2. Sys.dm_broker_*
it is for service broker.
3. Sys.dm_tran_*
It is for querying transaction information such as blocking, lock manager, etc.
4. Sys.dm_os_*
It is for querying server wide information such as CPU thread utilization, SQL Server internal memory pools etc
5. Sys.dm_xe_*
It is for extended events in SQL Server 2008
6. Sys.dm_exec_*
It is where you want to query your SQL code behavior in execution environment.
7. Sys.dm_db_*
This is where you want to get information about your database and storage utilization.
8. Sys.dm_clr_*
This is the prefix where you want to query information about SQL CLR that has been deployed into target database.
9. Sys.dm_db_index_* or sys.dm_db_missing_index_*
This is the prefix where you want to get information about your index utilization.
10. Sys.dm_resource_governor_*
This is the prefix where I want to get information about resource governor in SQL Server 2008.
For CV, there is not such a consistent prefix like DMV/DMF, only few that I can see it is as consistent as DMV. It is : sys.fulltext_* (where I want to query about fulltext indexes).
Hope these classifications will help you to memorize them easily.