By: Aaron Bertrand | Comments (3) | Related: > SQL Server 2017
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.
- A new DMV, sys.dm_exec_query_statistics_xml, allows you to correlate sessions to plans, as long as query profiling is enabled (it works with both standard and lightweight profiling).
- Showplan XML now includes info about the statistics used for a plan and, for actual plans, runtime metrics and the top 10 wait stats experienced by that plan (well, with some exceptions). These wait stats are also now being tracked in Query Store.
- At least one other waits/showplan change that hasn’t been announced yet (stay tuned).
- A new dynamic management function, sys.dm_db_stats_histogram, allows you to access histogram information programmatically, without DBCC.
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.
- With new machine learning capabilities, there is a new command called PREDICT, which allows for predictive analysis. There is a Microsoft post about this here, and two earlier tips about it:
- Graph database has also been added – I won’t pretend to know anything about this, but you can create Node and Edge tables, and then use predicates like MATCH for pattern matching. See the following posts for more information:
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
- You can now grant/deny/revoke permissions on DATABASE SCOPED CREDENTIAL – such as ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION.
- ADMINISTER DATABASE BULK OPERATIONS is now exposed by sys.fn_builtin_permissions.
- Enhanced security of CLR (using the new “clr strict security” option of sp_configure); you can whitelist CLR entities that are required for your application.
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:
- All SQL Server 2017 Tips
- All SQL Server on Linux Tips
- All SQL Server Team blog posts relating to SQL Server 2017
- Microsoft for the Modern Data Estate (2017 GA announcement from Rohan Kumar)
- SQL Server community-driven enhancements in SQL Server 2017
- “Everything you need to know about SQL Server 2017” (video)
- What’s new in SQL Server 2017
- What’s new in Database Engine – SQL Server 2017
- What’s new for SQL Server 2017 on Linux
- Editions and supported features of SQL Server 2017: Windows | Linux
- Unsupported features and services in SQL Server 2017 on Linux
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
