为BACKUP命令设置BUFFERCOUNT，BLOCKSIZE和MAXTRANSFERSIZE

我正在寻找有关设置BUFFERCOUNT命令的BLOCKSIZE，MAXTRANSFERSIZE和BACKUP的值的实用指南。我做了一些研究（请参阅下文），做了一些测试，并且我完全意识到，任何真正有价值的答案都将以“嗯，这取决于……”开始。我对已经完成的测试以及我发现的任何资源中显示的测试的担忧（请参见下面的方法）是测试是在真空中完成的，很可能是在没有其他负载的系统上进行的。 br />对于基于长期经验的这三种选择，我对适当的指导/最佳实践感到很好奇：数周或数月的数据点。而且我并不是要寻找特定的值，因为这主要是可用硬件的功能，但是我想知道：


各种硬件/负载因素如何影响应做的事情。
在任何情况下都不应该覆盖这些值吗？
是否存在要覆盖那些并非立即显而易见的陷阱的陷阱？占用过多的内存和/或磁盘I / O？复杂的还原操作吗？
如果我有一台运行着多个SQL Server实例的服务器（一个默认实例和两个命名实例），并且如果我同时运行所有3个实例的备份，那是否会影响我设置这些值的方式是否要确保集合（BUFFERCOUNT * MAXTRANSFERSIZE）不会超出可用RAM？可能发生I / O争用吗？
在将三个实例放在一台服务器上并再次在所有三个实例上同时运行备份的同一场景中，在每个实例内同时运行多个数据库的备份又如何影响这些值的设置？这意味着，如果三个实例中的每个实例都有100个数据库，则每个实例同时运行2或3个备份，这样就可以同时运行6到9个备份。 （在这种情况下，我有许多中小型数据库，而不是几个大型数据库。）

到目前为止，我收集到的内容是： > BLOCKSIZE：支持的大小为512、1024、2048、4096、8192、16384、32768和65536（64 KB）字节。 [1]

磁带设备的默认值为65536，否则为512 [1]

如果要备份，则打算复制到CD-上并从CD-中恢复ROM，指定BLOCKSIZE = 2048 [1]

当您写入单个磁盘时，默认值512就可以了。如果使用RAID阵列或SAN，则必须进行测试以查看默认值还是65536更好。 [13（第18页）]


如果手动设置，则该值必须> =用于创建数据文件的块大小，否则会出现以下错误：消息3272，级别16，状态0，第3行
'C：\ Program Files \ Microsoft SQL Server \ MSSQL11.MSSQLSERVER \ MSSQL \ Backup \ BackupTest。 bak'设备的硬件扇区大小为4096，但block size参数指定的不兼容替代值512。使用兼容的块大小重新发出该语句。





BUFFERCOUNT：



默认[2]，[8]：


SQL Server 2005和更高版本：
（NumberofBackupDevices * [mystery_multiplier]）+ NumberofBackupDevices +（2 * NumberofVolumesInvolved）



[mystery_multiplier]：此值存在一些不一致之处。我看到它以3种形式表达：



3 [2]


GetSuggestedIoDepth [8]


GetSuggestedIoDepth + 1 [8]


测试该显示乘数为3是在SQL Server 2005 SP2 [9]上完成的。是4。表示给定GetSuggestedIoDepth的报告值（直接在下面），或者：



GetSuggestedIoDepth现在为4，或者GetSuggestedIoDepth + 1




GetSuggestedIoDepth为DISK设备返回3 [9]

没有硬设置最大值，但是鉴于所需的内存=（BUFFERCOUNT * MAXTRANSFERSIZE ），则实际的最大值似乎是：
BUFFERCOUNT <= (available_memory / MAXTRANSFERSIZE)




MAXTRANSFERSIZE：


可能的值是65536字节（64 KB）的倍数，最大为4194304字节（4 MB）。 [1]

默认值：如果设备处于读取模式（还原），或者这是台式机或Express Edition，请使用64K，否则请使用1 MB。 [9]



常规/杂项：


可以使用的最大大小是（缓冲池的物理内存） / 16）。从GlobalMemoryStatusEx（ullTotalPhys）API调用返回。 [9]

