Coding Powers

SQL 2005 Performance Tuning using DMVs - Part 1of 7

A few months ago, I researched and put together an internal presentation on one of the more interesting features in SQL 2005 (Dynamic Management Views).  This topic is not quite as cutting edge now as it was when I researched it.  But, I think it is still useful to blog what I found.  First off, thanks to Kim Tripp at SQL at www.sqlskills.com.  I first became interested in this topic when watching some of her webcasts.  I also found many of my good samples of DMV useage from some of her posts.

I am going to break this up into parts, this first part is just an intro. 

Microsoft made a big effort with SQL Server 2005 to make the server more transparent to administrators and developers. The result is DMVs, which should make database tuning and maintenance easier.

“DMVs can expedite the diagnosis process by eliminating the need to generate and analyze physical dumps in most cases. DMVs provide a simplified and familiar relational interface for getting critical system information. This information can be used for monitoring purposes to alert administrators to any potential problems. Or, the information can be polled and collected periodically for detailed analysis later.”

DMVs are very useful in diagnosing different SQL performance problems.  I will go into more specifics with the future posts here.  But in general, you will find great resources for looking into any of the following:

• Resource Bottlenecks
  • CPU
  • Memory
  • I/O
• TempDB Bottlenecks
• Slow Queries
  • Bad Plans
  • Missing Indexes
  • Blocking
• Index Fragmentation

Here is a good list of the Naming Conventions of the DMVs that are available:

• CLR Related
  • dm_clr_
• Database Related
  • dm_db_
• Execution Related
  • dm_exec_
• Full Text Search Related
  • dm_fts_
• Index Related
  • dm_db_index_
  • dm_missing_index_
• SQL OS Related
  • dm_os_
• I/O Related
  • dm_io_
• Query Notifications Related
  • dm_qn_
• Replication Related
  • dm_repl_
• Service Broker Related
  • dm_broker_
• Transaction Related
  • dm_tran_

After playing around with all the DMVs I could find, I put together a list of my 10 favorites:

1. dm_exec_query_stats – performance stats of all queries
2. dm_exec_sql_text – returns exact sql for entire batch given a sql handle
3. dm_exec_query_plan – return query plan for entire batch given a plan handle
4. dm_db_index_usage_stats – displays how often each index is used, show Read Seeks, Read Scans, Write Seeks and Write Scans
5. dm_db_index_physical_stats – replace DBCC SHOWCONTIG used to check index fragmentation
6. dm_db_index_operational_stats – use to check for index contention and blocking
7. dm_os_wait_stats – Used to determine the types of waits (signal or resource that are occurring)
8. dm_os_waiting_tasks – Used to help identify blocking
9. dm_tran_locks – Used to help indentify blocking
10. dm_os_performance_counters – Query Access to SQL Server Performance counters

The last thing I wanted to cover in this initial post, was just methods for writing queries to identify the DMVs and their types.  Some DMVs are actual views, while others are tabled valued functions.  Again, thanks to Kim Tripp for most of this knowledge.

Download a script for identifying DMVs and syntax to querying each here