Database Reference
In-Depth Information
FROM Purchasing.PurchaseOrderHeader AS poh
JOIN Purchasing.PurchaseOrderDetail AS pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID
JOIN Production.Product AS p
ON pod.ProductID = p.ProductID
JOIN HumanResources.Employee AS e
ON poh.EmployeeID = e.BusinessEntityID
JOIN Person.Person AS per
ON e.BusinessEntityID = per.BusinessEntityID
WHERE per.LastName LIKE @LastName AND
poh.VendorID = @VendorID
ORDER BY per.LastName,
per.FirstName;
GO
Then you have to modify the existing procedure so that it looks like this:
ALTER PROCEDURE dbo.PurchaseOrderBySalesPersonName
@LastName NVARCHAR(50),
@VendorID INT = NULL
AS
IF @VendorID IS NULL
BEGIN
EXEC dbo.PurchaseOrderByLastName @LastName
END
ELSE
BEGIN
EXEC dbo.PurchaseOrderByLastNameVendor @LastName, @VendorID
END
GO
With that in place, regardless of the code path chosen, the first time these queries are called, each procedure will
get its own unique execution plan, avoiding bad parameter sniffing. And, this won't negatively impact performance
time. If I run both the queries now the results are approximately the same.
Taking the performance from 1313ms to 56ms or 154ms is a pretty good reduction in execution time. If this query
were called hundreds of times in a minute, that level of reduction would be quite serious indeed. But, you should
always go back and assess the impact on the overall database workload.
Analyzing the Effect on Database Workload
Once you've optimized the worst-performing query, you must ensure that it doesn't hurt the performance of the other
queries; otherwise, your work will have been in vain.
To analyze the resultant performance of the overall workload, you need to use the techniques outlined in Chapter
15. For the purposes of this small test, reexecute the complete workload and capture extended events in order to
record the overall performance.
■
Tip
For proper comparison with the original extended events, please ensure that the graphical execution plan is off.
