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.

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. 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. 
• 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. 

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 . 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 . These features are now both supported; more information about retention policies can be found . 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. 

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 :

• 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. 
• 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.” 
• 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.) 

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 DMVsys.dm_db_tuning_recommendations). 

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. 
  • How to setup Machine Learning Services in SQL Server 2017 CTP2
  • Develop machine learning models for predictive analysis in SQL Server 2017
    
• 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.

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.