On June 6th, 2018, we released a new build of Plan Explorer: 18.4.0. Download it now!
I always like to share a little insight behind the release notes, so I’ve detailed some of the enhancements below. I skipped a few fixes because they’re really not all that interesting, but if you have any further questions or feedback, please let us know.
The Version Number
We’ve followed the recent change in the SentryOne platform (which also turned 18.4 today!) to publish major version numbers corresponding to the year, and minor versions corresponding to the release in that year. While this isn’t the 4th minor release of Plan Explorer this year, the goal is to make that consistent with the platform going forward.
35482 – Implement ShowPlanXML.xsd 1.8 from SSMS 17.5
There is a new ShowPlanXML schema that offers a bunch of additional pieces of information we can use, in upcoming releases, to make issues more visible in the plan diagram and various grids within Plan Explorer. This enhancement won’t be obvious on its own, but it does enable other enhancements we’ve already done, including…
36994 – Update estimated rows and row goal handling
“Estimated Rows to be Read” and “Estimated Rows (without row goals)” are now illustrated on the tooltip for relevant operators (orange emphasis mine):
32220 – Add Adaptive Join operator icon
SQL Server 2016 added the “Adaptive Join” operator, which allows SQL Server to decide at runtime whether to use a hash join or a nested loops join. Here it is in action for an estimated plan (we’ll talk about actual plans in a moment):
37634 – Add UDF metrics to statement grid and warnings to root node
On modern versions of SQL Server, the amount of work spent inside scalar UDFs is reported in the showplan XML (for actual plans only), so we can relay it to you and emphasize when this is contributing to performance issues. In the following example, you can see that we add the UDF elapsed and CPU time to the statement grid and, if the percentage of total elapsed and/or CPU time is more than 50%, we’ll highlight the grid and show a tooltip:
We’ll also add a warning to the root node’s tooltip in the plan diagram:
38954 – Add schema to operator object name in diagram
Not a lot of magic here; we added the schema prefix to eliminate confusion between staging.Employees and dbo.Employees in the same plan, and also to help identify when the query is hitting staging.Employees instead of dbo.Employees.
39162 – Simplify color scaling
When we first implemented colors to highlight the most expensive operators in a plan, we used an infinite scale of red to orange and then from orange to yellow. 50% and 49% were a very slightly different red, but few humans would ever be able to perceive the difference. We switched out an infinite scale for 6 buckets of specific red and orange colors, and we stopped highlighting costs for nodes that represent < 50% of the cost of the most expensive operator in the plan. We think that for the majority of plans this will help you better focus on the problem(s), and for a good portion of the remainder, you won’t be able to tell the difference.
38218 – Apply opacity for operators with 0 executions + actual rows
There are several cases where an operator is simply uninteresting, because it does no work. We’ll always show all the operators and costs from the estimated plan, but we’ll add information about actual costs when we have them, including cases where the operator was skipped.
Take the adaptive join example above – while the estimated plan sets up with both nested loops and hash join operators, when the query actually runs, only one of those operators does anything. So, when looking at actual costs, we dim the other operator (even though the cost % above will still show the estimated cost in many cases).
Compare these two side by side – estimated costs on the left, actual costs on the right:
Another example is where a table or predicate has been eliminated at runtime. Here we have a query against a partitioned view, but no rows were returned from any of the partitioned tables because of the predicate:
And a final example is where a parallel operation is skipped because at runtime there weren’t enough rows to justify splitting them apart and putting them back together. This often happens when you use hints to force order, or coerce parallelism or hash grouping, and those weren’t optimal.
If we look at the Plan Tree tab for this operator, we can see further evidence that those two parallelism operators didn’t lift a finger, just take a look for any operators that didn’t have any thread handle a single row:
In all of the above cases, any such operator will have some information appended in its tooltip:
Note that this is quite different from an operation which read the table and yielded zero rows, or passed zero rows along; these operations didn’t even bother – we can tell because their execution count is zero. We’ll have more visualization updates for you in this space soon.
37897 – ASYNC_NETWORK_IO truncated to NETWORK_IO on Wait Stats tab
This one was pretty simple; the wait associated with ASYNC_NETWORK_IO is actually mapped in the Extended Events code as NETWORK_IO (see this post for more info). We weren’t translating, so wait stats tab just showed the original string, and as such the link to the SQLskills SQL Server Wait Types Library was broken.
37889 – Statement grid column filters treat all values as text
Dates and numbers were treated as text in the filters in the statement grid. You would try to filter for duration > 500 and it would return 7 but leave out 2000 – because it was comparing as text. If you have any interesting behavior when filtering in the statement grid, please let us know.
38075 – Memory grant warning message has incorrect values
Most of you probably didn’t notice this (because we missed it too), but when we added details about memory grant warnings to the tooltip, we converted one set to MB but left the label as KB, so they were off by, oh, a factor of ~1,000:
36237 – Double-click actions should not try to manage indexes
There were a few areas in the diagram and grids that inadvertently had a double-click action associated with them. The intent was to open an index properties dialog in SSMS:
Since this functionality was never actually implemented in Plan Explorer, we removed the double-click actions. If we do introduce this action in the future, it will be through a more intuitive right-click context menu.
36234 – SSMS Add-in does not launch PE consistently
There were a few edge cases where the menu option from our Management Studio add-in would not launch the plan in Plan Explorer, or would launch a new window instead of reusing an existing one. We have dealt with several of those scenarios but, please, if you see any inconsistency here, let us know.
<!–
Pretty basic one here, in the Plan Tree tab, if you had a plan with both Row and Batch mode operators, the Actual Execution Mode column would only populate for batch mode, while the Estimated Execution Mode column would populate for both.
–>
39954 – Update installer .NET Framework requirement
Plan Explorer has inherited several code changes from the SentryOne platform, including some of its system requirements. One requirement that may be important to you is .NET Framework 4.7.1. You can download the latest version (4.7.2 at the time of writing) from Microsoft Downloads, but you will want to check .NET Framework system requirements first. Because 4.7.x is incompatible with older versions of Windows, you’ll need Windows 7 SP1 or better, Windows 8.1 or better, Windows 10 Anniversary Update or better, or their server equivalents.
40004 – Implement deadlock visualization changes
In SentryOne 18.3, we made several enhancements to our deadlock analysis, making them much easier to diagnose, and even replayable. Now they are also being introduced in the free deadlock analysis feature in Plan Explorer. This is a pretty big deal, and I’ll blog about these enhancements separately. Here is a sneak peek from the SentryOne 18.3 Change List:
