DMVs in SQL Server 2005 - PART I

Introduction:

Microsoft has brought lot of new changes to the way how SQL Server works and handles things in SQL Server 2005. These changes have been incorporated not only to the developers but also to those who administer SQL Server databases and instances. One of the major changes that have been done is a new feature that comes with SQL Server 2005 that is called as Dynamic views. Let’s get to know what it exactly is and does.

What’s a Dynamic View:

Dynamic Management Views and functions are designed to give detailed information about internal working of SQL server 2005. The information gives a insight of what’s going inside SQL server 2005 like information on health of server and databases which can be used to monitor performance, diagnose problems and tune databases. They are designed to be used instead of system tables and functions in SQL server 2000.

DMV’s are of both views and table-valued functions. There are two types of DMV’s.
1. Server scope DMV
2. Database scope DMV

Server scope DMV’s apply to the entire server and are stored in master database whereas Database scope DMV’s are specific to that database.

Dynamic Management views can be identified with their name starting with dm_. They reside in the master database.

Dynamic Management Views and Functions are categorized into twelve as given below.

1. CLR related DMV’s
2. I/O related DMV’s
3. Database Mirroring related DMV’s
4. Query Notifications related DMV’s
5. Database related DMV’s
6. Replication related DMV’s
7. Execution related DMV’s
8. Service Broker related DMV’s
9. Full-Text Search related DMV’s
10. SQL Server Operating System related DMV’s
11. Index related DMV’s
12. Transaction related DMV’s

DMV’s are owned by the schema sys and is to be referenced in two part naming convention as sys.DMV name.

Permission Required:

1. Select permission required on the Dynamic Management View/Functions
2. View server state permission for server scope DMV’s
3. View database state permission for database scope DMV’s

Important:

Microsoft reserves the right to change/drop any DMV’s and DMF’s in future releases.

What's to come next:
------------------------------
1. Some Useful DMV's for Real Time
2. Transaction, IO and OS Related DMV's
3. Some Useful scripts using DMv's and DMF's

So keep checking and update yourself.