SQL 2016: worth the upgrade?

Although, I have not performed side-by-side comparisons on identical hardware, with identical databases, and identical queries/reports/execution packages. It is extremely likely to improve the performance of many regular database activities. Microsoft really focused on improving the performance of existing features.

Digging into testing that has been done by Microsoft’s internal engineering team, Lenovo’s product testing team, and Dell’s product testing team you’ll find these results:

• Lenovo – 38.4% performance increase on identical hardware
• Dell – 22 identical queries on an identical 900 million row data warehouse load ran 2.6 times faster, a performance improvement of 2,600%
• Microsoft – 34% faster when running on identical hardware

None of this guarantees improvements seen for specific TimeXtender use cases. Although, because TimeXtender’s Data Warehouse Automation product is designed to standardize the T-SQL within SQL Server and uses SQL Server's engine to perform the majority of the work, you have some built-in reliability that all of the processes involved in your data warehouse will work similarly on both SQL Server 2014 and 2016. This makes it very likely that you will be able to take advantage of 2016’s performance improvements.

But, as always, make sure to test your own systems before any upgrade. Custom code or unique scenarios or edge cases are always worth taking a careful look at. Pay special attention to the changes Microsoft made to the cardinality estimator and changes to auto-updating of statistics.

Again, none of this is specific to TX DWA, but the products reliance on SQL Servers database engine means any internal changes to the engines function or performance can affect performance. My personal feeling is it's highly likely to provide a benefit for the majority of your activities.

Keep in mind that for smaller sized databases it won't "feel" faster. But it's all relative. If you examined the query plans you may actually see a benefit of 30%, but that number could be nanoseconds in reality. It's going to depend on the process and the load. The larger and slower performing processes would be the first ones to check. Next would be the one's run most often.

There are places online to see what has changed and get suggestions for things to test:

• Query Optimizer Additions
• Why can an upgrade cause a performance regression