🥇MS SQL Server：加强备份

等待！等待！确实，这不是另一篇有关 SQL Server 备份类型的文章。我什至不会谈论恢复模型之间的差异以及如何处理过度增长的日志。

也许（只是也许），读完这篇文章后，您将能够确保使用标准方法从您身上删除的备份将在明天晚上删除，嗯，速度快 1.5 倍。这只是因为您使用了更多的备份数据库参数。

如果帖子的内容对您来说是显而易见的，我很抱歉。我阅读了 Google 提供的有关“habr sql server backup”短语的所有内容，并且在没有一篇文章中我发现任何提到使用参数可以以某种方式影响备份时间这一事实。

我会立即提请您注意亚历山大·格拉琴科（Alexander Gladchenko）的评论（@mssqlhelp):

切勿在生产中更改 BUFFERCOUNT、BLOCKSIZE、MAXTRANSFERSIZE 参数。它们只是为了写这样的文章而制作的。在实践中，你很快就会摆脱记忆问题。

当然，成为最聪明的人并发布独家内容会很酷，但不幸的是，情况并非如此。有英语和俄语文章/帖子（我总是很困惑如何正确称呼它们）专门讨论这个主题。以下是我遇到的一些：时间, 二, 三个（在 sql.ru 上）.

因此，首先，我将附加一个稍微精简的 BACKUP 语法 MSDN （顺便说一下，我上面写了关于备份数据库的内容，但这一切都适用于事务日志备份和差异备份，但效果可能不太明显）：

BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  <...>
  [ WITH { <...>
           | <general_WITH_options> [ ,...n ] } ]
[;]

<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
 <...>
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...>

<…> - 这意味着那里有一些东西，但我删除了它，因为现在它与主题无关。

您通常如何进行备份？他们如何“教”如何备份数十亿篇文章？一般来说，如果我需要对一些不是很大的数据库进行一次性备份，我会自动编写如下内容：

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

而且，一般来说，这里列出了有关备份的文章中通常提到的所有参数的 75-90%。嗯，还有INIT、SKIP。您访问过 MSDN 吗？您是否看到有一个半屏幕的选项？我也看到了...

您可能已经意识到，我们将进一步讨论第一个代码块中保留的三个参数 - BLOCKSIZE、BUFFERCOUNT 和 MAXTRANSFERSIZE。以下是来自 MSDN 的描述：

块大小 = { 块大小 | @ 块大小变量 } - 指示物理块大小（以字节为单位）。支持的大小为 512、1024、2048、4096、8192、16、384 和 32 字节 (768 KB)。对于磁带设备，默认值为 65；对于其他设备，默认值为 536。通常，此参数不是必需的，因为 BACKUP 语句会自动为设备选择适当的块大小。显式设置块大小会覆盖自动块大小选择。

缓冲区计数 = { 缓冲区计数 | @ 缓冲区计数变量 } - 定义将用于备份操作的 I/O 缓冲区总数。您可以指定任何正整数值，但大量缓冲区可能会因 Sqlservr.exe 进程中的虚拟地址空间过多而导致内存不足错误。

缓冲区使用的空间总量由以下公式确定： BUFFERCOUNT * MAXTRANSFERSIZE.

最大传输尺寸 = { 最大传输大小 | @ 最大传输大小变量 } 指定 SQL Server 和备份集介质之间交换的最大数据包大小（以字节为单位）。支持 65 字节 (536 KB) 的倍数到 64 字节 (4 MB)。

我发誓 - 我以前读过这篇文章，但我从未想过它们会对生产力产生多大的影响。而且，显然，我需要做出某种“出柜”，承认即使现在我也不完全理解他们到底在做什么。我可能需要阅读有关缓冲 I/O 和使用硬盘的更多信息。有一天我会这样做，但现在我可以编写一个脚本来检查这些值如何影响备份的速度。

我制作了一个小型数据库，大约10GB大小，将其放在SSD上，并将备份目录放在HDD上。

我创建一个临时表来存储结果（我没有临时表，所以我可以更详细地研究结果，但你自己决定）：

DROP TABLE IF EXISTS ##bt_results; 

CREATE TABLE ##bt_results (
    id              int IDENTITY (1, 1) PRIMARY KEY,
    start_date      datetime NOT NULL,
    finish_date     datetime NOT NULL,
    backup_size     bigint NOT NULL,
    compressed_size bigint,
    block_size      int,
    buffer_count    int,
    transfer_size   int
);

