What are Dynamic Management Views (DMVs)
Another tool at your disposal to measure performance and view details about the SQL Server is the DMVs. Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance
There are two different kinds of DMVs and DMFs:
- · Server-scoped: These look at the state of an entire SQL Server instance.
- · Database-scoped: These look at the state of a specific database
- · While the DMV can be used like the select statement, the DMF requires a parameter
- · Some apply to entire server and are stored in the master database while others to each database
- · Over 200 DMVs at this time
The DMVs are broken down into the following categories:
- · Change Data Capture Related Dynamic Management Views
- · Change Tracking Related Dynamic Management Views
- · Common Language Runtime Related Dynamic Management Views
- · Database Mirroring Related Dynamic Management Views
- · Database Related Dynamic Management Views
- · Execution Related Dynamic Management Views and Functions
- · Extended Events Dynamic Management Views
- · Full-Text Search Related Dynamic Management Views
- · Filestream-Related Dynamic Management Views (Transact-SQL)
- · I/O Related Dynamic Management Views and Functions
- · Index Related Dynamic Management Views and Functions
- · Object Related Dynamic Management Views and Functions
- · Query Notifications Related Dynamic Management Views
- · Replication Related Dynamic Management Views
- · Resource Governor Dynamic Management Views
- · Service Broker Related Dynamic Management Views
- · SQL Server Operating System Related Dynamic Management Views
- · Transaction Related Dynamic Management Views and Functions
- · Security Related Dynamic Management Views
–website to DMV
https://msdn.microsoft.com/en-us/library/ms188754.aspx
View All DMV’s Through Below Query:-
SELECT name, type, type_desc
FROM sys.system_objects
WHERE name LIKE ‘dm_%’
ORDER BY 2 desc