Database Reference
In-Depth Information
Prepared Workload
Prepared workloads
(or queries) explicitly parameterize the variable parts of the query so that the query plan isn't tied
to the value of the variable parts. In SQL Server, queries can be submitted as prepared workloads using the following
three methods:
•
Stored procedures
: Allows saving a collection of SQL statements that can accept and return
user-supplied parameters
Sp_executesql
: Allows executing a SQL statement or a SQL batch that may contain user-
supplied parameters, without saving the SQL statement or batch
•
•
Prepare/execute model
: Allows a SQL client to request the generation of a query plan that can
be reused during subsequent executions of the query with different parameter values, without
saving the SQL statements in SQL Server
For example, the
SELECT
statement shown previously can be explicitly parameterized using a stored procedure
as follows:
IF (SELECT OBJECT_ID('dbo.BasicSalesInfo')
) IS NOT NULL
DROP PROC dbo.BasicSalesInfo;
GO
CREATE PROC dbo.BasicSalesInfo
@ProductID INT,
@CustomerID INT
AS
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;
The plan of the
SELECT
statement included within the stored procedure will embed the parameters (
@ProductID
and
@Customerld
), not variable values. I will cover these methods in more detail shortly.
Plan Reusability of an Ad Hoc Workload
When a query is submitted as an ad hoc workload, SQL Server generates an execution plan and stores that plan in
the cache, where it can be reused if the same ad hoc query is resubmitted. Since there are no parameters, the
hard-coded values are stored as part of the plan. For a plan to be reused from the cache, the T-SQL must match
exactly. This includes all spaces and carriage returns plus any values supplied with the plan. If any of these change,
the plan cannot be reused.
To understand this, consider the ad hoc query you've used before:
SELECT soh.SalesOrderNumber,
soh.OrderDate,
sod.OrderQty,
sod.LineTotal