跟踪标志3213在执行备份/还原操作时输出备份/还原配置参数，并且3605将输出转储到ERRORLOG文件：DBCC TRACEON (3213, 3605, -1);

您可以使用DISK = N'NUL:'（在DOS / Windows下相当于UNIX中的/dev/null），以便更轻松地测试某些指标（但由于跳过了编写I / O，因此无法很好地了解总处理时间）
资源



T-SQL BACKUP命令的MSDN页面
KB904804：在SQL Server 2000中备份数据库时，性能会降低提高SQL Server备份性能
备份和还原
优化SQL Server备份和还原
优化备份性能
如何使用压缩磁盘和固态磁盘提高SQL数据库完全备份的速度
不正确的BufferCount数据传输选项会导致OOM条件
如何工作：SQL Server备份和还原如何选择传输大小
工作原理：SQL Server备份缓冲区交换（VDI焦点）
SQL备份调整大型数据库
SQL Server内存作为备份缓冲区

案例研究：快速可靠的备份和通过网络还原VLDB（.docx文件）
建议使用多少备份设备来提高备份性能？


我测试过：

 --DBCC TRACEON (3213, 3605, -1);

BACKUP DATABASE [Test] TO
      DISK =  'NUL:'
     --,DISK = 'NUL:'
     -- DISK =  'BackupTest1.bak'
     -- ,DISK =  'BackupTest2.bak'
WITH
    STATS = 5,
    FORMAT,
    CHECKSUM,
    NO_COMPRESSION,
    COPY_ONLY
    --,BUFFERCOUNT = 40
    --,MAXTRANSFERSIZE = 4194304--2097152,
    --,BLOCKSIZE = 16384 

--DBCC TRACEOFF (3213, 3605, -1);
 

UPDATE

似乎我有时忘记添加一些我在回答问题时总是要求他人提供的信息;-)。我确实提供了一些有关当前情况的信息，但我可以提供更多详细信息：

我正在为提供24/7 / 365.25 SaaS应用程序的客户端工作。因此，用户随时随地都可以使用，但实际上，这些用户都是美国用户（目前），并且通常工作在“标准”时间：太平洋时间早上7点（美国东部时间上午10点）到太平洋时间晚上7点。 （即美国东部时间晚上10点），但每周7天，而不仅仅是星期一至星期五，尽管周末的工作量要轻一些。这是一个利基行业，因此没有成千上万（或更多）潜在客户。客户端数据库的数量因实例而异，最大的实例拥有206个客户端。最大的DB约为8 GB，但只有大约30个DB超过1 GB。因此，我并不是专门尝试最大化VLDB的性能。

当我开始使用此客户端时，它们的备份始终是FULL，每天一次，并且没有LOG备份。他们还将MAXTRANSFERSIZE设置为4 MB，将BUFFERCOUNT设置为50。我用稍微定制化的Ola Hallengren的数据库备份脚本替换了该设置。稍微自定义的部分是，它是从多线程工具（我编写并希望很快会开始销售）运行的，该工具在连接到每个实例时动态发现数据库，并允许对每个实例进行限制（因此，我目前正在运行同时执行三个实例，但是每个实例的DB顺序执行，因为我不确定同时运行它们的后果。在其他日子；每10分钟进行一次LOG备份。我在这里查询的3个选项使用默认值。但是，知道如何设置它们之后，我想确保我没有撤销优化（只是因为旧系统中存在一些重大缺陷并不意味着一切都错了）。当前，对于206个数据库，FULL备份（一周一次）大约需要62分钟，而剩余的几天（FULL之后的第一天为7，最后一天的最后一天为20）则需要7-20分钟的DIFF备份。下一个FULL）。那就是按顺序运行它们（单线程）。总共LOG备份过程（所有3个实例上的所有DB）总共每次花费50到90秒（同样，每10分钟）。 DB，但是a）我不确定DB的多线程和中小型大小会带来什么好处，并且b）我不想使还原过程复杂化（有多种原因导致处理单个文件是首选）。

