SQL SERVER – 2005 – Dynamic Management Views (DMV) and Dynamic Management Functions (DMF)

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 (http://blog.SQLAuthority.com)), BOL – Dynamic Management Views and Functions

About these ads

8 thoughts on “SQL SERVER – 2005 – Dynamic Management Views (DMV) and Dynamic Management Functions (DMF)

  1. 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.

  2. 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

  3. 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 ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s