Today’s post is not going to be about explaining something but more along the lines of my experience on SQL Server 2014.

When I heard about all the new features in SQL Server 2014, the In Memory Tables, new cardinality estimator, new changes to how TempDB works, SSIS and Delayed Durability, I was excited to see how I could use these new features for my data warehouses.

I did a full backup, restore to a Virtual Machine (VM), test the upgrade process to SQL Server 2014 to ensure that when I completed the upgrade I would not have any unforeseen issues. (As well as test my backup files and my restore document to ensure that I could restore my system from a backup)

So the first thing that I did was to test the In Memory tables, and I was excited by using the In Memory table with no durability, this would enable my staging tables to work that much faster in getting data from the staging tables into my disk based Fact Tables. I tried a few variations in terms of what Index to use and different types of tables to use. As we are all aware due to this being the first release of In Memory tables, there are some limitations. I have now come to the conclusion for my current workloads that the new In Memory tables offer no real benefit for my staging tables in my data warehouse. I find that at times it is slower than the disk based tables. (I am aware that I could well be not using them correctly, but I did quite a lot of reading and testing to ensure that I was doing what was suggested).

Next I was hoping the combination of the cardinality estimator, changes to the TempDB and Delayed durability would increase the throughput of my queries, which would lead to faster load times from my staging tables into my Fact tables. I have monitored the time my SSIS Packages have taken to run, and unfortunately I cannot see any vast improvement in my times.

What I can say that is working in the new SSDT with Visual Studio 2013 does seem a lot quicker to develop and when debugging in my experience it starts a lot quicker than in the past. Yes it did take some getting used to all the new images for all the control flow items, data flow items etc, but that is a small price to pay. I do prefer the Dark Theme Smile 

So for me in my current environment I would say that all the new changes have not made any real significant impact on my data warehouse. And have not made it any quicker to load data and get it into my Fact table.

NOTE: I welcome anyone who has any suggestions or idea’s to please let me know. I am more than willing to learn as well as potentially not have configured it correctly.