Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

SQL Server world: Learning and Sharing my experience & knowledge ....
Browse by Tags · View All
sql server 51
best practices 39
#SQL Server 28
dba 25
BRH 19
beginners 17
download 15
#TSQL 15
dba practices 9
#DBA 9

Archive · View All
September 2010 13
August 2011 5
October 2010 5
November 2011 4
October 2011 4
May 2011 4
February 2011 4
November 2010 4
January 2012 3
July 2011 3

SQL Server DMVs - where to start and how to use?

Sep 20 2010 2:48PM by Satya Jayanty (@sqlmaster)   


When you need to troubleshoot a problem, prior to SQL Server 2005 you have to depend upon PROFILER or query system tables constantly or some other means to trace the system usage.

SQL Server 2005 onwards DBAs are equipped with Dynamic Management Views (and Data Mining Extenstions DMX for Analysis Services) for core database engine to obtain the snapshot of information on resource usage such as disk, cpu, memory and many more. The 2 types of DMVs are server-scoped & database scoped whics requires obvious elevated privileges to execute on that SQL instance. A note to refer about their compatibility between the version that BOL highlights "...schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in SQL Server 2008.....". Further the DMVs & functions are divided into various categores such as:

Change Data Capture Related Dynamic Management Views

Query Notifications Related Dynamic Management Views

Common Language Runtime Related Dynamic Management Views

Replication Related Dynamic Management Views

Database Mirroring Related Dynamic Management Views

Resource Governor Dynamic Management Views

Database Related Dynamic Management Views

Service Broker Related Dynamic Management Views

Execution Related Dynamic Management Views and Functions

SQL Server Extended Events 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

I/O Related Dynamic Management Views and Functions

Security Related Dynamic Management Views

Object Related Dynamic Management Views and Functions

The next thought will come into mind that how many DMV/DMF are there in SQL Server, to get that information (see Pinal's post)

SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name


SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm[_]%' ORDER BY name

Further, the privileges that an user needs to run must have VIEW SERVER STATE permission to execute the DMVs, in addition to that if the user needs the DATABASE state of information then VIEW DATABASE STATE permission is also required. Otherwise DENY operation takes precedence, if a user has been granted VIEW SERVER STATE permissions but denied VIEW DATABASE STATE permission, the user can see server-level information, but not database-level information.

Finally here is the good resource to get the important DMVs that are useful in DBAs day-to-day troubleshooting fighting arsenal get SQL Server DMV starter pack from Redgate, and to get more comfort on your troubleshooting techniques you might download the evaluation copy of SQL Response that will alert the problems on SQL Server platform as soon as they arise. Also fellow MVP Glenn Berry has done excellent work on DMVs path as DMV a DAY to that I use everytime I get to site on a troubleshooting project, follow through the link for additional techniques for your DBA experience. Also the DMV related tags from this site.

 Follow SQLMaster on Twitter

Tags: best practices, dmv, dba, sql server, #SQL Server, BRH,

Satya Jayanty (@sqlmaster)
34 · 5% · 1720


Your Comment

Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]