SQL Plan Explorer Missing Execution Mode

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

historytabletest-anonymized.pesession (8.3 kB)
avatar image By DBNewbie_2007 0 asked Dec 21, 2017 at 05:53 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
▼ Viewable by all users
0
2 answers: sort voted first ▼

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

exec-mode.png (13.2 kB)
exec-mode-tooltip.png (52.7 kB)
batchmode.png (13.5 kB)
avatar image By Aaron Bertrand ♦ 1.7k answered Dec 21, 2017 at 06:06 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
▼ Viewable by all users
0

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

sentrydetails-122117.png (17.0 kB)
avatar image By DBNewbie_2007 0 answered Dec 21, 2017 at 06:33 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Dec 21, 2017 at 06:35 PM

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.

avatar image DBNewbie_2007 Dec 21, 2017 at 06:54 PM

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.

avatar image Aaron Bertrand ♦ Dec 21, 2017 at 07:01 PM

Updated answer.

avatar image DBNewbie_2007 Dec 21, 2017 at 07:13 PM +

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. :)

10|10000 characters needed characters left
▼ Viewable by all users
0
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x2
x1

asked: Dec 21, 2017 at 05:53 PM

Seen: 38 times

Last Updated: Dec 21, 2017 at 07:30 PM

Related Questions

Test run with a Columnstore index, the duration got a lot larger