In
my previous post, we have gone through about the basic of SQL Server Hints. Now
here we are moving with type of Query Hints.
There are
various types of Query Hints. Some of the important and useful for day to day
basis are as below:-
- FAST number_rows:- Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.
- FORCE ORDER:-Normally, the optimizer will determine the order in which the joins occur but using the FORCE ORDER hint you can make the optimizer use the order of joins as listed in the query itself. This would be done if you've got a fairly high degree of certainty that your join order is better than that supplied by the optimizer. The optimizer can make incorrect choices when the statistics are not up to date, when the data distribution is less than optimal or if the query has a high degree of complexity.
- KEEP PLAN:- Forces the query optimizer to relax the estimated recompile threshold for a query. The estimated recompile threshold is the point at which a query is automatically recompiled when the estimated number of indexed column changes have been made to a table by running UPDATE, DELETE, MERGE, or INSERT statements. Specifying KEEP PLAN makes sure that a query will not be recompiled as frequently when there are multiple updates to a table.
- KEEPFIXED PLAN:- It eliminates any recompile due to changes in statistics.
- MAXRECURSION number:- The MAXRECURSION hint places an upper limit on the number of recursions within a query.Valid values are between 0 and 32,767. Setting the value to zero allows for infinite recursion. The default number of recursions is 100. When the number is reached, an error is returned and the recursive loop is exited. This will cause any open transactions to be rolled back. Using the option doesn't change the execution plan but, because of the error, an actual execution plan might not be returned.
- RECOMPILE:- Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed. Without specifying RECOMPILE, the Database Engine caches query plans and reuses them.
No comments:
Post a Comment