Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Add some kind of visual indicator that an operator ran in batch mode

I often use actual time statistics in actual plans at the operator level to figure out why a query plan is slow. For clarity, I'm referring to this information in the XML:

<RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="11" ActualCPUms="11" />

CPU time is measured differently for row mode and batch mode operators. Row mode operators contain CPU for that operator and all of its children. Batch mode operators measure CPU just for that operator. SQL Server 2019 brings us batch mode for rowstore, but it isn't obvious which operators actually ran in batch mode from a quick look at the plan. Further complicating matters is that statistics can be reset between parallel zones in plans. All in all I've found it significantly more difficult to find the slowest operators in 2019 plans compared to 2017 plans. I can think of a few things that would help:

1) In SSMS, provide a small visual indicator that an operator ran in batch mode.
2) In SSMS, do the math for us to get the best available operator statistics time available.
3) In SSMS, enhance the "Find Node" functionality to be useful for ActualCPUms. Right now the search options are only equality and contains which doesn't seem to have a use case.
4) In SQL Server, add columns for execution mode to sys.dm_exec_query_profiles.
5) In SQL Server, add columns for the parallel zone to sys.dm_exec_query_profiles.
6) In SQL Server, add columns for the parent operator node id to sys.dm_exec_query_profiles.

2 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Joe Obbish shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

0 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base