该脚本的原理很简单 - 嵌套循环，每个循环都会更改一个参数的值，将这些参数插入到 BACKUP 命令中，从 msdb.dbo.backupset 中保存最后一条记录的历史记录，删除备份文件和下一次迭代。由于备份执行数据是从备份集中获取的，因此准确性会有所损失（没有几分之一秒），但我们会幸存下来。

首先，您需要启用 xp_cmdshell 来删除备份（如果不需要，请不要忘记禁用它）：

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

实际上：

DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH 
    COMPRESSION,
    BLOCKSIZE = {bs},
    BUFFERCOUNT = {bc},
    MAXTRANSFERSIZE = {ts}';

DECLARE @sql AS nvarchar(max);

/* BLOCKSIZE values */
DECLARE @bs     int = 4096, 
        @max_bs int = 65536;

/* BUFFERCOUNT values */
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;

/* MAXTRANSFERSIZE values */
DECLARE @ts     int = 524288,   --512KB, default = 1024KB
        @min_ts int = 524288,
        @max_ts int = 4194304;  --4MB

SELECT TOP 1 
    @bs = COALESCE (block_size, 4096), 
    @bc = COALESCE (buffer_count, 7), 
    @ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;

WHILE (@bs <= @max_bs)
BEGIN
    WHILE (@bc <= @max_bc)
    BEGIN       
        WHILE (@ts <= @max_ts)
        BEGIN
            SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));

            EXEC (@sql);

            INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
            SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size,  @bs, @bc, @ts 
            FROM msdb.dbo.backupset
            ORDER BY backup_set_id DESC;

            EXEC xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;

            SET @ts += @ts;
        END
        
        SET @bc += @bc;
        SET @ts = @min_ts;

        WAITFOR DELAY '00:00:05';
    END

    SET @bs += @bs;
    SET @bc = @min_bc;
    SET @ts = @min_ts;
END

如果您突然需要澄清这里发生的事情，请在评论或私信中留言。现在，我只告诉您我在 BACKUP DATABASE 中放入的参数。

对于 BLOCKSIZE，我们有一个“封闭”值列表，并且我没有执行 BLOCKSIZE < 4KB 的备份。 MAXTRANSFERSIZE 任何 64KB 倍数的数字 - 从 64KB 到 4MB。我的系统默认是1024KB，我取了512 - 1024 - 2048 - 4096。

BUFFERCOUNT 更困难 - 它可以是任何正数，但链接说它在备份数据库中是如何计算的以及为什么大值很危险？。它还说明如何获取有关备份实际使用的 BUFFERCOUNT 的信息 - 对我来说是 7。减少它是没有意义的，并且上限是通过实验发现的 - 当 BUFFERCOUNT = 896 和 MAXTRANSFERSIZE = 4194304 时，备份下降错误（有关内容写在上面的链接中）：

消息 3013，级别 16，状态 1，第 7 行备份数据库异常终止。

消息 701，级别 17，状态 123，第 7 行资源池“默认”中没有足够的系统内存来运行此查询。

为了进行比较，我将首先显示在不指定任何参数的情况下运行备份的结果：

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

好吧，备份和备份：

已处理文件 1070072 上的数据库“bt”、文件“bt”的 1 个页面。

已处理数据库“bt”的 2 页，文件 1 上的文件“bt_log”。

BACKUP DATABASE 在 1070074 秒（53.171 MB/秒）内成功处理了 157.227 个页面。

脚本本身测试参数，在几个小时内工作，所有测量值都在谷歌电子表格。这里是三个最佳执行时间的结果选择（我试图制作一个漂亮的图表，但在帖子中我将不得不使用表格，并在评论中 @mixsture 他补充说非常酷的图形).

SELECT TOP 7 WITH TIES 
    compressed_size, 
    block_size, 
    buffer_count, 
    transfer_size,
    DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;

注意，一个非常重要的注释 @mixsture из 评论:

我们可以自信地说，这些值范围内的参数与备份速度之间的关系是随机的，没有规律可循。但远离内置参数显然对结果有很好的影响

那些。仅通过管理标准 BACKUP 参数，备份删除时间就增加了 2 倍：26 秒，而开始时为 53 秒。这还不错吧？但我们需要看看修复后会发生什么。如果现在需要 4 倍的时间才能恢复怎么办？

首先，我们来测量一下使用默认设置恢复备份需要多长时间：

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

好吧，你自己也知道，方法是有的，替换不是替换，恢复不是恢复。我这样做：

已处理文件 1070072 上的数据库“bt”、文件“bt”的 1 个页面。

已处理数据库“bt”的 2 页，文件 1 上的文件“bt_log”。

