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

VII.1.3. Helping Your Optimizer Help You

The SQL Server Query Optimizer is quite adept at using all available resources to execute your database tasks as quickly and efficiently as possible. However, you can take specific steps to make the Optimizer more productive, which translates into better performance for you and your users.

VII.1.3.1. Create effective indexes

In the absence of a well-thought-out indexing strategy, the Query Optimizer can do only so much: Table scans and other sub-optimal accommodations will always be time-consuming and sluggish. Remember this fact when you design and configure your SQL Server database.

VII.1.3.2. Write well-designed queries

Even if you've created effective indexes, unfortunately, you're still free to create poorly conceived queries that might prove too challenging for an efficient query execution plan. To combat this undesirable outcome, Chapter 3 of this mini-book furnishes you with some tips to make your queries run more quickly.

VII.1.3.3. Enable and maintain statistical information

To pick the optimal query execution plan, SQL Server employs statistical details about all the tables that are participating in the query. When you define an index, SQL Server automatically creates statistics for any columns present in that index.

To get an idea of what SQL Server tracks about your data, Figure 1-6 shows the output of the DBCC SHOW_STATISTICS command.

Figure VII.1-6. Results from the DBCC SHOW_STATISTICS command.

These metrics were generated ...