Query Execution Plans security and new feature within SQL Server 2008 Management Studio
If there is any performance issue within your queries then looking at query execution plan is the first step towards assessing the loss, you may be aware that execution plans of queries and queries on partitioned tables and indexes can be examined by using the TSQL SET statements such as SET SHOWPLAN_XML or SET STATISTICS XML, or by using the graphical execution plan output in SQL Server Management Studio.
There are 2 methods of taking into execution plan for a query such as display the compile-time execution plan by clicking Display Estimated Execution Plan on the Query Editor toolbar or the run-time plan by clicking Include Actual Execution Plan. Kind of further help you can get by using these tools such as ascertain the operations of scans and/or seeks during insert/update/merge/delete operations from the executed queries. Also if your data is partitioned then the set of partitions accessed by your query will also be displayed such as the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans. Not only this you can also get such an information of execution plan with the help of Profiler event classes or using DMV sys.dm_exec_query_plan.
Though it is classed to view the plan of the queries the security aspect should also be considered here where you have each method that requires a different set of permissions, such as only grant the SHOWPLAN permission to trusted users because it might be possible to infer information about SQL Server objects from Showplan output. As this is a database-level permission to grant can be easily granted or revoked where by default members of SYSADMIN group will have the access rights, in addition to the db_owners fixed database role group, bear in mind even dbcreator fixed server role would also have privilege as they create the databases that will inherit such a permission. So inside the SQL relational engine when a SHOWPLAN process is started the permission is checked when a with that TSQL or batch executes and Showplan information is generated and this check will not occur when SHOWPLAN SET option is set to ON.
You may also be aware that within SQL Server 2005 Management Studio you can save the query plan to a .sqlplan file where you will have to open it by selecting appropriate file option such a opening as .XML document and saving it as .sqlplan file. Whereas, within SQL Server 2008 Management Studio you can easily open the save plan file by double-clicking it to open in SSMS to see as a query plan which will get you a graphical plan of query execution.