SQL Plan Explorer Missing Execution Mode

DBNewbie_2007 2017-12-21 17:53:11

In SSMS, the various graphical execution plans we can get the Actual and Estimated Execution mode… ROW or, for Columnstore indexes, BATCH.

Is there some flag or option setting that needs to be set in Sentry Plan Explorer to show the Estimated/Actual Execution modes?

Thanks!
link text

/storage/attachments/2170-historytabletest-anonymized.pesession
Aaron Bertrand 2017-12-21 18:06:58
We certainly collect this information, it's simply not shown by default on certain grids. If you look at the Plan Tree tab, for example, then go to the Column Chooser (by right-clicking any column header), you can drag "Actual Execution Mode" or "Est Execution Mode" onto the column headers. (There is currently a bug (#33288) that points out that actual is only populated for batch.)

alt text

These are also on tooltips in the graphical plan, for relevant operators. For example, a columnstore index scan:

alt text

If these data points are not there (you need to check both places), you can assume that they're both row. In the plan you added to your question, you are right, the tooltips for those operators do not show the execution mode, and I addressed those in the comment below. But if you look on the Plan Tree tab, you can see the batch mode operators if you add those two columns to the grid using the column chooser:

alt text

DBNewbie_2007 2017-12-21 18:33:41
Interesting, you can see the Execution mode but my copy of Plan Explorer cannot. I am using version 11.3.1.0 and viewing the details via the plan diagram windows. I also checked the plan XML and it contains the execution mode.

alt text

Aaron Bertrand 2017-12-21 18:35:46
I am also running 11.3.1. Do you have any ColumnStore operators in the plan? Can you share the plan? You are right that we don't show execution mode for operators where we don't expect batch mode to be possible. Our list of operators at the time that code was written may not match the list of known operators today, and as people start moving onto more modern versions of SQL Server, we do have work planned around improving these diagnostics.
DBNewbie_2007 2017-12-21 18:54:43
I added it to the original post… I kept getting a "We're sorry, but you are not allowed to answer multiple times to a question." then trying to add it to the "Your Answer" section.
Aaron Bertrand 2017-12-21 19:01:50
Updated answer.
DBNewbie_2007 2017-12-21 19:13:57
Thanks for the update. Sorry, but in SSMS 2017, all of the operators are showing execution mode, which I discovered as part of a tuning webinar I watched recently.

I typically run a query, and if trying to analyze or optimize it, I automatically move the execution plan over to Sentry Plan Explorer as the default analysis tool; I typically expect Sentry Plan Explorer to be the SSMS actual execution plan on steroids! LOL. We loved this tool so much we had purchased the "Pro" copies a while back. 🙂

Aaron Bertrand 2017-12-21 19:30:24
Certainly. They have a much bigger team and get much more advance notice when more operators are able to do batch mode. Plan Explorer offers a lot more in terms of analysis, but sometimes we fall behind on features just due to the nature of the space.
December 21, 2017
No Comments