Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Query Hints and Options
As we mentioned in earlier articles, SQL Server has an internal optimizer that comes up with the least expensive (in terms of CPU and input/output) execution plan for each query. Generally the optimizer makes the best decision; however, at times it can use a little help from its friends—namely, you. If you feel like you can outsmart (or help) the optimizer, feel free to use query hints and options. Just be sure to test query execution thoroughly with and without the hints and advanced options prior to deploying to the production environment.
We've discussed index and locking hints in other articles. The following table shows several other hints and advanced options supported in SQL Server 2000 that we've found useful in our optimization efforts.
|
Hint/Option |
Type |
Functionality |
|
LOOP |
JOIN hint |
Forces use of a LOOP algorithm. |
|
HASH |
JOIN hint |
Forces use of a HASH algorithm. |
|
MERGE |
JOIN hint |
Forces use of a MERGE algorithm. |
|
MAXDOP number |
Option |
Specifies the maximum degree of parallelism for the particular query, overriding the server-wide setting. This can be useful if one of your processors is slow and you'd rather execute the query on a single fast processor. |
|
FAST number of rows |
Option |
Optimizes the query to bring the first N rows fast. After that, the query executes as it would without the hint. This option could be useful if you'd like to give the users a perception that the query is executing quickly, by populating the first screen quickly. You could provide a link to the rest of the screens, which will be populated after users are done reading the first set of rows. |
|
FORCE ORDER |
Option |
Forces SQL Server to join tables in the order in which they appear in the query. Occasionally you'll see a better performance by joining tables A and B first and then joining to table C, instead of joining tables A and C and joining results to B. |