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

III.2.1. Why You Need the Optimizer

The SQL Server Optimizer (also called the Query Optimizer) has undergone several improvements through the years. In the days of SQL Server 6.5, a database administrator needed to tweak queries constantly by providing SQL Server an endless assortment of hints, such as which index to use or which type of join to use.

Although using hints helped the database management system (DBMS) perform better, they were a lot of work. The database administrator needed to fully understand the data and the database activity. In a dynamic database, the queries often had to be monitored while the data changed to determine whether the hints needed to be changed.

Currently, the Optimizer can analyze queries and quickly create a query plan without hints. Although hints can still be used, their use is generally discouraged except in the most advanced applications. The Optimizer automatically analyzes many aspects of the queries to create the query plan. Additionally, because the Optimizer optimizes queries when they're executed, the query plan can change when the data changes.

For example, you might want to optimize a Sales database query that pulls data from the Orders, OrderDetails, and Products tables. By analyzing the execution plan, you could determine the best indexes to use and provide hints in the query. Over time, the data changes, the indexes change, and the statistics for the indexes change. Therefore, instead of an optimal query, you have a very slow-running ...