Performance Analysis Tool - DMVStats

  • Article

Tom Davidson wrote:

I've been working with database applications for the last 25 years. A particular area of interest for me is SQL Server performance. As part of the Microsoft SQL Server Development Customer Advisory Team (SQLCAT) for the last 7 years, I've had an opportunity to observe what works (and more importantly what doesn't !) in the enterprise space. One of the key enterprise needs prior to SQL Server 2005, was an effective mechanism for troubleshooting and performance tuning for many common user scenarios. SQL Server Development along with SQLCAT & PSS collaborated on defining these common scenarios as well as a mechanism for troubleshooting and performance tuning that ultimately resulted in the SQL Server 2005 feature called Dynamic Management Views (DMVs). DMVs provide a consistent and transparent interface to view changing server states.

SQL Server 2005 SP2 does include a helpful Performance Dashboard comprised of a series of Reporting Services reports to identify some performance issues using query statistics such as execution counts, IOs, worker time, and query plans - courtesy of Microsoft's stellar PSS SQL escalation team - Keith Elmore, Bob Ward, Bob Dorr et al. (see Performance Dashboard for more info). While SQL Server 2005 provides all this great new DMV plumbing, there are a whole series of common user scenarios that are difficult to analyze and pinpoint. Examples include:

  1. what happened yesterday or last week? e.g. historical forensics,
  2. trending,
  3. source of obscure or transient waits (*not included in query stats),
  4. comparisons before and after application changes

Over the last year, I've been working on such a tool called DMVstats with some of my CAT colleagues . DMVstats collects performance oriented DMVs into a data warehouse, and provides a methodology called 'Waits' and 'Queues' to identify and track down performance issues.   Drill-through analysis is provided by reporting services reports.
DMVstats can be downloaded at https://www.codeplex.com/sqldmvstats.  Please comment on the DMVstats tool - your comments & suggestions - good or bad, all are welcomed!