How to find list of DMVs & DMFs?
As most of blogs in this section travels through usage of Dynamic Management Views (DMV) & Dynamic Management Functions (DMF) I have had an email asking What is DMV & DMF and how to find list of them from a SQL Server instance.
The internal mechanism of DMV is totally relies on calls to a set of Dynamic Link Libraries that return information directly from the SQL Server engine. This is useful to designed in answering the different set of queries with static views from the master database system tables. Though DMV & DMF are designed to get internal information be aware the results information is changed rapidly and it will be more effective to ask the engine what is happening than for the engine to store that kind of data in tables to be queried. There are various categories such as CLR, Execution, I/O, Index usage, Service broker etc. I'm sure there will be a section under BOL to refer more on these categories.
sys.schema catalog view is the object where it had this information of about each database schema, to get a list of DMVs & DMFs run:
USE master
GO
SELECT name
FROM sys.all_objects
WHERE name LIKE ’dm%’
ORDER BY name
GO
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.