我还意识到我可以启用压缩功能（我的测试查询有意禁用了压缩功能），我已经向团队推荐了压缩功能，但是我注意到内置的压缩​​功能有点麻烦。旧过程的一部分是将每个文件压缩到RAR中，我进行了自己的测试，发现是的，RAR版本比本地压缩版本小至少50％。我确实尝试过先使用本机压缩来加快处理速度，然后再使用RAR文件，但是这些文件虽然比仅本机压缩的文件小，但仍比仅RAR的压缩版本大一点，并且有足够的区别来证明不使用本机压缩。压缩备份的过程是异步的，每隔X分钟运行一次。如果找到.bak或.trn文件，则将其压缩。这样，备份过程不会因压缩每个文件而变慢。

评论

只是好奇，您是否要解决缓慢的备份问题？通常，默认设置在大多数环境中都可以正常工作。此外，电源选项是否设置为高性能-因为进行备份使用CPU周期。

---

@Kin不，备份不是特别慢。但是，如果进行较小的更改将/可以使它们加快20％（或更多），那么我当然会接受。对于206个数据库，完整备份（一周一次）大约需要62分钟，而剩余日期DIFF备份大约需要7至20分钟。那就是按顺序运行它们（单线程）。当我开始使用此客户端时，先前的设置是将4 MB用于MaxTransfer，将50 MB用于BufferCount。目前，我只是使用默认值，因此不确定是否会影响性能，因此想在进行任何更改之前了解更多信息。

---

@srutzky只是您上次评论的一个快速点，我节省了可观的时间，将备份分解为多个文件并存储到同一卷。我只是想与您分享，以防万一您还没有尝试过。如果您的206个DB跨多个DB并行运行备份，尽管您可能无法获得多线程优势。

---

@MaxVernon“虚拟设备接口（VDI）备份允许第三方备份解决方案与SQL Server集成。”取自我的问题:)中的资源10。我不想花那么多精力;-)

---

@srutzky，以防您想找点乐子：阅读MSSQL备份-检查HBA的最大传输大小-这个家伙非常聪明，在测试中非常彻底。还有一些可能与您的测试匹配的东西：SirSQL的自动备份调整。

---

#1 楼

您已经解决了许多问题。感谢您的彻底检查！

我注意到了几件事情：完成。



您是否正在运行24x7实例？全天候负载是多少？我注意到您已禁用备份压缩；是出于测试目的而设计，还是出于某种原因希望在将其投入生产时将其关闭？如果您有大量的硬件空间（CPU / RAM），并且在最短的时间内完成备份是至关重要的，那么您就需要针对该目标为特定的硬件调整这些参数。如果您想确保OLTP工作负载全天候服务，并且不想让备份影响工作量，则可能需要反过来调整这些参数。自从您要寻求一般指导以来，您还没有确定设计目标，因为您如此明智地声明了“取决于”。您应该保留这些默认值吗？不确定您的替换能力。除非您有特殊需要进行调整，否则您可能希望保留默认值。就像他们说的那样，让熟睡的狗躺下。占用过多的内存和/或磁盘I / O？还原操作复杂吗？





正如您所引用的文档清楚指出的那样，过度增加这些参数无疑会对正常运行时间产生负面影响。与所有基于生产的事物一样，您需要在部署它之前对其进行彻底的测试，除非绝对必要，否则请保留设置。



如果我有一台运行着多个SQL Server实例的服务器（一个默认实例和两个命名实例），并且如果我同时运行所有3个实例的备份，那么除了确保集合（BUFFERCOUNT * MAXTRANSFERSIZE）是否不超过可用RAM？可能发生I / O争用吗？我当然会担心使用超过60％或70％的可用ram进行备份操作，除非我100％地确定备份窗口期间什么都不会发生。

我在SQLServerScience.com上写了一篇博客，其中包含一些代码来演示如何进行备份性能测试。这可能不是我写过的最好答案， Great One™曾经说过：“您会错过100％的镜头”

评论

---

谢谢那些指针，麦克斯。 +1 ：）。我只是在我已经不短的问题中添加了一个UPDATE部分，以解决关于该问题的一些评论以及您在此处有关为何不使用压缩的问题。我相信我也回答了您有关如何运行备份的问题:-)。

–所罗门·鲁兹基
16年5月5日在21:16

---