Database Reference
In-Depth Information
Implement the Prepare/Execute Model to Avoid Resending a Query String
sp_executesql
requires the query string to be sent across the network every time the query is reexecuted. It also
requires the cost of a query string match at the server to identify the corresponding execution plan in the procedure
cache. In the case of an ODBC or OLEDB (or OLEDB .NET) application, you can use the prepare/execute model to
avoid resending the query string during multiple executions, since only the plan handle and parameters need to be
submitted. In the prepare/execute model, since a plan handle is returned to the application, the plan can be reused by
other user connections; it is not limited to the user who created the plan.
Avoid Ad Hoc Queries
Do not design new applications using ad hoc queries! The execution plan created for an ad hoc query cannot be
reused when the query is resubmitted with a different value for the variable parts. Even though SQL Server has the
simple parameterization and forced parameterization features to isolate the variable parts of the query, because of the
strict conservativeness of SQL Server in parameterization, the feature is limited to simple queries only. For better plan
reusability, submit the queries as prepared workloads.
There are systems built upon the concept of nothing but ad hoc queries. This is functional and can work within
SQL Server, but, as you've seen, it carries with it large amounts of additional overhead that you'll need to plan for.
Also, ad hoc queries are generally how SQL injection gets introduced to a system.
Prefer sp_executesql Over EXECUTE for Dynamic Queries
SQL query strings generated dynamically within stored procedures or a database application should be executed
using
spexecutesql
instead of the
EXECUTE
command. The
EXECUTE
command doesn't allow the variable parts of the
query to be explicitly parameterized.
To understand the preceding comparison between
sp_executesql
and
EXECUTE
, consider the dynamic SQL
query string used to execute the
SELECT
statement in
adhocsproc
.
DECLARE @n VARCHAR(3) = '776',
@sql VARCHAR(MAX);
SET @sql = 'SELECT * FROM Sales.SalesOrderDetail sod '
+ 'JOIN Sales.SalesOrderHeader soh '
+ 'ON sod.SalesOrderID=soh.SalesOrderID ' + 'WHERE sod.ProductID='''
+ @n + '''';
--Execute the dynamic query using EXECUTE statement
EXECUTE (@sql);
The
EXECUTE
statement submits the query along with the value of
d.ProductID
as an ad hoc query and thereby
may or may not result in simple parameterization. Check the output yourself by looking at the cache.
SELECT deqs.execution_count,
deqs.query_hash,
deqs.query_plan_hash,
dest.text,
deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp;
