#ITDevCon15 Conner Cunningham Keynote

On Wednesday morning, Conner Cunningham from Microsoft was the keynote speaker for IT/DevConnections in Las Vegas. His talk focused on SQL Server 2016. To summarize the things he said that got my attention were a Live Query Plan, the Query Store to retrieve a previous query plan and Azure Dev First – then on-premise. Continue reading if you want more information.

The talk concentrated on SQL Server 2016. His favorite parts are making queries run faster because this is the area he helped in planning and development years ago. Now, he encourages the developers and architects to make queries run faster. DBAs love this stuff.

The talked started with an overview of the new features in releases 7.0 to 2014. It was good to see the progress in SQL Server from the early days to today.

Conner said SQL Server 2016 is a very, very big release – more announcements will come at PASS Summit 2015 at the end of October in Seattle.

The data engine is now 3 to 4 different actual engines in different development streams but using the same skill set for the DBA. The rise of cloud computing has shifted to development in Azure first, then using the same code base to release on-premise.

The development can be done faster now due to changes in the process at Microsoft and release and testing is easier because of the cloud. This gets feedback to the development team faster and updates to problems released sooner. What used to take 3-5 years, now is done in months.

Microsoft only vendor where on premise and cloud are fully supported on same code base!!!

Here is a list of new 2016 features

Security

1. Row level permissions to limit rows returned (select statement returns only the rows the user has permission to see.)
2. TQE – Transparent Queryable Encrypt (better protection)
3. Data Mask columns (x’s in a SSN)

AlwaysOn Improvements

1. Log transport improvements
2. DB level failover rather than instance
3. Load balance readable secondary
4. ActiveDirectory integration
5. DTC transaction support  (this was a big one)
6. 2 failover targets

Language enhancements

1. JSON support 
2. Temporal table support – Historical tracking of changes automatically, Think Audit Tables

In-Memory Engine – OLTP (2014)

1. 5-20 times faster
2. Collation support, JOINs, Large DBs, MARS support, Initial UDF/TDF support and row level security

Column store enhancements

1. Updateable non-cluster column store indexes
2. Non-clustered B-tree indexes
3. Always On support – readable secondary support

Polybase

1. Query relational and non-relational data with T-SQL
2. Hadoop support

Column store scales better with Degrees of Parallelism

1. Batch mode scales far better

Stretch SQL Server in Azure

1. For data you do not want to delete but need at some point
2. Increases backup time where the warm data is backed up and cold data is in cloud (which has HA and backups/restores)
3. For historical data

Upgrade

1. Rewriting upgrade guide (was 429 pages)

Improved Upgrade advisor

1. Try the tool

Eliminate Trace flags for High-End Scaling

1. Most have been integrated in 2016

Optimizer Changes Now Tied to DB Compatibility Level

1. Less risk for upgrades
2. Trace Flag 4199 folded in new DB compatibility Level

Query Store

1. Force prior plan
2. Deep insight into workload performance
3. Simplifies upgrades by reducing change risk

Conner says he has been working on Query Store for a long time and is extremely happy about this has finally been done