X hits on this document

116 views

0 shares

0 downloads

0 comments

21 / 48

CHAPTER 28 PERFORMANCE TUNING

</DefinedValue> </DefinedValues> <Object Database="[AdventureWorks]" Schema="[HumanResources]" Table="[EmployeePayHistory]" I n d e x = " [ P K _ E m p l o y e e P a y H i s t o r y _ E m p l o y e e I D _ R a t e C h a n g e D a t e ] " / </IndexScan> </RelOp> </TopSort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>' >

How It Works

USE PLAN allows you to capture the XML format of a query’s execution plan and then force the query to use it on subsequent executions. In this recipe, I used SET STATISTICS XML ON to capture the query’s XML execution plan definition. That definition was then copied into the OPTION clause. The USE PLAN hint requires a Unicode format, so the XML document text was prefixed with an N'.

Both USE PLAN and plan guides should be used only as a last resort—after you have thoroughly explored other possibilities such as query design, indexing, database design, index fragmentation, and out-of-date statistics. USE PLAN may have short term effectiveness, but as data changes, so too will the needs of the query execution plan. In the end, the odds are that, over time, SQL Server will be better able to dynamically decide on the correct SQL plan than you. Nevertheless, Microsoft provided this option for those advanced troubleshooting cases when SQL Server doesn’t choose a query execution plan that’s good enough.

Viewing Execution Runtime Information

SQL Server 2005 provides four commands that are used to return query and batch execution statistics and information: SET STATISTICS IO, SET STATISTICS TIME, SET STATISTICS PROFILE, and SET STATISTICS XML.

Unlike the SHOWPLAN commands, STATISTICS commands return information for queries that have actually executed in SQL Server. The SET STATISTICS IO command is used to return disk activity (hence I/O) generated by the executed statement. The SET STATISTICS TIME command returns the number of milliseconds taken to parse, compile, and execute each statement executed in the batch. SET STATISTICS PROFILE and SET STATISTICS XML are the equivalents of SET SHOWPLAN_ALL and SET SHOWPLAN_XML, only the actual (not estimated) execution plan information is returned along with the actual results of the query.

The syntax of each of these commands is similar, with ON enabling the statistics, and OFF disabling them:

SET STATISTICS IO { ON | OFF }

SET STATISTICS TIME { ON | OFF }

SET STATISTICS PROFILE { ON | OFF }

SET STATISTICS XML { ON | OFF }

In the first example, STATISTICS IO is enabled prior to executing a query that totals the amount due by territory from the Sales.SalesOrderHeader and Sales.SalesTerritory tables:

655

Document info
Document views116
Page views116
Page last viewedFri Dec 09 10:09:48 UTC 2016
Pages48
Paragraphs987
Words15822

Comments