Database Reference
In-Depth Information
■
Note
We will talk about plan cache memory management later in this chapter.
SQL Server recompiles queries when it suspects that currently cached plans are no longer valid. One such case
is the schema changes of the objects referenced by the plan. It includes the creation or dropping of columns, indexes,
constraints, triggers, and statistics defined in a table.
Another case relates to stale statistics. SQL Server checks to see if the statistics are outdated when it looks up a
plan from the cache, and it recompiles the query if they are. That recompilation, in turn, triggers a statistics update.
Temporary tables can increase the number of recompilations triggered by outdated statistics. As you will
remember, SQL Server outdates statistics based on the number of modifications of the statistics (and index) columns.
For regular tables, statistics update thresholds are as follows:
When a table is empty, SQL Server outdates statistics when you add the data to the
empty table.
When a table has less than 500 rows, SQL Server outdates statistics after every
500 changes to the statistics columns.
When a table has 500 or more rows, SQL Server outdates statistics after every
500 + (20% of the total number of rows in the table) changes to the statistics columns.
However, for temporary tables, there is another threshold value of six changes, which can lead to unnecessary
recompilations in some cases. The
KEEP PLAN
query hint eliminates that threshold, and it makes the behavior of the
temporary tables the same as the regular ones.
Another query hint,
KEEPFIXED PLAN
, prevents query recompilation in the case of outdated statistics. Queries
would be recompiled only when the schema of the underlying tables are changed or the recompilation is forced,
for example when a stored procedure is called using the
WITH RECOMPILE
clause.
Plan Cache can store multiple plans for the same queries, batches, or T-SQL objects. Some of the
SET
options,
such as
ANSI_NULL_DLFT_OFF
,
ANSI_NULL_DLFT_ON
,
ANSI_NULL
,
ANSI_PADDING
,
ANSI_WARNING
,
ARITHABORT
,
CONCAT_NULL_YELDS_NULL
,
DATEFIRST
,
DATEFORMAT
,
FORCEPLAN
,
DATEFORMAT
,
LANGUAGE
,
NO_BROWSETABLE
,
NUMERIC_ROUNDABORT
, and
QUOTED_IDENTIFIER
affect plan reuse. Plans generated with one set of
SET
options cannot
be reused by sessions that use a different set of
SET
options.
Unfortunately, different client libraries and development environments have different default
SET
options.
For example, by default
ARITHABORT
is OFF in ADO.Net and
ON
in Management Studio. Remember this when you
troubleshoot inefficient queries submitted by client applications. You can get different execution plans when you run
those queries in Management Studio.
■
You can change the default
SET
options for queries running in Management Studio to match the client
applications in the
Options
menu item in
Tools
menu.
Tip
When your database works with multiple client applications developed in different languages, .net and Java for example,
consider specifying
SET
options in the same way at the session level after establishing the connection to SQL Server.
Another common reason for duplicated plans in cache is using unqualified object names without specifying
the object's schema. In that case, SQL Server resolves objects based on the default schema of database users and,
therefore, statements like
SELECT * FROM Orders
could reference completely different tables for different users,
which prevents plan reuse. Alternatively,
SELECT * FROM Sales.Orders
always references the same table regardless
of the default database schema for the user.
