1

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.

With Interleaved Execution Without Interleaved Execution

Paul White
  • 94,921
  • 30
  • 437
  • 687
Simon Bramley
  • 11
  • 3

1 Answers1

2

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.

Paul White
  • 94,921
  • 30
  • 437
  • 687