本文探讨了SQL Server 2017中如何通过智能差异备份和智能事务日志备份来提高备份性能和管理效率。讨论了检查点的作用，以及SQL Server 2017在差异备份和事务日志备份方面的改进，包括新的DMV和DMF，以更高效地管理备份。
So far, we’ve discussed several phases of backup that starts with planning, creating, strategizing and implementing. In this article, we are going to see how database administrators can define the strategy to improve backup performance and efficiently manage backups in SQL Server 2017. The following are the topics of discussion:
到目前为止，我们已经讨论了备份的几个阶段，这些阶段从计划，创建，制定战略和实施开始。 在本文中，我们将了解数据库管理员如何定义策略来提高备份性能并有效管理SQL Server 2017中的备份。以下是讨论的主题：
- Discuss checkpoints 讨论检查点
- sys.dm_db_file_space_usagesys.dm_db_file_space_usage中
- sys.dm_db_log_stats sys.dm_db_log_stats
- Understand the functioning of smart differential backup and its internals 了解智能差异备份的功能及其内部
- Understand the Smart transaction log backup process and its internals 了解智能事务日志备份过程及其内部
- T-SQL scripts T-SQL脚本
- And more… 和更多…
背景 (Background)
A checkpoint is a background process that writes dirty pages to disk. A checkpoint performs a full scan of the pages in the buffer pool, lists all the dirty pages that are yet to be written to the disk, and finally writes those pages to the disk. In SQL instances that do not have many dirty pages in the buffer pool, this is a trivial operation. However, with the SQL instances that have OLTP databases, use more memory and/or involve sequential scanning of all pages, the performance of the system could be impacted.
检查点是将脏页写入磁盘的后台进程。 一个检查点将对缓冲池中的页面进行全面扫描，列出所有尚未写入磁盘的脏页面，最后将这些页面写入磁盘。 在缓冲池中没有很多脏页SQL实例中，这是微不足道的操作。 但是，对于具有OLTP数据库，使用更多内存和/或涉及对所有页面进行顺序扫描SQL实例，可能会影响系统的性能。
With SQL Server 2012, indirect checkpoints were introduced. In this case, the dirty page manager manages the dirty page list and generally keeps tracks of the all the dirty pages modifiers of the database. By default, it runs every 60 seconds and tracks the dirty pages that need to be flushed.
在SQL Server 2012中，引入了间接检查点 。 在这种情况下，脏页管理器将管理脏页列表，并通常跟踪数据库的所有脏页修饰符。 默认情况下，它每60秒运行一次，并跟踪需要刷新的脏页。
For example, if an indirect checkpoint is set to 120 seconds then individual pages would be flushed around 120 seconds after that they were made dirty. This will impact I/O. I/O also depends on the number of dirty modifiers lists of the database; we may even see a significant spike in the set target interval and see small hits as the pages change. This eliminates overhead to tracing and flushing the dirty pages which results in significant improvement in backup performance. To review further, go through the reference section below to understand more about the checkpoint and backup performance improvement process in detail.
例如，如果将间接检查点设置为120秒，则将各个页面变脏后大约120秒将被刷新。 这将影响I / O。 I / O还取决于数据库的脏修饰符列表的数量。 我们甚至可能会看到设定的目标时间间隔出现明显的峰值，并且随着页面的变化点击率也会降低。 这消除了跟踪和刷新脏页的开销，从而显着提高了备份性能。 要进一步查看，请仔细阅读下面的参考部分以详细了解检查点和备份性能改进过程。
On a database where an automatic checkpoint mode is set, tracing of the entire unit of the buffer pool is necessary to evaluate for possible dirty pages. On the other hand, with an indirect checkpoint, only those pages of the buffer pool that are dirtied have to be dealt with.
在设置了自动检查点模式的数据库上，必须跟踪整个缓冲池单元，以评估可能的脏页。 另一方面，使用间接检查点，只需要处理缓冲池中脏了的那些页面。
For example, let’s consider a system with 2 TB of memory that contains ~250 million buffer units and has 100 dirty pages. Assume that it requires 5 CPU cycles to trace and identify the status of each buffer unit. To traverse 250 million BUFs, it would require 1.25 trillion CPU cycles—and to what? Simply to find 100 positive dirty pages in the automatic checkpoint mode! However, 100 dirty pages are hardly anything with the indirect checkpoint mode. The entire buffer pool scanning time is eliminated.
例如，让我们考虑一个具有2 TB内存的系统，其中包含约2.5亿个缓冲区单元，并具有100个脏页。 假设需要5个CPU周期来跟踪和识别每个缓冲单元的状态。 要遍历2.5亿个BUF，将需要1.25万亿个CPU周期，这又是什么呢？ 只需在自动检查点模式下查找100个肯定的脏页！ 但是，使用间接检查点模式，几乎没有100个脏页。 消除了整个缓冲池扫描时间。
智能差异备份 (Smart Differential Backup)
Before the advent of SQL Server 2017, to understand how the extents are altered since the last full backup used to be done by querying the DBCC PAGE command. The Differential Changed Map (DCM) is a bitmap pointer referenced by SQL Server to trace the modified pages since the last full backup. It is stored in DCM pages, one per GAM interval. A differential backup process will identify those pages with CHANGED status in the DCM. There are usually three additional extents included in a differential backup.
在SQL Server 2017问世之前，通过查询DBCC PAGE命令来了解自上次执行完整备份以来如何更改范围。 差异更改映射（DCM）是SQL Server引用的位图指针，用于跟踪自上次完整备份以来的修改页。 它存储在DCM页中，每个GAM间隔存储一个。 差异备份过程将识别DCM中状态为“已更改”的那些页面。 差异备份中通常还包含三个附加盘区。
It’s demo time
演示时间到了
SQLShackAuthorDS is a sample table created with two columns, id and authorname.
SQLShackAuthorDS是由两列id和authorname创建的示例表。
USE SQLShackDemoSBKP
GO
--Create the sample table
CREATE TABLE [dbo].[SQLShackAuthorList](
[ID] [int] IDENTITY(1,1) NOT NULL,
[authorName] [varchar](8000) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
GO
--insert 1000 dummy data
INSERT INTO SQLShackAuthorList values('SQL Shack Prashanth')
GO 1000
--select the sample data
select top 5 * From SQLShackAuthorList
--Backup the database
BACKUP DATABASE SQLShackDemoSBKP TO DISK = 'F:\PowerSQL\SQLShackDemoSBKP.bak' WITH INIT
-- List data and index pages allocated to the SQLShackAuthorsDS table
DBCC IND('SQLShackDSDemo',SQLShackAuthorList,-1)
GO
--set the trace flag
DBCC TRACEON(3604)
DBCC PAGE('SQLShackDSDemo',1,6,3) WITH TABLERESULTS
GO
DBCC PAGE arguments:
DBCC PAGE参数：
- Database name or database id 数据库名称或数据库ID
- Page file ID: 1 页面文件ID：1
- Page Number: 6; to retrieve DCM page 页码：6； 检索DCM页面
- Output: 3; retrieve page header and details of every page details for the data interpretation 输出：3; 检索页面标题和每个页面的详细信息以进行数据解释
In the DIFF_MAP, we can see the status of DCM pages. The output lists the modified extents since the last full backup. There are currently five modified extents listed—1:0, 1:8, 1:16 1:64, 1:152
在DIFF_MAP中，我们可以看到DCM页面的状态。 输出列出自上次完整备份以来的已修改范围。 当前列出了五个修改的扩展范围-1：0、1：8、1：16 1：64、1：152
Now, let’s go ahead and update the SQLShackAuthorList table with some dummy values for the ids 40,50,60,70 and look for the modified extents
现在，让我们继续，使用ID 40、50、60、70的一些虚拟值更新SQLShackAuthorList表，并查找修改的范围
update SQLShackAuthorList
SET authorName='SQLShack Prashan Jayaram'
where id in(40,50,60,70)
DBCC PAGE('SQLShackDSDemo',1,6,3) WITH TABLERESULTS
We can see that modified extents listed are 1:0, 1:8, 1:16, 1:24, 1:40, 1:64, 1:128, 1:52, and 1:304. Total of 9 extents are modified since the last backup.
我们可以看到列出的修改范围是1：0、1：8、1：16、1：24、1：40、1：64、1：128、1：52和1：304。 自上次备份以来，总共修改了9个盘区。
Let’s now initiate a differential backup
现在开始差异备份
BACKUP DATABASE SQLShackDemoSBKP TO DISK = 'F:\PowerSQL\SQLShackDSDemo_Diff_1.bak' WITH DIFFERENTIAL;
Let’s perform the insert operation and identify the modified extents
让我们执行插入操作并识别修改后的范围
Insert into SQLShackAuthorList values('Prashanth Jayaram'+ CAST(NEWID() AS VARCHAR(MAX)))
GO 100
DBCC PAGE('SQLShackDSDemo',1,6,3) WITH TABLERESULTS
In the output, the listed modified extents are 1:0, 1:8, 1:16, 1:24, 1:40, 1:64, 1:128, 1:36, 1:52, 1:160, 1:68, 1:192, and 1:304. A total of 13 extents are modified since the last full backup.
在输出中，列出的修改范围是1：0、1：8、1：16、1：24、1：40、1：64、1：128、1：36、1：52、1：160、1 ：68、1：192和1：304。 自上次完整备份以来，总共修改了13个盘区。
Again, let’s initiate a differential backup. As we can see, there is a total of 105 pages processed during the backup.
同样，让我们启动差异备份。 我们可以看到，备份期间总共处理了105页。
BACKUP DATABASE SQLShackDemoSBKP TO DISK = 'F:\PowerSQL\SQLShackDSDemo_Diff_2.bak' WITH DIFFERENTIAL;
It looks like SQL Server adds three more additional extents than the listed extents in the DCM i.e. 98 pages were processed during the 1st differential backup and 4 extents were modified in the process of the update-and-insert operation. In total, 98+4+3=105 pages, as you can see in the above output. You can reset the DCM by initiating a FULL backup.
看起来SQL Server比DCM中列出的扩展区增加了三个额外的扩展区，即在第一次差异备份期间处理了98个页面，并在更新和插入操作的过程中修改了四个扩展区。 如上输出所示，总共有98 + 4 + 3 = 105页。 您可以通过启动完整备份来重置DCM。
This is a hard way to estimate and identify the modified extents prior to SQL Server 2017.
这是在SQL Server 2017之前估算和识别已修改范围的很难方法。
Let’s walk through the details of SQL Server 2017 DMV’s and DMF’s which facilitates an extra column to measure the modified extents in a simpler way.
让我们详细介绍一下SQL Server 2017 DMV和DMF的详细信息，它们有助于增加一列以更简单的方式测量修改后的范围。
The DMV sys.dm_db_file_space_usage has a new field called modified_extent_page_count. This column tells us how many pages were changed since the last full backup. The modified page count can be used to track differential changes in the database since the last full backup to decide if a differential backup is beneficial.
该DMV sys.dm_db_file_space_usage有一个名为modified_extent_page_count新领域。 此列告诉我们自上次完整备份以来已更改了多少页。 自上次完整备份以来，修改后的页数可用于跟踪数据库中的差异更改，以确定差异备份是否有益。
Now, run the following SQL to check how many pages were modified
现在，运行以下SQL检查已修改了多少页
SELECT file_id,
total_page_count,
modified_extent_page_count,
(modified_extent_page_count *100) / total_page_count [% Change]
FROM
sys.dm_db_file_space_usage
Let’s run the update-and-insert SQL statement and then measure the modified pages
让我们运行“更新并插入” SQL语句，然后测量修改后的页面
UPDATE SQLShackAuthorList
SET authorName='SQLShack Prashanth Jayaram'
insert into SQLShackAuthorList values('SQL Shack Prashanth Jayaram'+ CAST(NEWID() AS VARCHAR(MAX)))
GO 100000
Let’s now check the modified pages by querying sys.dm_db_file_space_usage
现在，通过查询sys.dm_db_file_space_usage检查修改后的页面
We can see that there is a 28% change in the database. This information is very helpful and an important factor in deciding whether to initiate Full or a differential database backup.
我们可以看到数据库中有28％的变化。 该信息非常有用，并且是决定启动完整数据库备份还是差异数据库备份的重要因素。
Let’s prepare the SQL
让我们准备SQL
The script can be saved as a SQL file and executed over multiple instances using the sqlcmd command.
可以将脚本另存为SQL文件，并使用sqlcmd命令在多个实例上执行。
- Variable declaration, the @fullthreshold and @diffthreshold variables require initial values, which are used to compare and initiate the full or differential backup 变量声明中，@ fullthreshold和@diffthreshold变量需要初始值，该初始值用于比较和启动完全或差异备份
- Verify the SQL Server instance version 验证SQL Server实例版本
- Define a cursor to hold a list of active databases in a given instance 定义一个游标以保存给定实例中的活动数据库列表
- Loop through every database 遍历每个数据库
- Calculate the pages that have changed since last full backup and assign the value to a variable 计算自上次完整备份以来已更改的页面，并将值分配给变量
- Prepare a dynamic SQL to compare and initiate a full or differential backup 准备动态SQL以比较并启动完整或差异备份
- Reset the cursor variables 重置游标变量
--variable declaration
DECLARE
@ver nvarchar(128),
@fullthreshold int=80,
@Diffthreshold int=35,
@changes numeric(5,2),
@sql nvarchar(max),
@sSQL nvarchar(max),
@rval int,
@ParmDefinition nvarchar(max),
@dbname NVARCHAR(100),
@fPath NVARCHAR(256) ,
@fName NVARCHAR(256) ,
@Date VARCHAR(20) ,
@value varchar(10)
--Checking the SQL Server versioni using serverproperty
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
SET @ParmDefinition = N'@retvalOUT int OUTPUT';
IF ( @ver = '14' )
BEGIN
-- backup directory
SET @fpath = 'F:\PowerSQL\SmartBackup\'
-- filename format
SELECT @Date = CONVERT(VARCHAR(20),GETDATE(),112)
--Declare the cursor
DECLARE database_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') and state_Desc='ONLINE'
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fName = @fpath + @dbname + '_' + @date
--Calculate the pages that are modified since last full backup and assign it to a variable
set @SQL='SELECT @retvalOUT=CAST(ROUND((SUM(modified_extent_page_count)*100.0)/SUM(allocated_extent_page_count),2) as decimal(10,2))
from '+@dbname+'.sys.dm_db_file_space_usage'
EXEC sp_executesql @SQL, @ParmDefinition, @retvalOUT=@rval OUTPUT;
SELECT @rval;
--Initiate FULL or differential backup based on the defined threshold values
SET @sSQL= 'IF ('+ cast(@rval as varchar(10))+'>' +cast(@fullthreshold as varchar(10))+')
BACKUP DATABASE '+@dbname +' TO DISK ='''+ @fName+'_full.bak''' +CHAR(13)
+'ELSE'+CHAR(13)+'
BEGIN
IF ('+ cast(@rval as varchar(10))+'>' +cast(@Diffthreshold as varchar(10))+' AND ' +cast(@rval as varchar(10))+'<'+ cast(@fullthreshold as varchar(10)) +')'+char(13)
+'BACKUP DATABASE '+@dbname +' TO DISK ='''+ @fName+'_Diff.bak'''+' WITH DIFFERENTIAL'+CHAR(13)+
'END'
--Print the SQL
print @sSQL
--Execute the dynamic SQL
EXEC sp_executesql @sSQL
FETCH NEXT FROM database_cursor INTO @dbname
END
CLOSE database_cursor
DEALLOCATE database_cursor
END
In the following output, we can see that the 48% of the database TestSQLShackDemo has changed and 70% of SQLShackDemoSBKP has changed. This results in initiating a differential backup.
在以下输出中，我们可以看到数据库TestSQLShackDemo的48％已更改，而SQLShackDemoSBKP的70％已更改。 这导致启动差异备份。
SQL Server 2017中的事务日志备份 (Transaction log backups in SQL Server 2017)
In most production environments, setting the right auto-growth value is a challenging and even daunting task. Understanding the VLFs and managing T-log backups is another area where database administrators spend a lot of time analyzing database growth and transaction rates of the database in order to configure the database to perform at an optimal level.
在大多数生产环境中，设置正确的自动增长值是一项充满挑战甚至艰巨的任务。 了解VLF和管理T-log备份是数据库管理员需要花大量时间分析数据库的增长和数据库的事务速率以配置数据库以使其达到最佳性能的另一个领域。
SQL Server 2017 introduces an enhancement to sys.dm_db_log_stats dynamic management function. The function has an output column called log_since_last_log_backup_mb, which has the value that denotes the amount of log data generated since the last transaction log backup. We can develop a procedure based on the value of this column and efficiently use it to initiate a transaction log backup. This facilitates maintaining a consistent log backup size and prevents abrupt database auto-growth due to transactional outbursts. It also helps database administrators to effectively manage the space issues, thereby speeding up the database recovery process.
SQL Server 2017对sys.dm_db_log_stats动态管理功能进行了增强。 该函数具有一个名为log_since_last_log_backup_mb的输出列，该列的值表示自上次事务日志备份以来生成的日志数据量。 我们可以根据此列的值开发一个过程，并有效地使用它来启动事务日志备份。 这有助于保持一致的日志备份大小，并防止由于事务爆发而导致的数据库自动突然增长。 它还可以帮助数据库管理员有效地管理空间问题，从而加快数据库恢复过程。
I recommend reading the article, Transaction Log Physical Architecture to get more insight into the internals of the transaction log file.
我建议阅读文章“ 事务日志物理体系结构”，以更深入地了解事务日志文件的内部结构。
SELECT
name AS 'DatabaseName',
total_vlf_count AS 'VLFcount' ,
log_since_last_log_backup_mb
FROM
sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
where
s.state_Desc='ONLINE'
Let’s initiate a transactional log backup for a specific database that has generated a log of 10 -20 MB. In the above output, we can see that SQLShackDemoSBKP has generated 12 MB log data.
让我们为已生成10 -20 MB日志的特定数据库启动事务日志备份。 在上面的输出中，我们可以看到SQLShackDemoSBKP已生成12 MB日志数据。
--variable declaration
DECLARE
@ver nvarchar(128),
@logthreshold int=10,
@log_since_last_log_backup_mb decimal(10,2),
@sql nvarchar(max),
@sSQL nvarchar(max),
@rval int,
@ParmDefinition nvarchar(max),
@dbname NVARCHAR(100),
@fPath NVARCHAR(256) ,
@fName NVARCHAR(256) ,
@Date VARCHAR(20) ,
@value varchar(10)
--Checking the SQL Server versioni using serverproperty
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
SET @ParmDefinition = N'@retvalOUT int OUTPUT';
IF ( @ver = '14' )
BEGIN
-- backup directory
SET @fpath = 'F:\PowerSQL\SmartBackup\'
-- filename format
SELECT @Date = CONVERT(VARCHAR(20),GETDATE(),112)
--Declare the cursor
DECLARE database_cursor CURSOR READ_ONLY FOR
SELECT
name,
convert(decimal(10,2),log_since_last_log_backup_mb)
FROM
sys.databases AS s
CROSS APPLY sys.dm_db_log_stats(s.database_id)
where
s.state_Desc='ONLINE' and recovery_model_desc='FULL' and name IN ('SQLShackDemoSBKP','SQLShackTailLogDB_Test')
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @dbname,@log_since_last_log_backup_mb
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fName = @fpath + @dbname + '_' + @date
--Initiate Log backup based on the defined threshold values
SET @sSQL= 'IF ('+ cast(@log_since_last_log_backup_mb as varchar(10)) +'>='+cast(@logthreshold as varchar(10))+')
BACKUP log '+@dbname +' TO DISK ='''+ @fName+'_log.bak'''
--Print the SQL
print @sSQL
--Execute the dynamic SQL
EXEC sp_executesql @sSQL
FETCH NEXT FROM database_cursor INTO @dbname, @log_since_last_log_backup_mb
END
CLOSE database_cursor
DEALLOCATE database_cursor
END
After executing the above SQL, we can see that a T-log backup has been initiated on SQLShackDemoSBKP. We can schedule the script and initiate the backup according to the defined threshold conditions.
执行上述SQL后，我们可以看到已在SQLShackDemoSBKP上启动了T日志备份。 我们可以安排脚本并根据定义的阈值条件启动备份。
结语 (Wrapping up)
In this article we walked through the concept of the checkpoint and the impact of implementing indirect checkpoints and why indirect checkpoints are recommended on systems with larger memory footprints.
在本文中，我们介绍了检查点的概念以及实现间接检查点的影响，以及为什么建议在内存占用量较大的系统上建议使用间接检查点。
We also demonstrated the interesting and useful enhancements with DMV and DMF for efficiently managing differential and transactional log backups.
我们还展示了DMV和DMF有趣且有用的增强功能，可以有效地管理差异和事务日志备份。
T-SQL scripts were made available and can be easily customized for an SQL Server 2017 environment.
T-SQL脚本已可用，可以轻松针对SQL Server 2017环境进行自定义。
All in all, SQL Server 2017 provides many features, of which my favorites are handling smart backups, which is a really intuitive feature.
总而言之，SQL Server 2017提供了许多功能，我最喜欢的功能是处理智能备份，这是一个非常直观的功能。
目录 (Table of contents)
参考资料 (References)
- SQL 2016 – it just runs faster: indirect Checkpoint Default SQL 2016 –运行速度更快：间接检查点默认
- How we made backups faster with SQL Server 2017 我们如何使用SQL Server 2017更快地备份
- Smart Transaction log backup, monitoring and diagnostics with SQL Server 2017 使用SQL Server 2017进行智能事务日志备份，监视和诊断
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
