Free Tool: SQL Sentry's Plan Explorer

Color enhancements show where the most expensive operations are in the plan. Plus, you can select either CPU or I/O as the “expense” you want to measure. Since I/O is more often the problem than CPU these days, I prefer I/O costing.

Plan Explorer automatically highlights common problems, such as table scans, key lookups, cumulative operation costs, and parallelism. It also allows you to compare compiled and runtime values to help you determine if parameterization might be a problem in the query.

Full object names and the full query are easy to view—simply right-click objects in the viewer.

Plan Explorer’s horizontal space isn’t wasted in the tree view, compared to SQL Server Management Studio (SSMS). Instead, Plan Explorer branches only when nesting another layer rather than at every operation’s branch.

Additional information is available in new tabs such as the Statement Tree tab, which makes monstrously large execution plans easy to read and parse. The Join Diagram tab shows the nesting of query objects, such as when views are built on top of other views. The Operations tab highlights the difference between estimated rows and actual rows.

You can operate directly from the XML plan file rather than connecting to a SQL Server system 

Queries can be run directly from Plan Explorer as needed. However, keep in mind that it’s a tuning tool, rather than a query tool, so you’ll get all the information you need, including statistics I/O, but not the actual result set.

This tool lets you choose between the number of rows and total data volume of the rows, where as line size between operation steps shows only the number of rows in SSMS. For example, SSMS would have a tiny line for eight rows, but imagine if each row was 2GB of XML data. Plan Explorer puts that in perspective.

Plan Explorer corrects the problem in SSMS in which a missing index warning appears for all execution plans in a batch, even when only one plan is missing an index. Missing index information also appears by default in the Statement Tree tab.

In SSMS, plans can occasionally add up to more than 100 percent of execution cost. Plan Explorer doesn’t have this issue.

Plan Explorer lets users add columns and sort in grids.