X hits on this document

103 views

0 shares

0 downloads

0 comments

36 / 48

670

CHAPTER 28 PERFORMANCE TUNING

SELECT Produ Product Product Product Product Product Product Product Product FROM Produ INNER JOIN Production INNER JOIN Production INNER JOIN Production

ction.Product.Name, ion.Product.ProductNumber, ion.ProductModel.Name AS ModelName, ion.ProductInventory.LocationID, ion.ProductInventory.Shelf, ion.ProductInventory.Bin, ion.ProductCostHistory.StartDate, ion.ProductCostHistory.EndDate, ion.ProductCostHistory.StandardCost ction.Product Production.ProductCostHistory ON .Product.ProductID = Production.ProductCostHistory.ProductID Production.ProductInventory ON .Product.ProductID = Production.ProductInventory.ProductID Production.ProductModel ON .Product.ProductModelID = Production.ProductModel.ProductModelID

WHERE Production.ProductInventory.LocationID IN (60) ORDER BY Production.Product.Name

For this recipe, the query is saved as a file to C:\Apress\ProductsByLocation.sql where it will be evaluated later on.

First, however, Figure 28-16 shows the graphical execution plan of this query. For larger result sets, one common goal for index tuning is to reduce the number of scans in favor of seeks. As you can see from this query’s execution plan, some tables use index scan operations while only one table (the ProductCostHistory table) uses an index seek.

Figure 28-16. Execution plan for the Products by Location query

You’ll use the Database Engine Tuning Advisor to evaluate the query and make recommen- dations to improve its performance. The Database Engine Tuning Advisor is launched by going to StartProgramsMicrosoft SQL Server 2005Performance ToolsDatabase Engine Tuning Advisor.

You’ll be prompted for the Server name and authentication method in the Connect to Server dialog box (see Figure 28-17). After selecting these options, click the Connect button.

Document info
Document views103
Page views103
Page last viewedTue Dec 06 13:30:24 UTC 2016
Pages48
Paragraphs987
Words15822

Comments