Database Reference
In-Depth Information
Execution Plan Cache Recommendations
The basic purpose of the plan cache is to improve performance by reusing execution plans. Thus, it is important to
ensure that your execution plans actually are reusable. Since the plan reusability of ad hoc queries is inefficient, it is
generally recommended that you rely on prepared workload techniques as much as possible. To ensure efficient use
of the plan cache, follow these recommendations:
•
Explicitly parameterize variable parts of a query.
•
Use stored procedures to implement business functionality.
sp_executesql
to avoid stored procedure maintenance.
•
Use
•
Use the prepare/execute model to avoid resending a query string.
•
Avoid ad hoc queries.
sp_executesql
over
EXECUTE
for dynamic queries.
•
Use
•
Parameterize variable parts of queries with care.
•
Avoid modifying environment settings between connections.
•
Avoid the implicit resolution of objects in queries.
Let's take a closer look at these points.
Explicitly Parameterize Variable Parts of a Query
A query is often run several times, with the only difference between each run being that there are different values for
the variable parts. Their plans can be reused, however, if the static and variable parts of the query can be separated.
Although SQL Server has a simple parameterization feature and a forced parameterization feature, they have severe
limitations. Always perform parameterization explicitly using the standard prepared workload techniques.
Create Stored Procedures to Implement Business Functionality
If you have explicitly parameterized your query, then placing it in a stored procedure brings the best reusability
possible. Since only the parameters need to be sent along with the stored procedure name, network traffic is reduced.
Since stored procedures are reused from the cache, they can run faster than ad hoc queries.
Like anything else, it is possible to have too much of a good thing. There are business processes that belong in the
database, but there are also business processes that should never be placed within the database.
Code with sp_executesql to Avoid Stored Procedure Maintenance
If the object maintenance required for the stored procedures becomes a consideration or you are using queries
generated on the client side, then use
sp_executesql
to submit the queries as prepared workloads. Unlike the stored
procedure model,
sp_executesql
doesn't create any persistent objects in the database.
sp_executesql
is suited to
execute a singleton query or a small batch query.
The complete business logic implemented in a stored procedure can also be submitted with
sp_executesql
as a large query string. However, as the complexity of the business logic increases, it becomes difficult to create and
maintain a query string for the complete logic.
Also, using sp
sp_executesql
and stored procedures with appropriate parameters prevents SQL injection attacks
on the server.
