X hits on this document

95 views

0 shares

0 downloads

0 comments

17 / 48

CHAPTER 28 PERFORMANCE TUNING

Each command is enabled when set to ON, and disabled when set to OFF. This recipe’s example demonstrates returning the estimated query execution plan of a query in the AdventureWorks database using SET SHOWPLAN_TEXT and then SET SHOWPLAN_XML (SET SHOWPLAN_ALL is not demonstrated in this book because it returns seventeen columns and multiple rows which are not easily presented in print):

SET SHOWPLAN_TEXT ON GO

SELECT p.Name, p.ProductNumber, r.ReviewerName FROM Production.Product p INNER JOIN Production.ProductReview r ON

p.ProductID = r.ProductID WHERE r.Rating > 2 GO

SET SHOWPLAN TEXT OFF GO _

This returns the following estimated query execution plan output:

StmtText -----------------

-----------------------------------------------

SELECT p.Name, p.ProductNumber, r.ReviewerName FROM Production.Product p INNER JOIN Production.ProductReview r ON

p.ProductID = r.ProductID WHERE r.Rating > 2

(1 row(s) affected)

StmtText -----------------------------------------------

|--Nested Loops(Inner Join, OUTER REFERENCES:([r].[ProductID])) |--Clustered Index Scan(OBJECT:([AdventureWorks].[Production].[ProductReview].[PK_ProductReview_Product ReviewID] AS [r]), WHERE:([AdventureWorks].[Production].[ProductReview].[Rating] as [r].[Rating]>(2)))

|--Clustered Index Seek(OBJECT:([AdventureWorks].[Production].[Product].[PK_Product_ProductID] AS [p]), SEEK:([p].[ProductID]=[AdventureWorks].[Production].[ProductReview].[ProductID] as [r].[ProductID]) ORDERED FORWARD)

(3 row(s) affected)

The next example returns estimated query plan results in XML format:

SET SHOWPLAN_XML ON GO

SELECT p.Name, p.ProductNumber, r.ReviewerName FROM Production.Product p INNER JOIN Production.ProductReview r ON

p.ProductID = r.ProductID WHERE r.Rating > 2 GO

651

Document info
Document views95
Page views95
Page last viewedSat Dec 03 21:52:24 UTC 2016
Pages48
Paragraphs987
Words15822

Comments