Hello fellow TimeXtender users,
We were wondering: is it worth upgrading from SQL 2014 to 2016 in the scenario where we use TX traditionally: stage - dw - cubes.
Has anyone done it and have you seen performance improvements?
Thanks!
Wim
CALM - Co
Solved
SQL 2016: worth the upgrade?
Best answer by Anonymous
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:
+5I personally have not seen any difference using TX DWA with 2014 vs 2016. Anyone else?
+1I'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:
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:
1 person likes this
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
