Since SQL Server 2005 , the SET SHOWPLAN_XML is the recommended method to gather SQL Execution Plan information. Analysing the Execution Plan offers great clues for troubleshooting and improving query performance time. 

The SET SHOWPLAN_XML  according to SQL Server BOL “Causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements will be  to be executed in the form of a well-defined XML document”

Things to look for:

1) Use an XML Editor – such as IE , which allows you to expand and collapse the hierarchy

2) Clustered Index Scans – Focus on estimated rows . Why does Query Optimizer choose a Clustered Index Scan?

3) Missing Indexes – allows some useful information. Basically, the optimizer suggests that the suggested index will improve performance – with an impact attribute percentage figure. Don’t  automatically create the Missing Index. First , commit a update statistics, rerun query – has anything changed?

 Second , look at the suggested index – weigh up , how often is this query used versus the  maintenance overhead . 

4) Information such as the CachedPlanSize will help you estimate if your query exceeds one page in length. This impacts how the cached plan is managed in memory. More of this topic in a future post