Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Overview of Database Engine Changes in SQL Server 2017


By:   |   Read Comments (3)   |   Related Tips: More > SQL Server 2017

Attend these FREE MSSQLTips webcasts >> click to register


Problem

On October 2nd, SQL Server 2017 became generally available; however, I have yet to find a single document that describes all of the enhancements to the database engine.

Solution

The following is a description of all of the changes I have found, organized by feature area. Note that some of these were either back-ported to, or originally released in, SQL Server 2016 SP1 or later.

Administration

Linux/Docker

This is the first SQL Server version to be supported on various flavors of Linux as well as Docker containers. You can get a free whitepaper detailing SQL Server across all platforms here.

Compatibility Level 140

This new compatibility level is required to take advantage of several of the features listed below, and they are similar to the features that need 140 in Azure SQL Database, as described in this blog post. Note that if you upgrade an instance to SQL Server 2017, or restore/attach a database from an older version, the compatibility level will not move up to 140 automatically.

Smarter Backups

There are a couple of changes that allow you to make more intelligent decisions around differential and log backups.

  • The column modified_extent_page_count was added to sys.dm_db_file_space_usage so that you can dynamically determine whether you should take a full or differential backup. For example, you could set a threshold to take a differential backup as long as less than 20% of the extents have been modified. You can see more about this in the tip SQL Server 2017 Differential Backup Changes.
  • New DMVs sys.dm_db_log_info and sys.dm_db_log_stats were added to provide information that currently requires DBCC LOGINFO. You can use the information here to get more predictable transaction log backup sizes, by seeing how much data has changed since the last log backup. You can also keep track of virtual log file (VLF) counts and sizes over time. See more in the tip SQL Server 2017 Transaction Log Backup Improvements.

tempdb configuration

In SQL Server 2016 there were many changes to tempdb, including better configuration during setup, and better visibility in the error log when configuration was not optimal. In SQL Server 2017, we get an increase in the maximum initial data files size from 1GB to 256GB (see Allan Hirt’s post). Note that a warning is issued if instant file initialization is not enabled.

Temporal Tables

The original implementation in SQL Server 2016 was missing two important features: CASCADE for foreign key constraints, and a retention policy to prevent the history tables from becoming unmanageable (I wrote this tip about the latter back in February). These features are now both supported; more information about retention policies can be found in the docs. Personally, I think there should be a policy that allows you to keep n versions of a row, instead of it being time-based, since some rows in a table may change a lot more frequently than other rows.

Availability Groups

A number of enhancements were made to Availability Groups specifically (and a few other performance improvements mentioned later will also benefit AGs as a nice side effect):

  • Availability Groups can now be set up without an underlying cluster (WSFC) and across mixed environments (instances on Windows and Linux/Docker).
  • There is a new Minimum Replica Commit setting – this means you can dictate that a certain number of secondary replicas must commit a transaction before committing on the primary.

Version store monitoring

Ever feel like you want to enable READ COMMITTED SNAPSHOT, but don’t know how to reliably determine the impact on tempdb? A new DMV, sys.dm_tran_version_store_space_usage, allows you to see the impact on version store usage, grouped by each database. So you can use this to profile your workload in a testing environment, before and after the change, and also to monitor the impact over time – even if other databases are also using version store.

Environmental information

With the move to Linux, there was a need for additional information exposed within system views to determine operating system specifics:

  • sys.dm_os_host_info was added, and exposes things like platform, distribution, service pack level, and language.
  • sys.dm_os_sys_info was expanded, now revealing CPU info (socket count, core count, and cores per socket).

DBCC CLONEDATABASE improvements

Some additional functionality was added to the DBCC CLONEDATABASE command – it now supports Full-Text Search, and will force a flush of Query Store statistics so that you capture all of them in the clone.

Identity Caching

ALTER DATABASE SCOPED CONFIGURATION now supports an IDENTITY_CACHE option – setting this to OFF disables IDENTITY caching, which can help eliminate gaps due to events like failovers and restarts. This setting (which replace server-level trace flag 272) can have a performance impact, so you should definitely test how it affects your workload; also, remember that gaps in IDENTITY columns can still happen due to things like deletes and rollbacks.

Resumable online index rebuild (Enterprise only)

In SQL Server 2017 you can now spread your index rebuild operations across multiple maintenance windows, and gracefully recover from an interruption, such as a disconnection or failover. There are already two tips explaining this feature in detail: SQL Server 2017 Resumable Online Index Rebuilds and More on Resumable Index Rebuilds in SQL Server 2017.

Performance

Showplan, Query Store, and Statistics improvements

SQL Server 2017 introduces a handful of changes to the way queries and statistics are collected and displayed.

Adaptive query processing

This “feature” really is an umbrella over multiple changes to query processing that can occur during execution to improve performance (documented here):

  • Batch Mode Adaptive Joins
    This feature tries to defeat parameter sniffing by enabling the query processor to change batch mode join operators between hash join and nested loop join, depending on row thresholds. In effect, this acts like a recompile, but is far more granular. You can see more details in Joe Sack’s blog post and Itzik Ben-Gan’s SQLMag article.
  • Adaptive Memory Grant Feedback
    In previous versions, the memory grant calculated before the query started was the memory grant you received – no more, no less. If you needed more, you spilled to disk; if you needed less, what you didn’t need was wasted (and potentially hampered concurrency). Now, in certain scenarios involving batch mode operators, the memory grant will be recalculated if the actual is “different enough.” More details in this tip and this blog post.
  • Interleaved Execution for Multi-Statement Table Valued Functions
    This feature essentially allows the optimizer to derive cardinalities for the results of MSTVFs before incorporating those estimates into the rest of the plan, resulting in much more appropriate optimization. (In previous versions, those estimates were fixed at 100 or, if you go back far enough, 1.) For more details, see this tip and Joe Sack’s blog post.

Note that while the interleaved execution enhancement works on other editions, batch mode adaptive joins and memory grant feedback are Enterprise only.

Automatic tuning

This feature allows you to set SQL Server to observe query plan regressions and automatically force a better, previous plan. You can, of course, set it up to observe and manually make the changes yourself (using the new DMV sys.dm_db_tuning_recommendations). The feature is documented here, and Microsoft’s Jovan Popovic has blogged about it in great detail here, here, and here.

Under the covers

There are not really features, per se, but under-the-covers performance improvements you get for free:

  • Improvements to build times for non-clustered indexes on memory-optimized tables during database recovery.
  • Smarter use of buffer pool for backups of small databases on servers with sufficient memory.
  • Optimizations for indirect checkpoint.
  • Better performance of ALTER TABLE against memory-optimized tables.
  • Transaction logo redo now happens in parallel.

T-SQL

There are several T-SQL enhancements offered up in this version:

  • SELECT INTO … ON FILEGROUP
    You can now specify the target filegroup for a SELECT INTO command.
  • CONCAT_WS
    This works like CONCAT, but allows you to specify a separator (WS = “with separator”).
  • TRANSLATE
    This is more like a nested REPLACE() command – allowing you to replace opening and closing parentheses in one operation, for example.
  • TRIM
    This one is pretty simple – it merely combines LTRIM() and RTRIM() to save a few characters.
  • STRING_AGG
    This aggregate function now supports WITHIN GROUP (based on Itzik Ben-Gan’s Connect item), which allows you to define ordering of the concatenated output – including based on columns not in the output.
  • BULK INSERT / OPENROWSET
    Enhancements here to support FORMAT = 'CSV' as direct file source, FIELDQUOTE to specify a quote character other than double-quote, and the ability to use Azure Blob Storage for source, format, and error output files.

Analytics and Data Science

SQL Server 2017 offers two main improvements here.

In-Memory OLTP

SQL Server’s story around memory-optimized tables keeps getting better. Enhancements in this release that bring it closer to being a first-class citizen:

  • Computed columns (and indexes on those columns) are now supported.
  • CASE expressions, CROSS APPLY, and TOP (N) WITH TIES now supported in natively compiled modules.
  • JSON commands are now fully supported in both check constraints and in natively compiled modules.
  • The system procedure sp_spaceused now properly reports space for memory-optimized tables.
  • The system procedure sp_rename now works on in-memory tables and natively compiled modules.
  • There is no longer a limitation of 8 indexes on memory-optimized tables.
  • Memory-optimized filegroup files can now be stored on Azure Storage.

Columnstore

Columnstore functionality also got some under-the-hood enhancements, but the more visible changes are online build/rebuild (Enterprise only) and that LOB columns are now supported.

Security

Next Steps

There are a lot of changes in SQL Server 2017 that could help your implementation, and a wealth of information out there to help you get more information on the pieces that are most interesting to you. See the following tips and other resources:



Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, October 17, 2017 - 9:39:17 AM - Suresh Thatikonda Back To Top

 

 Hi Sir,

 

I installed SSRS-2017 services, but it takes by deafault name as instancename\SSRS. So how to change the name of the instance. And how to install more than one SSRS2017 instance in one windows server, if I am trying to install  then I got 3 options

1.Uninstall (existing 2017 instance)

2.Upgrade (existing 2017 instance)

3.Repair (existing 2017 instance)

 

Could you please assist me for this. If you have any concern please send a mail to : Suresh.tkonda@gmail.com

 

Thanks & Regards,

Suresh.

 


Wednesday, October 04, 2017 - 1:53:30 PM - JRStern Back To Top

Thanks Aaron, great post.  Hey I wouldn't say "defeat parameter sniffing", more like "extend" as a more dynamic mode of optimization.


Wednesday, October 04, 2017 - 1:55:10 AM - khalil Back To Top

 Wonderful article, Thanks for sharing.

Regards

Khalil

 


Learn more about SQL Server tools