October 27, 2010 | SQL Server

SQL Sentry's Plan Explorer : a closer look

Today I was digging more into SQL Sentry's 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.

 

3 comments on this post

    • Chris Wood - October 27, 2010, 8:57 PM

      Aaron,
      How long do you expect this to be in beta testing? I'd like to use it once it is finally released.
      Thanks
      Chris

    • AaronBertrand - October 27, 2010, 9:34 PM

      Hi Chris,
      I think it largely depends on what kind of issues people see over the next few weeks.  So far it seems pretty solid based on a rather large audience, so I'd be surprised at release time to see a much different build than the one you see now.
      I don't think there should be anything stopping you from using it now.  It does not interfere with your SQL Servers or your SSMS installations, and you don't have to connect to any servers with it … you can just use it to load plans locally (you can even install it on a machine or VM without any SQL Server stuff installed at all, just with a way to get the .sqlplan files over there).
      Let me know what your concern is about using the beta (aside from the fact that the word "beta" is in there).  Maybe I'm biased but this tool seems to me to be a lot more stable than some tools other vendors have released in a 1.0 or later state.  🙂
      Aaron

    • Chris Wood - October 27, 2010, 10:23 PM

      Aaron,
      I'll plug it in after the PASS Summit then.
      Thanks
      Chris

Comments are closed.