Troubleshooting Database Compatibility Levels

Randolph West tells a tale about checking compatibility levels:

In that demo, the AdventureWorks sample database was initially set to compatibility level of 140 (SQL Server 2017 default compatibility) to execute a scalar UDF. At this point, the estimated execution plan showed that the UDF was given a cost of 0%, and performance was terrible (the expected behaviour). Then the database compatibility level was switched to 150 (which is all that’s required to enable this new optimization feature), the query was executed again, the UDF was inlined, and performance improved dramatically.

This is where it got interesting. As a test, the compatibility level of the database was set back to 140, but the query plan continued to inline the UDF. Curious. Flushing the plan cache didn’t change the outcome (even though we knew it wasn’t necessary). Had we discovered a bug in a preview version of SQL Server 2019? It was CTP 2.2 after all, and since then (at the time of this writing) CTP 2.5 is already available.

Read on for the answer.

Related Posts

AG Support for Containers: Not RTM

Allan Hirt makes a disappointing discovery: This one is going to be a short post. One of the big capabilities of SQL Server 2019 – the ability to deploy AGs using containers – is not shipping when SQL Server 2019 is released. Read the whole thing, which isn’t much longer than my snippet. But Allan […]

Read More

Rolling Windows Upgrades with AGs + WSFC

Allan Hirt shows how you can combine Availability Groups with Windows Server Failover Clusters and upgrade the operating system version while keeping your SQL Servers running: The configuration for a cluster rolling upgrade allows for mixed Windows Server versions to coexist in the same WSFC. This is NOT a deployment method. It is an upgrade […]

Read More

Categories

May 2019
MTWTFSS
« Apr Jun »
 12345
6789101112
13141516171819
20212223242526
2728293031