One of the biggest pain points for SQL Server 2000 was for looking at the plan of a SQL Statement, you have to execute the same. As per my understanding SQL Server 2000 did not expose any mechanism for this. So the only way to look at the plan of the query was to capture the queries used by application using profiler and then run them in Query Analyzer. This was a nasty process and sometimes it was not possible to use this mechanism if the query involved dependency on some objects not present in the query under consideration, for instance temporary tables.
In SQL Server 2005, this analyis has been made possible by presenting the plan in XML format. For looking at the plan of the query in XML format, execute the following command -
-- Find Plan for a Query--select query_plan from sys.DM_EXEC_QUERY_PLAN(0x0600050059571120B8414C0B000000000000000000000000)
For better understanding of DMV DM_EXEC_QUERY_PLAN, see http://msdn2.microsoft.com/en-us/library/ms189747.aspx.
Being able to look at the plan after the query has executed is a big help. But I would have preferred to see this plan in graphical format. How do I do that?
SQL Server 2005 also supports a mecanism to so that. To accomplish this, store the xml plan in a file and change the extension of the file to .sqlplan. Now open this file in SQL Server Management Studio and VOLA you can see the plan in graphical format.