Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. They can exactly tell what is going on with SQL Server and its objects at the moment.There are tow kinds of DMVs and DMFs. Server-scoped dynamic management views and functions. Database-scoped dynamic management views and functions. All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema.
Following are major categories of DMVs and DMVs.
- Common Language Runtime Related Dynamic Management Views
- I/O Related Dynamic Management Views and Functions
- Database Mirroring Related Dynamic Management Views
- Query Notifications Related Dynamic Management Views
- Database Related Dynamic Management Views
- Replication Related Dynamic Management Views
- Execution Related Dynamic Management Views and Functions
- Service Broker Related Dynamic Management Views
- Full-Text Search Related Dynamic Management Views
- SQL Server Operating System Related Dynamic Management Views
- Index Related Dynamic Management Views and Functions
- Transaction Related Dynamic Management Views and Functions
Reference : Pinal Dave (https://blog.sqlauthority.com)), BOL – Dynamic Management Views and Functions
9 Comments. Leave new
Dear sir/mam
I want to know that how to create dynamical procedure and function in sql-server 2005.
plz give me indetails with a procedure
Hey Pinal
Thank you very much. This is great, simple but to the point. Many times I find similar thing which you wrote other places but the way you write it is to the point and very very easy to understand.
Love you man! God bless you.
Want an query which get unsed table in an database
How do I use the functionality of Notification services in sql server 2008 using DMF and SSRS ?
How do I use the functionality of Notification services in sql server 2008 using DMF and SSRS ??
Hello,
I am just wondering if i can use the execution related dmv “dm_exec_query_stats” can be used to fetch data for a particular database and not for a particular instance?
can someone help me in this regard?
thanks
Suba
Hi Suba,
Use the below query:
SELECT DB_NAME(p.dbid), q.*
FROM sys.dm_exec_query_stats q
CROSS APPLY sys.dm_exec_query_plan(q.plan_handle) p
Regards,
Pinal Dave
Hello Pinal,
We have Sql Express Edition.I want to know what all queries has been fired till date.
Is there any way I can see list of queries ?
Hello Pinal,
How to find out which DMVs are Server scoped and which are Database-scoped?