Database Reference
In-Depth Information
Caching Execution Plans Effectively
You must ensure that the execution plans for your queries are not only cached but reused often. Do so by adopting the
following best practices:
•
Avoid executing queries as nonparameterized, ad hoc queries. Instead, parameterize the
variable parts of a query and submit the parameterized query using a stored procedure or the
spexecutesql
system stored procedure.
•
If you must use lots of ad hoc queries, enable the Optimize for Ad Hoc Workload option,
which will create a plan stub instead of a full plan the first time a query is called. This radically
reduces the amount of procedure cache used.
ANSI NULLS
) in every connection that executes
the same parameterized queries. This is important because the execution plan for a query is
dependent on the environment settings of the connection.
•
Use the same environment settings (such as
•
As explained earlier in the “Explicitly Define the Owner of an Object” section, explicitly qualify
the owner of the objects when accessing them in your queries.
The preceding aspects of plan caching are explained in detail in Chapter 16.
Minimizing Recompilation of Execution Plans
To minimize the cost of generating execution plans for queries, you must ensure that the plans in the cache are
not invalidated or recompiled for reasons that are under your control. The following recommended best practices
minimize the recompilation of stored procedure plans:
•
Do not interleave DDL and DML statements in your stored procedures. You should put all the
DDL statements at the top of the stored procedures.
•
In a stored procedure, avoid using temporary tables that are created outside the stored
procedure.
•
Prefer table variables over temporary tables for small data sets.
ANSI SET
options within a stored procedure.
•
Do not change the
•
If you really can't avoid a recompilation, then identify the stored procedure statement that
is causing the recompilation, and execute it through the
sp_execute_sql
system stored
procedure.
The causes of stored procedure recompilation and the recommended solutions are explained in detail
in Chapter 17.
Adopt Best Practices for Database Transactions
The more effectively you design your queries for concurrency, the faster the queries will be able to complete without
blocking one another. Consider the following recommendations while designing the transactions in your queries:
•
Keep the scope of the transactions as short as possible. In a transaction, include only the
statements that must be committed together for data consistency.
