Your browser (Internet Explorer 7 or lower) is out of date. It has known security flaws and may not display all features of this and other websites. Learn how to update your browser.


SQL Server 2016 CTP2 is now ready for testing!

sqlserver2016Sharing first thoughts

Today Microsoft announced the CTP2 of SQL Server 2016 (, the long waited public preview of the new SQL Server version. And as one of my friends said some time ago, “I was just about to retire when they release a new version and it became exited again”. Speaking of myself, I am not planning to retire soon, but a first CTP of a new SQL Server version is truly exciting event,  like taking a new adventure, it is like getting a new motivation for the job.

So what motivates me in this new version and what I am planning to write and speak about in next few months (years) (and hopefully implement in practice and production environments).

I am rarely doing one of those posts that just targets clicks by fancy title and says nothing but a link that you could find by yourself. So beside giving you links and keywords of the product, let me share with you my favorite features that I am going to test and blog, and some info about how to start your own testing of this newly released CTP2.


My favorite feature

Query Store

This is my most favorite new feature of the SQL Server 2016! Its been a long time since they promised to have it in the products and talked about an option to analyze execution plans from the cache (and not only), store them, compare them for a particular query, find queries that are not performing well in the last 24 hours, even force more productive and optimal plan for a query. This is a great way to do performance tuning on the job and to resolve fast a cardinality error in plans or bad parameter sniffing. It’s easy, just enable it at a database level and you can start cumulating query execution data and plans. You can see the graphsical view (dashboard like view) in SSMS or you can execute DMVs and functions against the data in the QDS. This will definitely be the topic of my next post.


Features that I am happy we have it in the product and I will test and blog about

In-memory enhancements

The SQL Server 2014 was the first product release that introduced InMemory technology for OLTP type of workloads. The 2016 version increases the T-SQL surface area for using InMemory OLTP, and introduces InMemory for ColumnStore. And here comes my pessimistic point of view: I doubt that you are going to combine two types of workloads – intensive (extreme) transaction processing and analytics in the form of datawarehouse complex reports that are typically good candidates for Column store indexes. But in case you plan to do that then you will possibly need a server that is very well sized for that and that has plenty of RAM. In case you are more or less following the good practices of separating two types of workloads than InMemory analytics will still work for you and will potentially speed up your datawarehouse queries by multiple times.


Always Encrypted technology

Data Encryption has been a pain for too long. All the options that have been available as using keys with encryption functions, or using some hashing did not worked for most of the cases. Either because of performance issues with encryption/decription or because of the data being available for the DBAs or DBdev respectively. Security changes in the product are something that is very sensitive and complex to implement, there shouldn’t be a “release one” argument for something that is not working. The Always Encrypted has nothing in common with AlwaysON HA, I think this is kind of confusing. It works with AlwaysON solutions but it is different. The idea is to have data encrypted “at rest and in-motion”, meaning that the client provider is performing that work of encryption and decryption, and the data is stored encrypted inside the database, but decrypted when needed by the native client.


AllwaysON improvements

Having 3 synchronous replicas, DTC support and round-robin load balancing between secondary’s is something that has been requested and expected I would say since the first AlwaysON release in SQL Server 2012. So great we have it now! And this is something I would like to test – how it synchs, how it chooses the replica etc.


Enhanced Database Caching (multiple Tempdb data files per Instance in multi-core environments) , Temporal Database and and definitely Stretch tables are features that sound promising and that I am also eager to see in deep how they work.


Other features (I am not gone cover)

JSON support

As Brent says and I fully agree, SQL Server is one of the world’s most expensive application servers. No additional comments needed

All the BI Features for Reporting and Analytics

Тhey are just too much, I am not sure I will be able to test and cover all of them, but I will definitely try to


First of all before diving into features of the product let me share some feedbacks I got from customers. Most of them say that the next version is coming too fast after the last release. They usually start commenting the it as “really? I haven’t seen all the new features of SQL Server 2014 yet!” Still, I think that most of the enterprises will not manage to align with this fast release cycle of the product and as I have noticed many times already, they will skip version(s) (even more than one) in their own roadmaps.


Info for starting with CTP2:


Start Testing by:


Thanks and good luck !


Post Update:

Installation part

Link to new post

Documentation part

The product documentation also has been published. You can find it here: