Database Workload Optimization - SQL Server Query Performance Tuning - page 504

Sample Workload
To troubleshoot SQL Server performance, you need to know the SQL workload that is executed on the server. You
can then analyze the workload to identify causes of poor performance and applicable optimization steps. Ideally, you
should capture the workload on the SQL Server facing the performance problems. In this chapter, you will use a set of
queries to simulate a sample workload so that you can follow the optimization steps listed in the previous section. The
sample workload you'll use consists of a combination of good and bad queries.

■ i recommend you restore a clean copy of the AdventureWorks2012 database so that any artifacts left over from
previous chapters are completely removed.

Note

The simple test workload is simulated by the following set of sample stored procedures; you execute them using
the second script on the AdventureWorks2012 database:

USE AdventureWorks2012;
GO

CREATE PROCEDURE dbo.ShoppingCart
@ShoppingCartId VARCHAR(50)
AS
--provides the output from the shopping cart including the line total
SELECT sci.Quantity,
p.ListPrice,
p.ListPrice * sci.Quantity AS LineTotal,
p.[Name]
FROM Sales.ShoppingCartItem AS sci
JOIN Production.Product AS p
ON sci.ProductID = p.ProductID
WHERE sci.ShoppingCartID = @ShoppingCartId;
GO

CREATE PROCEDURE dbo.ProductBySalesOrder @SalesOrderID INT
AS
/*provides a list of products from a particular sales order,
and provides line ordering by modified date but ordered
by product name*/

SELECT ROW_NUMBER() OVER (ORDER BY sod.ModifiedDate) AS LineNumber,
p.[Name],
sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
WHERE soh.SalesOrderID = @SalesOrderID
ORDER BY p.[Name] ASC;
GO