Today I was digging more into SQL Sentry Plan Explorer, and I continue to discover little things I think you will find valuable in your performance tuning and troubleshooting efforts.

Let's say I am analyzing a plan for a relatively simple batch:

 SELECT * FROM sys.objects;
 
CREATE TABLE #foo([object_id] INT, def NVARCHAR(MAX));
 
INSERT #foo SELECT [object_id], OBJECT_DEFINITION([object_id])
   FROM sys.procedures;
 
SELECT OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id])
   FROM #foo WHERE def LIKE '%foo%';
 
DROP TABLE #foo;

I want to find the heavy hitters here, but the output from the estimated execution plan in SSMS is not very helpful:

It happens, in this case, that if I have a really tall monitor, the most expensive operator is visible.  But for most people with smaller monitors, the default layout, and Object Explorer visible, here is what they're more likely to see:

This will require lots of scrolling and panning to get at the most expensive operators.  Also note that when you highlight one of the plan segments, you have to rely on visually parsing the query (if it's even all visible – otherwise very fast reading of a temperamental tooltip) to determine which part of the query above the selected plan segment corresponds to.  In a lot of cases this may be trivial, but I can see some scenarios making this quite cumbersome.

If you have Plan Explorer installed, you can play along, and we will see how much easier this *could* be.

First, in Management Studio, turn on Include Actual Execution Plan (you can do this via the toolbar, the Query menu, or with the keyboard shortcut Ctrl+M), and execute the query (F5).  This will allow you to switch back and forth between Management Studio and Plan Explorer to compare the output.

Now, open Plan Explorer, and in the Text Data pane, paste the query from SSMS.  You'll notice everything else stays empty.  Click on the "Retrieve Estimated Plan" button.  Here you can connect to any instance of SQL Server to generate a plan:

Once you click Connect, the tool will go to the server and generate an estimated plan.  When it comes back, you will see this (click to embiggen):

Now note, when you highlight any row in the Statements Tree pane, the rest of the UI updates with your current context.  So let's say I was interested in the statement that had the 98% estimated cost; when I highlight that row, the statement in the query in the Text Data becomes highlighted, and the Plan Diagram (as well as the other tabs) are all updated to reflect the current statement as well:

And note that you can do the same thing by scrolling around or clicking anywhere within the Text Data pane.

Once again, for a FREE tool, this goes leaps and bounds beyond what you can do today in Management Studio. You can download your own copy here:

http://greg.blogs.sqlsentry.net/2010/10/sql-sentry-plan-explorer-beta.html

And don't worry, I've got plenty more where that came from.