Professional SQL Server® 2005 Performance Tuning

9.2. Middle Game: Gathering the Facts

At this point you need to gather information about the queries you need to tune. The major component of this involves generating and interpreting query plans. Once you've gathered the facts, and understand how SQL Server is handling the queries, you'll be able to move to the last stage—the end game.

9.2.1. Query Plans

Some DBAs start tuning an offending query by running the query through the Database Tuning Advisor. However, a better approach is to start by generating an execution plan. Once you've done that you should be able to find specific places to start.

There are two types of query plans you can generate. One is a graphical plan, and the other is text-based. Both types are useful.

Figure 9-2 is a typical graphical query plan. In order to generate this you can do this a couple of ways. The easiest is to press Ctrl-L in the Microsoft SQL Server Management Studio. Another option is to choose Display Estimated Query Plan from the Query menu option.

Figure 9-2. Figure 9-2

Finally, you can click the Include Actual Query Plan button on the toolbar (see Figure 9-3).

Figure 9-3. Figure 9-3

Figure 9-4 shows a typical query plan.

Figure 9-4. Figure 9-4

Note that execution plans cannot be generated for encrypted stored procedures, triggers, ...