Upgrading SQL Server– Useful Improvements in SQL Server 2017

Even though much of the development effort and resources in SQL Server 2017 has been used to get SQL Server running as a first-class citizen on Linux, there are some interesting new features and improvements in SQL Server 2017 regardless of what operating system you are going to use.

One example are changes in the limits for tempdb initial file size for both tempdb data and log files. In SQL Server 2017, you can set the initial file size as large as 256GB (262,144MB) per file. If you set it to a size larger than 1GB and instant file initialization (IFI) is not enabled, you will get a warning during setup. Keep in mind that IFI only applies to SQL Server data files.

SQL Server 2017 also has a number of new DMV and DMFs that make it easier to manage and monitor your system. One example is sys.dm_db_log_info, which returns the virtual log file (VLF) count and other useful information about your VLFs. This is a more powerful replacement for the old DBCC LOGINFO command.

Another example (which I might be somewhat responsible for, due to much lobbying) are new columns in sys.dm_os_sys_info that reveal very useful processor information, including socket_count, core_count, and cores_per_socket. Getting this information form this DMV is much better that reading the SQL Server Error Log to to to find some of this information.

There are also nice improvements related to doing “smart” differential backups. The sys.dm_db_file_space_usage DMV has a new column, modified_extent_page_count which lets you track differential changes since the last full database backup in order to help decide whether it will be more efficient to take a full database backup instead of a differential backup.

For transaction log backups, there is a new DMF sys.dm_db_log_stats, which has a column called log _since_last_log_backup_mb. By checking the value of this column, you can programmatically decide when to take transaction log backups based on the amount of transaction log activity rather than just on time. This will let you take transaction log backups more frequently during periods of high activity (which can reduce how large the transaction log backup file is and also minimize transaction log file growth. It will also let you take transaction log backups les frequently during periods of low activity, which will reduce the number of transaction log backups that need to be restored, and possibly reduce your RTO times.

Small improvements like this, which are not always well publicized can really make your job easier as a DBA, which is yet another reason to push for an upgrade to SQL Server 2017.


Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017. You can use this code: BL150GG to register for the PASS 2017 Summit to get a $150 discount.

Here is a link to the complete series about upgrading SQL Server.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.