This post gives a brief overview about
- How the execution plan can be saved in a XML format and
- How the saved XML file about query plan be seen in a GUI format.
1. SHOWPLAN_XML generates compile-time query execution plan information for a query, but does not execute the query. STATISTICS XML generates run-time query execution plan information for a query, and executes the query.
So set the necessary setting on.
SET SHOWPLAN_XML ON
<< Query >>
SET SHOWPLAN_XML OFF
In the Results pane, right-click the Microsoft SQL Server 2005 XML Showplan that contains the query plan, and then click Save Results As and also select ‘All Files’ in the type column. Save the plan with extension, ‘.sqlplan’
2. Loading the plan is as simple as that,
Just open SQL Server management studio, File -> Open. Select all types in the type column. Look out for the plan which is saved in the .sqlplan format.
You can now see the graphical format of the plan which you have in XML.
Other references:
http://technet.microsoft.com/en-us/library/ms190646(SQL.90).aspx
- This is a good post about identifying Top 20 CPU utilising queries in your system and their query plans via various DMVs. Once you get a graphical query plan, you can easily find the real culprit from the cost, where the maximum percentage lies.
