Database Reference
In-Depth Information
Figure 25-1.
Extended Events session output showing the SQL workload
The worst-performing query in terms of duration is also one of the worst in terms of CPU usage and reads. That
procedure,
dbo.PurchaseOrderBySalesPersonName
, is highlighted in Figure
25-1
(you may have different values, but
this query is likely to be the worst-performing query or at least one of the worst). The query inside that procedure is
presented here for easy reference:
SELECT poh.PurchaseOrderID,
poh.OrderDate,
pod.LineTotal,
p.[Name] AS ProductName,
e.JobTitle,
per.LastName + ', ' + per.FirstName AS SalesPerson,
poh.VendorID
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 = COALESCE(@VendorID, poh.VendorID)
ORDER BY per.LastName,
per.FirstName;
Another method open to you if you can't run Extended Events is to use the
sys.dm_exec_query_stats
DMO. This
will provide you with aggregate information about all the queries currently in cache. It's a fast way to identify the most
frequently called, longest-running, and most resource-intensive procedures. It brings along the added benefit of being
able to quickly join to other DMOs to pull out the execution plan and other interesting information.
Once you've identified the worst-performing query, the next optimization step is to determine the resources
consumed by the query.
Determining the Baseline Resource Use of the Costliest Query
The current resource use of the worst-performing query can be considered as a baseline figure before you apply any
optimization techniques. You may apply different optimization techniques to the query, and you can compare the
resultant resource use of the query with the baseline figure to determine the effectiveness of a given optimization
technique. The resource use of a query can be presented in two categories:
•
Overall resource use
•
Detailed resource use
