Dynamic Management Views and Functions in SQL Server 2005


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

Problem

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. 

Solution

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 Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips





Comments For This Article





download


Recommended Reading

How to Find Keywords in SQL Server Stored Procedures and Functions

Determine SQL Server memory use by database and object

Using SQL Server DMVs to Identify Missing Indexes

Finding a SQL Server process percentage complete with DMVs

Retrieve Actively Running T-SQL Statements from SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools