The Engine features section is traditionally the most important, or interesting, for most DBAs or system administrators when a new version of Microsoft SQL Server is released. However, there are also numerous engine feature improvements that have tangential meanings for developers too. So, if you are a developer, don't skip this artice - or you may miss some improvements that could save you some trouble later on!
Query Store
The Query Store is possibly the biggest new engine feature to come with the release of SQL Server 2016. DBAs and developers should be more than familiar with the situation of a query behaving reliably for a long period, which suddenly changed into a slow-running, resource-killing monster. Some readers may identify the cause of the issue as the phenomenon of parameter sniffing or similarly through stale statistics. Either way, when troubleshooting to find out why one unchanging query suddenly becomes slow, knowing the query execution plan(s) that SQL Server has created and used can be very helpful. A major issue when investigating these types of problems is the transient nature of query plans and their execution statistics. This is where Query Store comes into play; SQL Server collects and permanently stores information on query compilation and execution on a per-database basis. This information is then persisted inside each database that is being monitored by the Query Store functionality, allowing a DBA or developer to investigate performance issues after the fact.
It is even possible to perform longer-term query analysis, providing an insight into how query execution plans change over a longer time frame. This sort of insight was previously only possible via handwritten solutions or third-party monitoring solutions, which may still not allow the same insights as the Query Store does.
Live query statistics
When we are developing inside SQL Server, each developer creates a mental model of how data flows inside SQL Server. Microsoft has provided a multitude of ways to display this concept when working with query execution. The most obvious visual aid is the graphical execution plan. There are endless explanations in books, articles, and training seminars that attempt to make reading these graphical representations easier. Depending upon how your mind works, these descriptions can help or hinder your ability to understand the data flow concepts—fully blocking iterators, pipeline iterators, semi-blocking iterators, nested loop joins... the list goes on. When we look at an actual graphical execution plan, we are seeing a representation of how SQL Server processed a query: which data retrieval methods were used, which join types were chosen to join multiple data sets, what sorting was required, and so on. However, this is a representation after the query has completed execution. Live Query Statistics offers us the ability to observe during query execution and identify how, when, and where data moves through the query plan. This live representation is a huge improvement in making the concepts behind query execution clearer and is a great tool to allow developers to better design their query and index strategies to improve query performance.
Stretch Database
Microsoft has worked a lot in the past few years on their Mobile First, Cloud First strategy. We have seen a huge investment in their cloud offering, Azure, with the line between on-premises IT and cloud-based IT being continually blurred. The features being released in the newest products from Microsoft continue this approach and SQL Server is taking steps to bridge the divide between running SQL Server as a fully on-premises solution and storing/processing relational data in the cloud.
One big step in achieving this approach is the new Stretch Database feature with SQL Server 2016. Stretch Database allows a DBA to categorize the data inside a database, defining which data is hot and which is cold. This categorization allows Stretch Database to then move the cold data out of the on-premises database and into Azure Cloud Storage.
The segmentation of data remains transparent to any user/application that queries the data, which now resides in two different locations. The idea behind this technology is to reduce storage requirements for the on-premises system by offloading large amounts of archive data onto cheaper, slower storage in the cloud.
This reduction should then allow the smaller hot data to be placed on smaller capacity, higher performance storage. The magic of Stretch Database is the fact that this separation of data requires no changes at the application or database query level. This is a purely storage-level change, which means the potential ROI of segmenting a database is quite large.
Database scoped configuration
Many DBAs who support multiple third-party applications running on SQL Server can experience the difficulty of setting up their SQL Server instances per the application requirements or best practices. Many third-party applications have prerequisites that dictate how the actual instance of SQL Server must be configured. A common occurrence is a requirement of configuring the Max Degree of Parallelism to force only one CPU to be used for query execution. As this is an instance-wide setting, this can affect all other databases/applications in a multi-tenant SQL Server instance (which is generally the case). With Database Scoped Configuration in SQL Server 2016, several previously instance-level settings have been moved to a database-level configuration option. This greatly improves multi-tenant SQL Server instances, as the decision of, for example, how many CPUs can be used for query execution can be made at the database-level, rather than for the entire instance. This will allow DBAs to host databases with differing CPU usage requirements on the same instance, rather than having to either impact the entire instance with a setting or be forced to run multiple instances of SQL Server and possibly incur higher licensing costs.
Temporal Tables
There are many instances where DBAs or developers are required to implement a change tracking solution, allowing future analysis or assessment of data changes for certain business entities. A readily accessible example is the change in history on a customer account in a CRM system. The options for implementing such a change tracking system are varied and have strengths and weaknesses. One such implementation that has seen wide adoption is the use of triggers, to capture data changes and store historical values in an archive table. Regardless of the implementation chosen, it was often cumbersome to be able to develop and maintain these solutions.
One of the challenges was in being able to incorporate table structure changes in the table being tracked. It was equally challenging creating solutions to allow for querying both the base table and the archive table belonging to it. The intelligence of deciding whether to query the live and/or archive data can require some complex query logic.
With the advent of Temporal Tables, this entire process has been simplified for both developers and DBAs. It is now possible to activate this change tracking on a table and push changes into an archive table with a simple change on a table's structure. Querying the base table and including a temporal attribute to the query is also a simple T-SQL syntax addition. As such, it is now possible for a developer to submit temporal analysis queries, and SQL Server takes care of splitting the query between the live and archive data and returning the data in a single result set.
Columnstore indexes
Traditional data storage inside SQL Server has used the row-storage format, where the data for an entire row is stored together on the data pages inside the database. SQL Server 2012 introduced a new storage format: columnstore. This format pivots the data storage, combining the data from a single column and storing the data together on the data pages. This storage format provides the ability of massive compression of data; it's orders of magnitude better than traditional row storage.
Initially, only non-clustered columnstore indexes were possible. With SQL Server 2014, clustered columnstore indexes were introduced, expanding the usability of the feature greatly. Finally, with SQL Server 2016, updateable columnstore indexes and support for In-Memory columnstore indexes have been introduced. The potential performance improvements through these improvements are huge.
Containers and SQL Server on Linux
For the longest time, SQL Server has run solely on the Windows operating system. This was a major roadblock for adoption in traditionally Unix/Linux based companies that used alternative RDBM systems instead. Containers have been around in IT for over a decade and have made a major impression in the application development world. The ability to now host SQL Server in a container provides developers with the ability to adopt the development and deployment methodologies associated with containers into database development. A second major breakthrough (and surprise) around SQL Server 2017 was the announcement of SQL Server being ported to Linux. The IT world was shocked at this revelation and what it meant for the other RDBM systems on the market. There is practically no other system with the same feature-set and support network available at the same price point. As such, SQL Server on Linux will open a new market and allow for growth in previously unreachable areas of the IT world.
This concludes the section outlining the engine features. Through Microsoft's heavy move into cloud computing and their Azure offerings, they have had increased need to improve their internal systems for themselves. Microsoft has been famous for its dogfooding approach of using their own software to run their own business and Azure is arguably their largest foray into this area. The main improvements in the database engine have been fueled by the need to improve their own ability to continue offering Azure database solutions at a scale and provide features to allow databases of differing sizes and loads to be hosted together.
