SQL Server Query Plan Analysis

SQL Sentry Plan Explorer builds upon the graphical plan view in SQL Server Management Studio (SSMS) to make query plan analysis more efficient. It is a lightweight standalone app that contains many of the plan analysis features introduced in SQL Sentry v6, and does not require a collector service or database. You can either use Plan Explorer for FREE or upgrade to Plan Explorer PRO.

 

Plan Explorer FREE

  • Color-coding that highlights potential trouble areas
  • Multiple grids and other views to allow for sorting and deeper analysis
  • Collection of real runtime metrics with the generation of actual plans
  • Upload plans to Answers.SQLPerformance.com to receive advice from industry experts

Plan Explorer PRO

  • All of the features of Plan Explorer FREE are included. Plus:
  • Multiple query tuning plans or sessions can be compared side-by-side
  • Different versions of your query can be kept within a single session
  • Contextual comments can be added to any version of your plan
Why Go Pro?

Product Datasheet

The Product Datasheet provides you with all of the features in our tools. You can easily compare our tools and see which ones fit your organization’s needs.

 

 

Demo Kit for Plan Explorer

SQL Server MVP Aaron Bertrand shares a demo kit for Plan Explorer to give you better insight into the advantages of the tool, and to help you share its virtues with colleagues, user groups, and the SQL Server community at large.

 

Features

Why Go Pro?

Plan Diagram

Plan Explorer Plan Diagram

See more of the plan on your screen thanks to our zoom controls and optimized layout algorithm. You can quickly identify the costliest operators through intuitive color coding. The Plan Diagram focuses on specific resource utilization by showing operator costs by I/O or CPU. It also displays the number of rows or total data size on operator exchange arrows and shows the expensive sub-trees by displaying cumulative costs instead of per-node costs.

Statements Tree

Plan Explorer Statements Tree

The Statements Tree allows you to sort by any column, making it easier to focus on a specific metric without having to find it in the graphical plan. You can easily spot statements with the highest costs, duration, CPU, I/O, and row counts and quickly see discrepancies in actuals vs. estimates. You can compare different statements using a variety of revealed metrics, including key lookups, scan operations and missing indexes. If you are using Plan Explorer PRO, you can also see the entire plan tree for estimated plans and the full query call stack for actual plans.

Plan Tree & Top Operations tabs

Plan Explorer Plan Tree and Top Operations Tabs

The Plan Tree shows the hierarchical representation of the query plan. You can quickly spot costly scan operations and discrepancies in actual vs. estimated row counts. You can also immediately see imbalanced thread distribution in parallel operations. Another advantage is that you can review metrics like execution counts, data size, rebinds/rewinds and other costs per operation. If you would like a sortable representation of the Plan Tree then you can use the Top Operations tab.

Join Diagram tab

Plan Explorer Join Diagram Tab

The Join Diagram tab showing the underlying tables even when complex and nested views are involved. You can quickly visualize the relationship between tables involved in the plan and easily reorganize the layout using drag and drop.

Query Columns tab

Plan Explorer Query Columns Tab

The Query Columns tab shows every column being used in the plan, and its associated operation and index. It also shows whether columns are used for sorting, grouping or filtering. You can quickly spot scan operations, key lookups and actual vs. estimated discrepancies with color coding. You can also easily identify candidate columns for index changes to eliminate key lookups and scans.

Parameters tab

Plan Explorer Parameters Tab

The Parameters tab shows all query parameters and their compiled values. You can quickly spot potential plan issues due to parameter sniffing. With actual plans, you can also compare compiled and runtime values.

Expressions tab

Plan Explorer Expressions Tab

The Expressions tab shows simplified and expanded expressions that are used in the query. By looking at the tab, you can discover implicit conversions that you may not have spotted otherwise.

Table I/O tab

Plan Explorer Table I/O Tab

The Table I/O tab shows the I/O against each table and worktable involved in the plan as well as the scan count per table. It also shows the logical, physical and read-ahead reads, separated by in-row and LOB data, per table. These features allow you to spot potentially redundant operations.

History and Comments

Plan Explorer PRO History and Comments

Plan Explorer PRO allows you to maintain a history of different versions as you fine tune your query. You can step forwards and backwards to compare different versions of your query within a single session. You can also revert back to any version of the query with a single click. Another advantage is that you can add contextual comments to any version of the plan and then save and share your work with other users, even if they are using the free version of Plan Explorer.

Multiple Documents

 Plan Explorer PRO Multiple Documents

Plan Explorer PRO allows you to use multiple tabs to open multiple query tuning sessions in a single Plan Explorer instance. You can also pull a tab off the main interface or tile tabs vertically or horizontally with a Visual Studio-like docking interface. You can easily compare two or more plans or sessions side-by-side.

Wait Stats tab

 Plan Explorer PRO Wait Stats Tab

The Wait Stats tab is supported when generating actual plans against SQL Server 2008 and above. It allows you to quickly correlate and compare different types of waits that result from query or schema changes. You can also see where time was spent for your batch when generating an actual plan.

Full Query Call Stack

 Plan Explorer PRO Full Query Call Stack

