Database Reference
In-Depth Information
Here you'll see that the
Actual Number of Rows
and
Estimated Number of Rows
values are the same: 4,012.
From these two measures, you can see that the estimated rows for the execution steps of the first query (using a local
variable in the
WHERE
clause) is way off the actual number of rows returned by the steps. Consequently, the execution
plan cost for the first query, which is based on the estimated rows, is somewhat misleading. The incorrect estimation
misguides the optimizer and causes some variations in how the query is executed. You can see this in the return times
on the query, even though the number of rows returned is identical.
Any time you find such an anomaly between the relative execution plan cost and the
STATISTICS
output for the
queries under analysis, you should verify the basis of the estimation. If the underlying facts (estimated rows) of the
execution plan itself are wrong, then it is quite likely that the cost represented in the execution plan will also be wrong.
But since the output of the various
STATISTICS
measurements shows the actual number of logical reads and the
real elapsed time required to perform the query without being affected by the initial estimation, you can rely on the
STATISTICS
output.
Now let's return to the actual performance issue associated with using local variables in the
WHERE
clause. As
shown in the preceding example, using the local variable as the filter criterion in the
WHERE
clause of a batch query
doesn't allow the optimizer to determine the right indexing strategy. This happens because, during the optimization
of the queries in the batch, the optimizer doesn't know the value of the variable used in the
WHERE
clause and can't
determine the right access strategy—it knows the value of the variable only during execution. Effectively this means
the optimizer had to use the density vector instead of looking up information through the histogram in the statistics.
To avoid this particular performance problem, use one of the following approaches. Don't use a local variable as
a filter criterion in a batch for a query like this. A local variable is different from a parameter value, as demonstrated in
Chapter 16. Create a stored procedure for the batch and execute it as follows:
CREATE PROCEDURE spProductDetails (@id INT)
AS
SELECT pod.*
FROM Purchasing.PurchaseOrderDetail AS pod
JOIN Purchasing.PurchaseOrderHeader AS poh
ON poh.PurchaseOrderID = pod.PurchaseOrderID
WHERE poh.PurchaseOrderID >= @id;
GO
EXEC spProductDetails
@id = 1;
The optimizer generates the same execution plan as the query that doesn't use a local variable for the ideal case.
Correspondingly, the execution time is also reduced. In the case of a stored procedure, the optimizer generates the
execution plan during the first execution of the stored procedure and uses the parameter value supplied to determine
the right processing strategy.
This approach can backfire. The process of using the values passed to a parameter is referred to as
parameter
sniffing
. Parameter sniffing occurs for all stored procedures and parameterized queries automatically. Depending on
the accuracy of the statistics and the values passed to the parameters, it is possible to get a bad plan using specific
values and a good plan using the sampled values that occur when you have a local variable. Testing is the only way to
be sure which will work best in any given situation. However, in most circumstances, you're better off having accurate
values rather than sampled ones. For more details on parameter sniffing, see Chapter 16.
Be Careful When Naming Stored Procedures
The name of a stored procedure does matter. You should not name your procedures with a prefix of
sp_
. Developers
often prefix their stored procedures with
sp_
so that they can easily identify the stored procedures. However, SQL
Server assumes that any stored procedure with this exact prefix is probably a system stored procedure, whose home is
