Pulling back the covers on SQL Server

Tuning SQL Server has always been a black art.  Luckily, SQL Server 2005 added a number of tools to give us a glimpse into its inner workings through Dynamic Management Views (DMV). In the MSDN article, SQL Server: Uncover Hidden Data to Optimize Application Performance, Ian Stirk provides an excellent overview of the various DMVs.  "SQL Server 2005 collects data relating to running queries. This data, which is held in memory and starts accumulating after a server restart, can be used to identify numerous issues and metrics, including those surrounding table indexes, query performance, and server I/O. You can query this data via the SQL Server Dynamic Management Views (DMVs) and related Dynamic Management Functions (DMFs)."  More...

Using DMVs directly is great, but various tools have been built on top of them to take your tuning experience to the next level.  Microsoft SQL Server Customer Advisory Best Practices Team has published DMVStats: A SQL Server 2005 Dynamic Management View Performance Data Warehouse on CodePlex.  We're taking a look at this tool over the next couple of weeks.  If anyone else has used it, I'd love to hear your results.

No Comments