Profile: 

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 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 changing server states - for more info on DMVS, see http://msdn2.microsoft.com/en-us/library/ms188754.aspx

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 http://blogs.msdn.com/psssql/archive/2007/03/30/sql-server-2005-performance-dashboard-reports.aspx 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 still 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. Analysis is provided by reporting services reports. For more info on the Waits and Queues methodology see http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

Please comment on the DMVstats tool - your comments & suggestions - good or bad, all are welcomed!

Regards,
Tom Davidson