A quick blog post to start the day, If you remember a few months ago I blogged about SQL Server vNext https://blobeater.blog/2017/02/03/sql-server-vnext/, more specifically around the Adaptive Query Processing feature.

Well, Joe Sack recently blogged about a feature called Interleaved Execution which I used back in January and tested out performance of it on versus off  across some stored procedures, the testing element is mentioned in this article: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/

Joe states in the article “MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions. Interleaved execution will help workload performance issues that are due to these fixed cardinality estimates associated with multi-statement table valued functions.”

This is exactly what I saw where the below is just a basic screen shot of 1 of many tests that I carried out.

I used the Query Store for the most part of the analysis and it really did not show any signs of regression.

However don’t forget that this is the first version of interleaved execution, if the code is mixed with INSERTS/UPDATES then it will not apply, hopefully I can test this out in the future too. I totally forgot about this fact during my testing and at one point I sat for 5 hours straight trying to understand why I was not seeing the change in estimations!

Fun times are ahead!