Comparing SQL Server Versions: A Performance Perspective

When it comes to upgrading SQL Server versions, one of the primary concerns is performance. As each new version is released, the engine has to handle an increasing amount of code and branch logic for backwards compatibility. This can lead to a natural assumption that query benchmarks may slow down over time.

In my experience, this is indeed the case with SQL Server 2019. Initially, I thought that lightweight query profiling changes or CEIP x events might be contributing to the performance regression. However, after testing on a single VM hosting both SQL Server 16 and 19, I found that the performance issue persisted.

With historical data stored over several years, I was able to look back and confirm that the performance regression occurred when our instances were migrated to new VMs running SQL Server 2019. To monitor any changes made by our infrastructure teams at the OS or VMware level, I had instrumented code to run nightly benchmarks across a large farm of SQL Servers.

This experience highlights the importance of baselining, which can help you decide when to upgrade. You may also want to document some queries (in addition to those provided by Brent) and record the results. Interestingly, after performing similar testing in SQL Server 2022 (Compatibility Level 160), I found similar results, suggesting that the real problem might be an issue with the client implementation or hardware rather than an inherent problem with SQL Server 2019.

In my demo case, using a MAXDOP of 4, the 2019 Compatibility Level ran with about 46% less CPU and duration than SQL Server 2016. When I changed the MAXDOP to 1, as Brent did in his article, the results improved further, with about 38% less CPU and duration.

Overall, while there may be a performance regression when upgrading to SQL Server 2019, it's essential to identify the root cause of the issue rather than assuming it's an inherent problem with the version. By doing so, you can take targeted actions to optimize your system and ensure a smoother upgrade process.

Sql server versions compare