Monitoring Query Performance

Before you think about taking some action to make a query faster, such as adding an index or denormalizing, you should understand how a query is currently being processed. You should also get some baseline performance measurements so you can compare behavior both before and after making your changes. SQL Server provides these tools (SET options) for monitoring queries:

  • STATISTICS IO

  • STATISTICS TIME

  • STATISTICS PROFILE

  • STATISTICS XML

You enable any of these SET options before you run a query, and they will produce additional output. STATISTICS PROFILE and STATISTICS XML were described in Chapter 3; STATISTICS IO and STATISTICS TIME will be explained below. Typically, you run your query with these options set in a tool ...

Get Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.