Interleaved Execution not happening in SQL 2017 DB

Question

We have a SQL Server 2017 database that was imported from a 2008 instance long ago. The compatibility level is set to 140 (confirmed by the execution plans), but no multi-statement TVFs are ever executed with interleaving and always have cardinality estimates of 100.

I ran these test queries against the WideWorldImportersDW DB and they were executed with Interleaving, but when I create the relevant objects in my original DB and run the queries there, no interleaved execution occurs (see both plans below).

I was wondering if anyone else has experienced this and what, if anything, can be done to fix it. Thanks in advance.

---

The sys.database_scoped_configurations DMV does NOT contain DISABLE_INTERLEAVED_EXECUTION_TVF. I do NOT get interleaved execution if I add a DISABLE_BATCH_MODE_ADAPTIVE_JOINS hint. Parameterization is set to Simple in both databases.

The Interleaved_exec_disabled_reason extended event is firing with reason Parameter Sniffing. sys.database_scoped_configurations has Parameter Sniffing = 0 in the old database, but 1 in the new.

Changed the old database to Parameter Sniffing = 1, and Interleaved Execution is now working.

• Bad plan
• Good Plan

Answer 1

Interleaved execution cannot work with parameter sniffing disabled. The current implementation needs the sniffed values for the initial compilation and partial execution to capture runtime TVF cardinality.

It is a shame the documentation does not mention this, or many of the other reasons that can be reported by the Interleaved_exec_disabled_reason extended event. Extended events documentation is also rather thin, in general.