Batch mode on rowstore is a SQL 2019 engine feature so how can a query running CE 70 have this feature?
Cardinality estimation model is separate from compatibility level, and features available by way of using newer compatibility levels.
The "new" cardinality estimation model was introduced in SQL Server 2014, and like the original cardinality estimation model, has undergone various changes over the years.
This only controls the formulas SQL Server uses during query optimization to determine how many rows will potentially qualify for or be projected from various predicates, joins, groupings, etc.
It wouldn't be sensible to tie the two settings together so tightly, where users would either use the legacy model and get no new features, or the new model and get all of the new features.
The "new" model is only "new", and in many cases not better, than the legacy model.
Unless I am misunderstanding and a query that is using legacy CE can still leverage SQL 2019 IQP features?
Correct. You can change cardinality estimation in other ways, too. A mostly-correct list of valid use hints is available here:
SELECT
d.*
FROM sys.dm_exec_valid_use_hints AS d;
Which includes FORCE_LEGACY_CARDINALITY_ESTIMATION and FORCE_DEFAULT_CARDINALITY_ESTIMATION.
It also includes several ways to control which compatibility level features can be made available to your query.
- QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100
- QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110
- QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120
- QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130
- QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140
- QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150
- QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160 (SQL Server 2022)
There is even a specific use hint that you may find specifically helpful, specifically for your specific query, having HTDELETE problems: DISALLOW_BATCH_MODE.
Also, has anyone experienced poor performance when a query plan features Batch Mode on Rowstore?
The wait associated with this particular query is HTDELETE
You should post a new question with relevant details.
