SQL Server 2017
became General Availability (GA) on 10/2, I've been sharing through the
different social media channels the different features and enhancements
included in this new version, however I want to consolidate in this posts most
of those that you will be seeing in most of the production scenarios with an
explanation of each one.
- Resumable online index rebuild resumes an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space), or pauses and later resumes an online index rebuild operation. See ALTER INDEX and Guidelines for online index operations. This is a great improvement specially with index operations where you run out of space or have to pause it due to performance degradation, you can later resume where you left it instead of having to cancel, wait the rollback and then start from the beginning.
- A new generation of query processing improvements that will adapt optimization strategies to your application workload’s runtime conditions. For this first version of the adaptive query processing feature family, we have three new improvements: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions. See Adaptive query processing in SQL databases.
- Automatic tuning is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems. Automatic tuning in SQL Server, notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems. This feature in particular is one of the few Azure first, you can see this included in the Azure SQL database since at least V12 (current), for more reference See Automatic tuning.
- sys.dm_os_sys_info has three new columns: socket_count, cores_per_socket, numa_node_count. This in particular helps you check if your current setup exceeds NUMA boundaries, in the case you exceed it, it will lead to an overcommitted host and in the end will cause you performance issues.
- A new column modified_extent_page_count, is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database. The new column modified_extent_page_count allows you to build smart backup solution, which performs differential backup if percentage changed pages in the database is below a threshold (say 70-80%) else perform full database backup.
- SELECT INTO … ON FileGroup - SELECT INTO now supports loading a table into a filegroup other than a default filegroup of the user using the ON keyword support added in SELECT INTO TSQL syntax. This feature in particular will help when you need to run the first step of an archive solution or if you create a "troubleshoot filegroup" so you move the data directly to it instead of you PRIMARY filegroup where you might be troubleshooting a performance issue or anything else and you want to isolate the problem. See SELECT INTO.
- A new DMF sys.dm_db_log_info is introduced to expose the VLF information similar to DBCC LOGINFO to monitor, alert, and avert potential transaction log issues caused due to number of VLFs, VLF size or shrink file issues experienced by customers. This dmv helps you to analyze the current health of your transaction log file that can become fragmented due to either an incorrect design or that the current management of your log is not the adequate. Pro Tip: If you have a large number of VLF's that could be one of the reasons why your replications are failing or some other CRUD operations are performing poorly, so go and check this number.
- Cluster-less Availability Groups support added. Remember this post (Introduction to SQL Server Always On) where I explained the Key components of Always on, with SQL 2017 Windows Failover Clustering (WSFC) is no longer needed so you can enable the Always On feature even if the SQL Server is not part of a Windows cluster.
- Linux. Did I mention SQL 2017 can run in Linux? Well, this new version is fully supported either in Windows or Linux
The items described
above are related with the most frequently used with the database engine but
there are more, also in this post I'm not reviewing features such as SSIS,
SSAS, SSRS or Machine Learning and in this last one Microsoft made a lot of
improvements with it.
There are templates
already ready in Azure for you to go an create VM's with it or if you like to
create containers, there is one in Docker already with Linux (Ubuntu 16.04)and
SQL 2017 (https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker)
For a complete list
of the new features you can see this sites:
No comments:
Post a Comment