Dynamic Management Views and Functions in SQL Server 2005

By: Jeremy Kadlec   |   Updated: 2006-12-11   |   Comments   |   Related: > 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:

• sys.dm_clr_loaded_assemblies - Assemblies in available in SQL Server
• sys.dm_db_file_space_usage - Database file usage to determine if databases are getting low on space and need immediate attention
•  sys.dm_exec_cached_plans - Cached query plans available to SQL Server-
•  sys.dm_exec_sessions - Sessions in SQL Server-
•  sys.dm_exec_connections - Connections to SQL Server-
•  sys.dm_db_index_usage_stats - Seeks, scans, lookups per index-
•  sys.dm_io_virtual_file_stats - IO statistics for databases and log files-
•  sys.dm_broker_connections - Service Broker connections to the network-
•  sys.dm_os_memory_objects - SQL Server memory usage -
•  sys.dm_tran_active_transactions - Transaction state for an instance of SQL Server-

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

Next Steps

• With the introduction of the DMV's, consider these objects as new tools in your tool box to other tools such as Profiler, Perfmon or custom scripts.
• As you begin to troubleshoot and monitor SQL Server 2005, consider the Dynamic Management Views and Functions are a new means to capture real time performance metrics.
• If you have had historical performance problems with particular portions of your application, review the DMV's to determine if these objects can provide you with new insight to resolve the issue.
• Stay tuned for additional tips on the dynamic management views from MSSQLTips.com.  For now, check out these tips:
  • System Monitor (Perfmon) Counters for SQL Server 2005
  • SQL Server 2000 to 2005 Crosswalk = Waitstats performance metrics
  • Snapshot Isolation in SQL Server 2005
  • SQL Server 2000 to 2005 Crosswalk SQL Server User Defined Objects
  • SQL Server 2000 to 2005 Crosswalk Database Fragmentation
  • Sources for Database Information SQL Server 2000 to 2005 Crosswalk
  • Cross Walk SQL Server Query Plans
  • Server Info = SQL Server 2000 to 2005 Crosswalk

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips