SQL Sentry Plan Explorer v1.1

출처 : http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

Aaron Bertrand

Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.

SQL Sentry Plan Explorer : Version 1.1!

Last week, Microsoft offered up an early Christmas present: SQL Server 2005 SP4.  This week, it's SQL Sentry's turn to play Santa Claus: several new features and fixes have been packaged up into SQL Sentry Plan Explorer 1.1 (build 6.0.67.0).

So, what's new?  Several wish list items have been fulfilled (hey, it is Christmas, after all).  You can see the full change list here; but I'll talk briefly about a few of my favorites:

Parallel distribution
The Plan Tree tab for a parallel operator now shows the distribution of rows across threads, as well as the partitioning type (broadcast, demand, hash, or round-robin).  This can be useful in identifying issues where the row counts are not distributed evenly among the threads, which could indicate deeper scheduler issues.  In SSMS this information is buried in the Properties panel (taking up even more valuable real estate) and requires several clicks - here, it is just a single click away.  Tracking this down may help you decide whether to force a different type of join, or to simply set MAXDOP to 1 for this specific query, the classified workload (if using Resource Governor), or the entire instance.

 

For some further reading on thread distribution and parallelism, look no further than the blogs of Adam Machanic and Craig Freedman.

Parameter List tab
If you run a batch like this in SSMS, you get the query text, but only the showplan XML will tell you the runtime values of the parameters.  Go ahead and look through the tooltips for each operator, or the endless stream of information offered to you in the Properties panel:

DECLARE     @object_id INT = 5,     @object_name NVARCHAR(4000) = N'%foo%'; SELECT * FROM sys.objects     WHERE [object_id] > @object_id     OR name LIKE @object_name;

If you save this execution plan and open it in Plan Explorer 1.1, you will get a new Parameter List tab which will list out these parameters and their runtime values:

Of course it's easy enough to just look at a batch like the above, and know exactly what the parameter values are.  But what if you're trying to track down a parameter sniffing issue for a stored procedure whose runtimes just went through the roof?  Wouldn't it be useful to know both the runtime and compiled values of certain parameters?  Let's say you have a simple procedure that accepts a DATETIME parameter, and you first call it with a parameter like 2030-01-01:

CREATE PROCEDURE dbo.search     @dt DATETIME AS BEGIN     SET NOCOUNT ON;     SELECT * FROM sys.objects         WHERE modify_date >= @dt; END GO EXEC dbo.search @dt = '20300101';

The plan shows parameter values as follows:

 

Now the procedure has been compiled with a value of 2030-01-01, which returns 0 rows.  So we can see that on the Top Operations tab, the estimate and actual rows are very close:

If we then call the procedure again with a vastly different parameter, say 1970-01-01:

EXEC dbo.search @dt = '19700101';

The plan now shows parameters like this:

 

And since the runtime and compile values are so disparate, we can see how this affects the estimated and actual rows, which we can see on the Top Operations tab:

Reason for Early Termination
There are several cases where optimization bails out early in trying to find a plan.  Sometimes this is because the plan is so trivial, it is not worth the optimizer's time to go and figure out the best possible plan - this would be like spending $500 on theft insurance for a $50 car. But sometimes there are other reasons, including the self-explanatory "Time Out" and "Memory Limit Exceeded," and what SQL Server calls "Good Enough Plan Found" - which means it found a decent plan, and isn't going to spend any effort trying to find a better one.  Plan Explorer now includes the Reason for Early Termination in the tooltip on the head node where applicable.  A yellow warning icon means there was a timeout (the reasons for this can vary); a red critical icon means that memory limit was exceeded (which indicates memory pressure - see the Note in this MSDN article on XML Showplans).

For further information on optimization and early termination, see Grant Fritchey's recent blog post, "Reason for Early Termination of Statement."
 

File > Recent Files
Now you can open files you've accessed recently, regardless of how you originally opened them.  This feature wasn't left out intentionally but didn't quite make the very first build of Plan Explorer.  If you find you are opening saved plan files often through the file menu, Jonathan Allen shared a great productivity tip in his blog post, "Using SQL Sentry Plan Explorer."

Text Data tab
The Text Data tab, in addition to becoming bold, now adds "(Edit Mode)" when you have modified the query text (you may also notice that the title bar adds this text as well, and that the Statements Tree section becomes grayed out to make it obvious that it is no longer relevant to the text data).  Changing the text of the query can be useful when you want to try minor modifications and generate a new estimated plan, without going back and forth to Management Studio.

Scalar Expression mapping
Previously, on the tooltip for a scalar operator, you could see a list of operations and then a list of expressions in the output list, but there was no way to map which operation belonged to which item in the output.  While sometimes this correlation is obvious, now Plan Explorer shows exactly which expression each operator is responsible for:

Conclusion
I hope that gives you a good taste of the improvements we've made to SQL Sentry Plan Explorer; there are several other little enhancements and stability improvements documented in the change list.  If you're still using SSMS for execution plan analysis, download the free Plan Explorer and see what you're missing!  If you're already using 1.0, you can easily install 1.1 - just close any existing instances of Plan Explorer, run the setup program, and all of your existing shortcuts should launch the newest version.