RESTORE DATABASE 在 1070074 秒（40.752 MB/秒）内成功处理了 205.141 个页面。

现在我将尝试恢复更改了 BLOCKSIZE、BUFFERCOUNT 和 MAXTRANSFERSIZE 后的备份。

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE 在 1070074 秒（32.283 MB/秒）内成功处理了 258.958 个页面。

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE 在 1070074 秒（32.682 MB/秒）内成功处理了 255.796 个页面。

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE 在 1070074 秒（32.091 MB/秒）内成功处理了 260.507 个页面。

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE 在 1070074 秒（32.401 MB/秒）内成功处理了 258.015 个页面。

RESTORE DATABASE 语句在恢复期间不会更改；这些参数未在其中指定；SQL Server 本身从备份中确定它们。很明显，即使恢复了，也能有所收获——快了近 20%（说实话，我没有花太多时间在恢复上，我运行了几个“最快”的备份并确保没有恶化).

为了以防万一，让我澄清一下，这些参数并不适合每个人。只有通过测试才能得到最适合自己的参数。我得到了这些结果，你会得到不同的结果。但您会发现，您可以“调整”备份，并且它们实际上可以更快地形成和部署。

我还强烈建议您阅读完整的文档，因为可能存在特定于您的系统的细微差别。

自从我开始写有关备份的文章以来，我想立即再写一篇“优化”，这比“调整”参数更常见（据我所知，它至少被一些备份实用程序使用，也许与参数一起使用）之前已经描述过），但哈布雷也还没有描述过。

如果我们查看文档中“备份数据库”下的第二行，我们会看到：

TO <backup_device> [ ,...n ]

如果您指定多个 backup_devices，您认为会发生什么？语法允许。将会发生一件非常有趣的事情 - 备份将简单地“分布”在多个设备上。那些。每个“设备”单独而言将毫无用处，丢失一个，丢失整个备份。但这种涂抹会如何影响备份速度呢？

让我们尝试在并排位于同一文件夹中的两个“设备”上进行备份：

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

世界之父们，为什么要这样做呢？

已处理文件 1070072 上的数据库“bt”、文件“bt”的 1 个页面。

已处理数据库“bt”、文件“bt”的 2 页登录文件 1。

BACKUP DATABASE 在 1070074 秒（40.092 MB/秒）内成功处理了 208.519 个页面。

备份速度是否突然提高了 25%？如果我们添加更多设备怎么办？

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;

BACKUP DATABASE 在 1070074 秒（34.234 MB/秒）内成功处理了 244.200 个页面。

总的来说，仅由于备份一次写入一个磁盘上的 35 个文件，备份时间的增益约为 4%。我检查了一个更大的数字 - 我的笔记本电脑没有增益，最好是 4 台设备。对于你来说——我不知道，你需要检查一下。好吧，顺便说一句，如果您有这些设备 - 这些是真正不同的磁盘，那么恭喜您，收益应该更加显着。

现在我们来谈谈如何挽回这份幸福。为此，您必须更改恢复命令并列出所有设备：

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

RESTORE DATABASE 在 1070074 秒（38.027 MB/秒）内成功处理了 219.842 个页面。

快一点，但在附近，并不重要。一般来说，备份删除得更快，并以相同的方式恢复 - 成功吗？对于我来说，这是相当成功的。这 这一点很重要，所以我重复一遍 - 如果你 如果您丢失了这些文件中的至少一个，您就会丢失整个备份.

如果您在日志中查看使用跟踪标志 3213 和 3605 显示的备份信息，您会注意到，当备份到多个设备时，至少 BUFFERCOUNT 的数量会增加。也许，你可以尝试为 BUFFERCOUNT、BLOCKSIZE、MAXTRANSFERSIZE 选择更优化的参数，但我没有立即成功，而且我懒得再次进行这样的测试，但针对的是不同数量的文件。而且对于轮子来说是一个遗憾。如果你想在家里组织这样的测试，重新制作脚本并不困难。

最后，我们来谈谈价格。如果备份在用户工作的同时被删除，您需要采取非常负责任的方法来测试，因为如果备份删除得更快，磁盘的压力会更大，处理器的负载会增加（您仍然必须压缩它是动态的），因此，系统的整体响应能力会降低。

开玩笑，但我很清楚我没有透露任何信息。上面所写的内容只是演示如何选择最佳备份参数。

请记住，您所做的一切均由您自行承担风险。检查您的备份，不要忘记 DBCC CHECKDB。

来源： habr.com

MS SQL Server：加强备份

添加评论 取消回复

添加评论取消回复