Professional SQL Server® 2005 Performance Tuning

14.4. Benchmarking for Evaluation

Earlier in the chapter, we encouraged the creation of a set of benchmark tables to store some metadata about the nature of the database objects. You categorized tables in a database according to their functional purposes and stitched them together with high impact use-cases to be able to talk back to business owners about database issues in their terms. In SQL Server 2000, you could not get object-level usage statistics. In this section, you'll use the DMVs exposed in SQL Server 2005 to add to this metadata the statistical information that you'll need to be able to refer to when deciding whether the database is meeting the performance requirement expectations.

14.4.1. Creating the Benchmark Schema

Earlier we discussed capturing information in a meta-data format for expected performance requirements like persona and use cases. These data are useful when you can connect these user-concepts to real metrics from the database objects themselves. As part of this chapter, we've created a model to allow the capturing of database statistics. To create these tables download the script from www.wrox.com. The scripts will create the tables and populate the type table information to match the discussions earlier in the performance requirements section of this chapter.

14.4.2. Extracting Object_Id-Level Information from DMVs

After working with the new DMVs in SQL Server 2005, we were blown away by the amount of information that was available. This awe stopped ...