Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Dynamic Management Views and Functions in SQL Server 2005

By:   |   Last Updated: 2006-12-11   |   Comments   |   Related Tips: More > Dynamic Management Views and Functions


With SQL Server 2000 it was difficult to capture real time statistics on many of the core database engine features without issuing DBCC commands, running Profiler or scheduling the execution of custom scripts.  With add-on features such as Full Text Search it was difficult to capture metrics on portions of the application and troubleshooting performance was less than efficient.  From SQL Server 2000 to 2005, the number of add-on features has grown and troubleshooting overall SQL Server performance has the potential to be even more of an issue. 


Luckily, SQL Server 2005 has shipped with a number of Dynamic Management Views and Functions that are able to provide DBAs and Developers with the ability to capture real time metrics for many of the core engine components. Many of these DMV's have replaced previous system stored procedures, system tables or provide insight into processes that were previously not available via public interfaces.  Below outlines 10 sample DMV's of the 70+ that ship with SQL Server 2005 to get a feel for the type of information that is available:

For a complete listing of the DMV's, reference this article Dynamic Management Views and Functions.

Next Steps

Last Updated: 2006-12-11

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools