Execution Plan Cache Behavior - SQL Server Query Performance Tuning - page 294

SET @SQL = 'UPDATE dbo.RestrictedAccess
SET Status = ''Deleted''
WHERE ID = ' + @ID;
EXEC sys.sp_executesql @SQL;
GO
GRANT EXECUTE ON dbo.MarkDeleted TO UserOne;
Consequently, user UserOne won't be able to mark the row as 'Deleted' using the stored procedure MarkDeleted .
(I cover the aspects of using a dynamic query in the stored procedure in the next chapter.)
Since stored procedures are saved as database objects, they add maintenance overhead to the database
administration. Many times, you may need to execute just one or a few queries from the application. If these singleton
queries are executed frequently, you should aim to reuse their execution plans to improve performance. But creating
stored procedures for these individual singleton queries adds a large number of stored procedures to the database,
increasing the database administrative overhead significantly. To avoid the maintenance overhead of using stored
procedures and yet derive the benefit of plan reuse, submit the singleton queries as a prepared workload using the
sp_executesql system stored procedure.
sp_executesql
sp_executesql is a system stored procedure that provides a mechanism to submit one or more queries as a prepared
workload. It allows the variable parts of the query to be explicitly parameterized, and it can therefore provide
execution plan reusability as effective as a stored procedure. The SELECT statement from BasicSalesInfo can be
submitted through sp_ executesql as follows:
DECLARE @query NVARCHAR(MAX),
@paramlist NVARCHAR(MAX);
SET @query = N'SELECT soh.SalesOrderNumber ,soh.OrderDate ,sod.OrderQty ,sod.LineTotal FROM
Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID WHERE
soh.CustomerID = @CustomerID
AND sod.ProductID = @ProductID';
SET @paramlist = N'@CustomerID INT, @ProductID INT';
EXEC sp_executesql @query,@paramlist,@CustomerID = 29690,@ProductID = 711;
Note that the strings passed to the sp_executesql stored procedure are declared as NVARCHAR and that they are
built with a prefix of N . This is required since sp_executesql uses Unicode strings as the input parameters.
The output of sys.dm_exec_cached_plans is shown next (see Figure 15-18 ).
SELECT c.usecounts,
c.cacheobjtype,
c.objtype,
t.text
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) t
WHERE text LIKE '(@CustomerID%';