I personally have not seen any difference using TX DWA with 2014 vs 2016. Anyone else?

I've not seen any difference in the typical scenarios we've upgraded for. One reason to upgrade is to be able to use Power BI on the cubes.

If you're running Enterprise edition you could benefit from better column store indexes on fact tables.

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

Thanks for the feedback everybody.

From Justin's feedback I gather that in theory cleansing rules should run faster. We are going to do some tests on our most complex ETLs and see if we see any performance difference.

I would say that with yesterday's announcement that SQL2016 SP moves support for compression, partitioning, columnstore from Enterprise Edition to Standard Edition, that would be a big reason for current 2014 Standard Edition upgrades to 2016:

https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/16/sql-server-2016-service-pack-1-generally-available/