Dynamic Management Views and Functions in SQL Server 2005

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

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

Related Tips: More | Become a paid author

Last Update: 12/11/2006

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!" It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. Need SQL Server help and not sure where to turn? Reach out to the Edgewood experts for a Health Check starting at $995. Get SQL Server Tips Straight from Kevin Kline. Join the over million SQL Server Professionals who get their issues resolved daily. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...