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:
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 ...