SP2 for SQL Server 2016 is available with new helpful DMVs

Last month (April 24, 2018), the Service Pack 2 for SQL Server 2016 was released and distributed.
This Service Pack has new DMVs, already available in SQL Server 2017 RTM.

In this article, I will just write few words about 2 DMVs (sys.dm_db_log_stats & sys.dm_db_log_info) and a new column (modified_extent_page_count) in the DMV sys.dm_db_file_space_usage that I presented during our last event about SQL Server 2017. I think they are really helpful for DBA.
It’s also the opportunity to present you the demo that I create for our Event.

Preparation

First, I create the database smart_backup_2016 and a table Herge_Heros

CREATE DATABASE [smart_backup_2016]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'smart_backup_2016', FILENAME = N'G:\MSSQL\Data\smart_backup_2016.mdf' )
 LOG ON
( NAME = N'smart_backup_2016_log', FILENAME = N'G:\MSSQL\Log\smart_backup_2016_log.ldf' )
GO

USE smart_backup_2016
GO

CREATE TABLE [dbo].[Herge_Heros]
   (
   [ID] [int] NULL,
   [Name] [nchar](10) NULL
   ) ON [PRIMARY]
GO

I do a little insert and run a first Full and a first TLog Backup

INSERT INTO [Herge_Heros] VALUES(1,'Tintin') -- Tim
INSERT INTO [Herge_Heros] VALUES(2,'Milou') -- Struppi


BACKUP DATABASE [smart_backup_2016] TO  DISK = N'C:\Temp\smart_backup.bak' WITH NOFORMAT, NOINIT,  NAME = N'smart_backup-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
BACKUP Log [smart_backup_2016] TO  DISK = N'C:\Temp\smart_backup.log' WITH NOFORMAT, NOINIT,  NAME = N'smart_backup-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

After, I insert a lot of line to have more than 50% modified pages

INSERT INTO [Herge_Heros] VALUES(3,'Quick') --Strups
INSERT INTO [Herge_Heros] VALUES(4,'Flupke')  --Stepppke
GO 100000

Now, the demo is ready!

new column modified_extent_page_count in sys.dm_db_file_space_usage

As you can see in this screenshot, the column is really existing in SQL Server 2016 SP2 (13.0.5026.0).
After, you can, like us in our DMK maintenance, create an adapted Backup Strategy depending from changes and no more depending from the time.
In this stored procedure, if the modified pages are greater than 50% of the total pages, it will do a Full Backup and if the modified pages are less than 50%, it will do a Differential Backup.

USE [dbi_tools]
GO

CREATE or ALTER PROCEDURE [maintenance].[dbi_smart_backup] @database_name sysname
as
DECLARE @pages_changes Numeric(10,0)
DECLARE @full_backup_threshold INT
DECLARE @diff_backup_threshold INT
DECLARE @sql_query nvarchar(max)
DECLARE @page_change_text nvarchar(20)
DECLARE @param nvarchar(50)
DECLARE @backupfile nvarchar(2000)
SET @full_backup_threshold=50
SET @diff_backup_threshold=0
SET @param = N'@pages_changesOUT nvarchar(20) OUTPUT'
SET @sql_query =N'SELECT @pages_changesOUT=( 100 * Sum(modified_extent_page_count) / Sum(total_page_count) ) FROM ['+@database_name+'].sys.dm_db_file_space_usage'

EXECUTE sp_executesql @sql_query,@param ,@pages_changesOUT=@page_change_text OUTPUT; 
SET @pages_changes = CAST(@page_change_text AS Numeric(10,0)) 
IF @pages_changes > @full_backup_threshold
  BEGIN
     --Full Backup threshold exceeded, take a full backup
     Print 'Full Backup Threshold exceeded, take a full backup'
     SET @backupfile = N'C:\Temp\'+@database_name+N'_' + replace(convert(nvarchar(50), GETDATE(), 120), ':','_') + N'.bak'
   BACKUP DATABASE @database_name TO DISK=@backupfile
  END
  ELSE
  BEGIN
	   IF @pages_changes >= @diff_backup_threshold
		BEGIN
			-- Diff Backup threshold exceeded, take a differential backup
			Print 'Diff Backup threshold exceeded, take a differential backup'
			SET @backupfile = N'C:\Temp\'+@database_name+N'_' + replace(convert(nvarchar(50), GETDATE(), 120), ':','_') + N'.dif'
			BACKUP DATABASE @database_name TO DISK=@backupfile WITH differential
		END
	ELSE
		BEGIN
			-- No threshold exceeded, No backup
		PRINT 'No threshold exceeded, No backup'   
		END
  END
GO

Now, I run the stored procedure [maintenance].[dbi_smart_backup] in the dbi_tool

USE smart_backup_2016;
GO
EXEC [dbi_tools].[maintenance].[dbi_smart_backup] @database_name = N'smart_backup_2016'

The dbi backup Stored Procedure in this case do a Full Backup because the modified pages are 64%.
I check the status of the modified pages and the modified pages are at 5%.

If I restart the stored procedure, I do a differential backup.

My backup strategy is really adapted to the change of pages in the database and no more based on the time (RTO vs RPO).
Let’s go to the new DMV sys.dm_db_log_stats do to the same with the TLog backup.

DMV sys.dm_db_log_stats

This DMV gives really good information about the transaction log files and can help to adapt the backup strategy and also control the growth of the file.
The DMV is very easy to use and for example, if you want to have the growth of the size since the last TLog backup, use the column log_since_last_log_backup_mb

SELECT log_since_last_log_backup_mb from sys.dm_db_log_stats(DB_ID('smart_backup_2016'))
GO

Like below, I create in our DMK maintenance an adapted TLOG Backup [dbi_smart_tlog_backup]

If the TLOG is growing more that 5 MB from the last TLOG backup, It will do a TLOG Backup and if not, no TLOG Backup.
In my example, the growth is 548 MB, then a TLOG Backup is necessary.

After, I control the size and as you can see the size since last TLOG Backup is 0.07MB

As you can see, no TLOG backup… My backup strategy is adapted to the load! 😉

DMV sys.dm_db_log_info

This DMV will help us to have all VLF(Virtual Log File) information and no more using the DBCC Loginfo.
You can use this DMV very easily like this:

SELECT [name] AS 'Database Name', COUNT(l.database_id) AS 'VLF Count'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]

These DMVs are very helpful and it is a good thing to have it also in SQL Server 2016 now.