I recently had the chance to sit down with my friend and fellow SQL Server MVP Aaron Bertrand to review SQL Sentry’s free tool Plan Explorer. Plan Explorer was initially developed as an internal troubleshooting tool for SQL Sentry’s support department. The support team kept bumping up against the limitations of the native SQL Server tools, and SQL Sentry decided that rather than settling for the default, they’d take matters into their own hands and create a more effective tool for deciphering SQL Server execution plan details. The tool was so good that SQL Sentry decided to offer it as a free download on their website. Let’s take a look at some of the key readability and usability features this free tool offers.
Plan Readability Features
Plan Explorer is so chock–full of features that it’s hard to know where to start. I decided to point out the features that I find most useful. The following are several enhancements in Plan Explorer that make execution plans much easier to read:
- 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.
Usability Features
Plan Explorer’s usability features are efficient and easy to use. The following are some of my favorite features included in this tool:
- 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.
I’ve hit on some of my favorite features in this tool. However, there are many more to investigate once you’ve installed the product on your workstation.
System Requirements
Plan Explorer requires Microsoft .NET Framework 4.0, which isn’t included in the Windows Installer. Otherwise, this tool has the same requirements as SSMS. In fact, SQL Sentry has created a SSMS plug-in that lets you to go directly from SSMS to Plan Explorer. Plan Explorer supports SQL Server 2005 and later.
You can download Plan Explorer and read the program details at the SQL Sentry web site. Aaron actively supports the product on Twitter and blogs about it frequently. Visit the SQL Sentry forums for more information about Plan Explorer at forum.sqlsentry.net, where you’ll find FAQs, online documentation, and more.
Plan Explorer
Benefits: You can use SQL Sentry’s Plan Explorer to get faster performance and tuning information from SQL Server query plans.
System Requirements: Microsoft .NET Framework 4.0; SQL Server 2005 and later
How to Get It: You can download Plan Explorer from the SQL Sentry web site
