Interleaved Execution And Compatibility Levels

Arun Sirpal gives us some helpful information regarding interleaved execution in SQL Server 2017:

I have read-only T-SQL that references the MSTVF. I did have some code that use both data modifications and cross apply but interleaved execution does not occur in those scenarios.

So on my SQL Server 2017 instance I set the database to 110 compatibility mode and set query store on where then I execute my code.

Note that 110 is the compatibility mode for SQL Server 2012.  That becomes an important part of Arun’s story.

Related Posts

When Adding Indexes Hurts Performance

Jeffry Schwartz takes us through an odd case: Recently, a customer requested that we tune a query that took 13 seconds to return 11 rows.  SQL Server 2017 suggested an index to improve performance, so we added it in a development environment.  The improvement made the query run 647 seconds, almost 50 TIMES longer than the original!  This naturally caused […]

Read More

Power Query and the Benefits of Immutability

Chris Webb explains why immutable expressions can be faster to run multiple times than mutable processes: Instead of taking the value #”Sorted Rows”[Column2]{0} and storing it in the variable Column2 then adding Column2 four times, I’m  adding the expression #”Sorted Rows”[Column2]{0} together four times. The query returns the same number as the previous query. However […]

Read More

Categories

November 2017
MTWTFSS
« Oct Dec »
 12345
6789101112
13141516171819
20212223242526
27282930