SQL Server 2017 Hidden Gems – Part 1
In this, the first in a series of blogs, we will be covering salient new features and enhancements in Microsoft SQL Server 2017.
With SQL Server 2017 CTP 2.0 release, 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. This feature was requested by SQL Server user groups.
With this feature, now you can configure your differential backup with an extra piece of code to determine the percentage of pages changed since the last full backup. With large changes in the database, the time to complete differential backup is similar to the time taken for a full database backup. Hence, there is no real benefit in taking a differential backup, which can instead increase the restore time of database.