SQL 2016 upgrade

The main draw for SQL Server 2016, at least from my point of view, is the Query Store.

Query Store allows us to answer the question of how queries were performing in the past, to easily track and fix query performance regressions, to see total resource consumption over time and to identify expensive queries. Previously we would have needed to run Extended Event sessions over periods of time, import the results and aggregate them and then analyse the results, and that’s prone to missing queries if they don’t run all the time. DMVs don’t have that problem, but the data they contain is not persisted, and is lost when the server restarts.

Query Store, once it is enabled, captures queries, plans and performance characteristics and persists them into the database. Since it’s persisted and running all the time, we don’t need to worry about whether there was an extended event session running when a query showed a problem, or whether the server has been restarted since the problem started. The data showing historical query performance is a query away.

And that’s far from the only feature added in this version.

We received temporal tables, tables that automatically keep a history and which are queryable for how the data in the table looked at any point in time.

Stretch DB was introduced, the ability to stretch a table into Azure so that cold data can be stored in the cloud, transparently to users.

Always Encrypted finally provides an answer to preventing the DBA from seeing data by transparently encrypting the data in the application and storing only encrypted data in the database.

Row level security allows users to be assigned rights to read or modify subsets of the data in tables. No more need to add views for security.

Live query statistics makes diagnosing current query problems easier, as there’s no need to wait for the query to finish to get the run-time statistics.

There are also improvements to Columnstore indexes, In-memory OLTP, Always On Availability Groups and a pile of internal optimisations that make SQL Server 2016 just run faster.

But, upgrading is risky, especially with the cardinality estimator changes in SQL Server 2014. Testing for broken functionality isn’t hard if the apps have good test coverage, but testing for performance has been really difficult.

Until now with Query Store.

Testing performance for the SQL Server 2016 upgrade is as easy as restoring a copy of the DB to a testing server, enabling Query Store, running the workload under old and new compatibility levels, and then using the built-in Query Store reports to identify queries that experienced performance regressions. What could take days of work to identify the regressions in previous versions, can now be done by looking at a single built in report. In some cases, fixing the performance regression may also be easily done with Query Store, using the plan forcing feature.

That was the story at RTM, a compelling case for upgrading, and a feature that makes it far easier to test for that upgrade than ever before.

Then SP1 was released.

The big news of SP1 wasn’t the new features it had, though the features were interesting. The big news of SP1 was about the Enterprise edition-only features, like Columnstore indexes, In-Memory OLTP, Partitioning, Compression and several more.

As of SQL Server 2016 SP1, those Enterprise-only programmability features are available in all editions, right down to Express.

This makes SQL Server 2016 a must-have upgrade for anyone using Standard edition of any lower version. Upgrade to SQL Server 2016 standard edition, apply SP1 and get a whole pile of features that were previously only available in Enterprise Edition.

But there’s more. SP1 also included some nice features itself, including a way to clone a database without data in a single statement, a more flexible hinting model for queries, more DMVs for monitoring what SQL Server is doing and the addition of the CREATE OR ALTER syntax for stored procedures, functions, views, and triggers, which makes deployment scripts simpler.

This is one of the best versions of SQL Server in a long time, and upgrading to it should be on almost every IT department’s roadmap for this year.