SQL Query Show Execution Plan

The Show Execution Plan basically represents the strategy SQL Server is using to search for and retrieve data.  This execution plan is then cached and used henceforth as long as the source query or stored procedure doesn’t change. 

The Show Execution Plan, and the related Display Estimated Execution Plan option, allows detailed analysis of a query’s performance.  An administrator can see exactly where the performance cost is occurring and, as a result, can reduce the cost by modifying the query.  One example of this is table index utilization.  Queries against large tables must take proper advantage of the table’s indexes.  Otherwise they will experience a significant performance hit as the query attempts to retrieve an excessive number of records from the table that will be unnecessarily discarded through other methods (i.e. Joins).

For more information, please see your SQL Server Books Online.