The Land Of Do-Overs
Of the new things in SQL Server 2019 that I plan on presenting about, the Batch Mode on Row Store (BMOR, from here) enhancements are probably the most interesting from a query tuning point of view.
Things like Accelerated Database Recovery, Optimize For Sequential Key, and In-Memory Tempdb Metadata are cool, but they’re server tuning. I love’em, but they’re more helpful for tuning an entire workload than a specific query.
The thing with BMOR is that it’s not just one thing. Getting Batch Mode also allows Adaptive Joins and Memory Grant Feedback to kick in.
But they’re all separate heuristics.
Getting Batch Mode
To get Batch Mode to kick in for a Row Store query, it has to pass a certain set of heuristics, which can be viewed in Extended Events.
SELECT dxoc.object_name, dxoc.name, dxoc.description FROM sys.dm_xe_object_columns AS dxoc JOIN sys.dm_xe_objects AS dxo ON dxo.package_guid = dxoc.object_package_guid AND dxo.name = dxoc.object_name WHERE dxo.name = 'batch_mode_heuristics' AND dxoc.column_type = 'data' ORDER BY dxoc.name;
Once we’ve got Batch Mode, we can use the other stuff. But they have their own jim-jams.
Getting Adaptive Joins
To get Adaptive Joins, you need to pass these heuristics.
SELECT dxmv.name, dxmv.map_value, dxo.description FROM sys.dm_xe_map_values AS dxmv JOIN sys.dm_xe_objects AS dxo ON dxmv.object_package_guid = dxo.package_guid AND dxmv.name = dxo.name WHERE dxmv.name = 'adaptive_join_skipped_reason';
No, those aren’t plain English, but you can decode most of them. They mostly deal with index matching, and cardinality making sense to go down this route.
Getting Memory Grant Feedback
For excessive grants, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant and update the cached plan. Plans with memory grants under 1MB will not be recalculated for overages.
For insufficiently sized memory grants that result in a spill to disk for batch mode operators, memory grant feedback will trigger a recalculation of the memory grant. Spill events are reported to memory grant feedback and can be surfaced via the spilling_report_to_memory_grant_feedback XEvent event. This event returns the node id from the plan and spilled data size of that node.
We can still see some stuff, though.
SELECT dxoc.object_name, dxoc.name, dxoc.description FROM sys.dm_xe_object_columns AS dxoc JOIN sys.dm_xe_objects AS dxo ON dxo.package_guid = dxoc.object_package_guid AND dxo.name = dxoc.object_name WHERE dxo.name = 'memory_grant_feedback_loop_disabled' AND dxoc.column_type = 'data' UNION ALL SELECT dxoc.object_name, dxoc.name, dxoc.description FROM sys.dm_xe_object_columns AS dxoc JOIN sys.dm_xe_objects AS dxo ON dxo.package_guid = dxoc.object_package_guid AND dxo.name = dxoc.object_name WHERE dxo.name = 'memory_grant_updated_by_feedback' AND dxoc.column_type = 'data' ORDER BY dxoc.name;
Getting All Three
In SQL Server 2019, you may see plans with Batch Mode operators happening for Row Store indexes, but you may not get an Adaptive Join, or Memory Grant Feedback. If you have a lot of single-use plans, you’ll never see them getting Memory Grant Feedback (I mean, they might, but it won’t matter because there won’t be a second execution, ha ha ha).
It’s important to remember that this isn’t all just one feature, but a family of them for improving query performance for specific scenarios.
In compat level 150.
Say, where’d I put that scotch…
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.