Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

VII.1.2. Communicating with the Optimizer

If your goal is to design and maintain a high-performance database-driven application, you're wise to invest a little time to learn how to interpret some of the many details offered by the SQL Server Query Optimizer.

Chief among these aspects are query plans, which offer a comprehensive description of how SQL Server plans to satisfy your requests. In this section, we show you the kinds of information that you find in a query plan, as well as how you can use this intelligence as guidance toward improving performance. To keep things simple, we start with some basic illustrations of query plans and then explore examples that are more intricate.

First, using the SQL Server Management Studio we created a simple header table to hold details about orders. Initially, no indexes or other performance aids are in place on this table.

CREATE TABLE order_header
(
   order_id INTEGER NOT NULL,
   order_date DATETIME NOT NULL,
   order_total DECIMAL(5,2) NOT NULL,
   order_instructions VARCHAR(50)
)

Next, we filled it with 50,000 rows of data. With the table loaded, we can start evaluating some query plans.

There are several ways to view query plans, including in XML and plain-text format. However, in keeping with this book's philosophy of leveraging powerful graphical tools, such as the SQL Server Management Studio, we focus on showing you query plans via that avenue.

To view query plans in the SQL Server Management Studio, click the Display Estimated Execution ...