Databases Reference
In-Depth Information
In fact, given the level of impact which query parameters can have on query performance,
the parameter sniffing behavior (as well as several other parameter-related topics) will be
discussed in plenty of detail in
Chapter 6
,
Additional Topics
.
Even when an execution plan is available in the plan cache, some metadata changes, such
as the removal of an index or a constraint, or significant enough changes made to the
contents of the database, may render an existing plan invalid or suboptimal, and thus
cause it to be discarded from the plan cache and a new optimization to be generated. As
a trivial example, removing an index will make a plan invalid if the index is used by that
plan. Likewise, the creation of a new index could make a plan suboptimal, if this index
could be used to create a more efficient alternative plan; and enough changes to the
database contents may trigger an automatic update of statistics, with the same effect on
the existing plan.
Plans may also be removed from the plan cache when SQL Server is under memory
pressure or when certain statements are executed. Changing some configuration options,
for example,
max degree of parallelism
, will clear the entire plan cache. Alternatively,
some statements, like altering a database with certain
ALTER
DATABASE
options will
clear all the plans associated with that particular database.
Hinting
Most of the time, the Query Optimizer does a great job of choosing highly efficient
execution plans. However, there may be cases when the selected execution plan does
not perform as expected. It is vitally important to differentiate between the occasions
when these cases arise because you are not providing the Query Optimizer with all the
information it needs to do a good job, and the occasions when the problem arises because
of a Query Optimizer limitation. As mentioned earlier, one of the purposes of this topic is
to give you the knowledge and tools, both to write better queries, and to troubleshoot the
cases when the Query Optimizer is not giving you a good plan, and your queries are not
performing well.