You can see the full tree and actual runtime metrics for all nested statements, aggregated to the calling statement. You can also easily trace the source of statements from dynamic SQL, conditional logic, loops and nested procedure calls. This allows you to identify nested statements or iterations that are outliers in terms of any performance metric.

Deadlocks

 Plan Explorer PRO Deadlocks

With Plan Explorer PRO, you can open and troubleshoot deadlock files (.xdl) from any source. This helps you investigate many details about processes involved in the deadlock using the synchronized grid view. It also helps you track the sequence of events and other critical information in the graph view.

Benefits

Speed

Plan Explorer helps you quickly get to the root of tough query tuning issues by making it obvious where issues exist in your execution plan. From highlighting expensive operations to allowing you to sort by any metric in most grids, you're never more than a click or two away from pinpointing a tuning opportunity. In Plan Explorer PRO you can open and compare multiple plans at the same time within one window, and quickly review changes due to different versions of your query.

 

Visibility

Plan Explorer exposes many details about the operators in a plan that either aren't available or are very cumbersome to obtain otherwise. With actual execution plan generation you can see runtime metrics such as Reads, CPU and Duration, as well as thread distribution in parallel operators, all without manually setting statistics options or digging into a plan's properties. In Plan Explorer PRO you can even view actual wait stats when you generate an actual plan.

 

Collaboration

With Plan Explorer you can share .sqlplan and .queryanalysis files with colleagues. In Plan Explorer PRO you can share your entire session, including revision history and comments – even if your colleagues are using the free version.

 

Higher Quality

SQL Sentry software undergoes intensive regression, scalability and overhead testing in our internal test lab of over 100 SQL Servers. It is then real-world tested in one of the largest SQL Server hosting environments in the U.S., until it is deemed stable enough to release to the public... no matter how long it takes. After release, our integrated, automated exception reporter gives us a perpetual feedback loop with our customers, who are continually surprised to receive a response from a real human usually within hours of submitting an exception.

 

Continual Innovation

While other SQL Server monitoring tools have stagnated, SQL Sentry has continued to push the envelope, introducing several industry firsts such as our patent-pending SQL Server disk analysis, unrestricted historical performance reporting, comprehensive deadlock analysis, performance event calendars, intelligent wait analysis, and many more cutting edge features.

 

Credibility

We've been a Microsoft Gold Certified Partner for many years, and our customer survey results say it better than we ever could. Our marks across the board are consistently higher than other Microsoft partners. All customers fell into the "Champion" quadrant, ranking us amongst the top Microsoft ISVs in the world. Survey results.

 

Videos

Plan Explorer Demo by Jonathan Kehayias (SQLskills)

Plan Explorer Demo by Jonathan Kehayias (SQLskills)

Jonathan Kehayias of SQLskills discusses the advantages of using Plan Explorer. He shows one of his favorite execution plans in SQL Server Management Studio and compares it to what it looks like in Plan Explorer. He also walks through the different features of Plan Explorer.

Plan Explorer Demo by Paul Randal (SQLskills)

Plan Explorer Demo by Paul Randal (SQLskills)

Paul Randal of SQLskills shows how to analyze what's using space in tempdb, and provides an overview of how to use SQL Sentry Plan Explorer in the investigation. Immerse yourself in SQL Server with the SQLskills Insider newsletter.

SQL Query Plan Analysis Made Easier

SQL Query Plan Analysis Made Easier

During this 1 hour webcast, Brent Ozar and Tim Ford provide an overview of what each DBA should consider with SQL Server query plans. Aaron Bertrand shows the capabilities within SQL Server Management Studio and then demonstrates how SQL Sentry Plan Explorer can make query plan analysis so much easier.

Watch more Plan Explorer videos at: SQLSentry.TV

Pricing

Much of the great functionality in Plan Explorer is free, however there are additional features available in Plan Explorer PRO. The PRO version is licensed per user; future version upgrades will be provided at a discount for current users. There is no annual maintenance for Plan Explorer PRO, and support is available via our knowledge base and online support forum.

For a complete listing, view the product price list and online store.

  License Quantity
Plan Explorer FREE
FREE Unlimited
SAVE 10% (5-9) / SAVE 20% (10+) Plan Explorer PRO
$295.00
$265.00
$235.00
1 - 4
5 - 9
10+
BUY NOW

Download

Plan Explorer is a single installation file containing both the FREE and PRO versions. Upon launching the application or at any point after, users will have the capability to activate a 15-day trial of Plan Explorer PRO to evaluate the additional features. A license key can be entered to upgrade or Plan Explorer will convert to the FREE version after the 15-day trial.

 

Plan Explorer User Guide

The SQL Sentry Plan Explorer User Guide (Web | PDF) covers features and general use of SQL Sentry Plan Explorer.

 

.NET 4.0 MUST BE PRE-INSTALLED to install and use SQL Sentry Plan Explorer. Download .NET 4.0 here.

Management Studio Add-In

The Plan Explorer SSMS add-in allows you to jump directly to Plan Explorer from SSMS by simply right-clicking an execution plan and choosing the "View with SQL Sentry Plan Explorer" context menu option. The add-in works with Management Studio for SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. Plan Explorer must be installed separately.

;