sql-server – 为BACKUP命令设置BUFFERCOUNT,BLOCKSIZE和MAXTRAN

我很好奇有关基于长期经验的这三个选项的适当指导/最佳实践：数周或数月的许多数据点.而且我不是在寻找特定的值,因为这主要是可用硬件的功能,但我想知道：

>各种硬件/负载因素如何影响应该做的事情.
>是否存在不应覆盖这些值的情况？
>是否有任何陷阱可以覆盖任何不明显的？耗尽太多内存和/或磁盘I / O？使恢复操作复杂化？
>如果我有一个运行多个SQL Server实例的服务器(一个默认实例和两个命名实例),并且如果我同时运行所有3个实例的备份,这是否会影响我设置这些值的方式,除了确保集合( BUFFERCOUNT * MAXTRANSFERSIZE)不超过可用内存？可能的I / O争用？
>在同一场景中,在一台服务器上同时运行三个实例,并同时在所有三个服务器上同时运行备份,如何在每个实例中同时运行多个数据库的备份会影响这些值的设置？这意味着,如果三个实例中的每个实例都有100个数据库,则每个实例同时运行2或3个备份,以便同时运行6到9个备份. (在这种情况下,我有许多中小型数据库,而不是几个大型数据库.)

到目前为止我收集了什么：

> BLOCKSIZE：

>支持的大小为512,1024,2048,4096,8192,16384,32768和65536(64 KB)字节. [1]
>磁带设备的默认值为65536,否则为512 [1]
>如果要进行计划复制到CD-ROM上并从CD-ROM还原的备份,请指定BLOCKSIZE = 2048 [1]
>当您写入单个磁盘时,默认值为512就可以了;如果使用RAID阵列或SAN,则必须进行测试以查看默认值或65536是否更好. [13(第18页)]
>如果手动设置,则值必须为> =用于创建数据文件的块大小,否则您将收到以下错误：

Msg 3272,Level 16,State 0,Line 3
The ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupBackupTest.bak’ device has a hardware sector size of 4096,but the block size parameter specifies an incompatible override value of 512. Reissue the statement using a compatible block size.

> BUFFERCOUNT：

>默认[2],[8]：

SQL Server 2005 and later versions:
(NumberofBackupDevices * [mystery_multiplier]) + NumberofBackupDevices + (2 * NumberofVolumesInvolved)

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

> 3 [2]
> GetSuggestedIoDepth [8]
> GetSuggestedIoDepth 1 [8]

将乘数显示为3的测试是在SQL Server 2005 SP2上完成的[9].

我在SQL Server 2008 R2和2012上的测试,以及关于SQL Server 2014 [8]的用户评论,显示乘数为4.意思是,给定GetSuggestedIoDepth(直接在下面)的报告值,或者：

> GetSuggestedIoDepth现在是4,或
>乘数现在是GetSuggestedIoDepth 1

> GetSuggestedIoDepth为DISK设备返回3 [9]
>没有硬设置的最大值,但考虑到所需的内存=(BUFFERCOUNT * MAXTRANSFERSIZE),看起来实际的最大值将是：
BUFFERCOUNT< =(available_memory / MAXTRANSFERSIZE) > MAXTRANSFERSIZE：
>可能的值是65536字节(64 KB)的倍数,范围最大为4194304字节(4 MB). [1]
>默认值：如果设备处于读取模式(还原)或者这是台式机或速成版使用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: You experience slow performance when you back up the database in SQL Server 2000
> Options to Improve SQL Server Backup Performance
> Backup and Restore
> Optimizing SQL Server Backup and Restore
> Optimizing Backup Performance
> How to increase SQL Database Full Backup speed using compression and Solid State Disks
> Incorrect BufferCount data transfer option can lead to OOM condition
> How It Works: How does SQL Server Backup and Restore select transfer sizes
> How It Works: SQL Server Backup Buffer Exchange (a VDI Focus)
> SQL Backup tuning large databases
> SQL Server Memory for Backup Buffer
> A Case Study: Fast and Reliable Backup and Restore of a VLDB over the Network(.docx文件)
> How many backup devices is recommended to improve backup performance?

我测试过：

--DBCC TRACEON (3213,-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,-1);

UPDATE

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

我正在为提供24/7 / 365.25 SaaS应用程序的客户工作.因此,用户有可能在任何时候开启,但实际上,用户都是美国的(目前)并且往往主要在“标准”时间工作：太平洋时间上午7点(即东部时间上午10点)到太平洋时间下午7点(即东部时间晚上10点),但每周7天,不仅仅是周一至周五,尽管周末负荷稍微轻一些.

它们的设置使每个客户端都有自己的DB.这是一个利基行业,因此没有数万(或更多)潜在客户.客户端数据库的数量因实例而异,最大的实例持有206个客户端.最大的DB约为. 8 GB,但只有大约30个DB超过1 GB.因此,我并没有特别尝试最大限度地提高VLDB的性能.

当我开始使用此客户端时,他们的备份始终为FULL,每天一次,并且没有LOG备份.他们还将MAXTRANSFERSIZE设置为4 MB,将BUFFERCOUNT设置为50.我用稍微定制的Ola Hallengren’s数据库备份脚本替换了该设置.稍微定制的部分是它从多线程工具(我编写并希望很快开始销售)运行,在连接到每个实例时动态发现DB,并允许每个实例限制(因此我目前正在运行同时有三个实例,但每个实例的数据库按顺序排列,因为我不确定同时运行它们的后果).

设置现在每周一天进行全面备份,其他日进行DIFF备份;每10分钟进行一次LOG备份.我正在使用我在这里询问的3个选项的默认值.但是,知道它们是如何设置的,我想确保我没有撤消优化(仅仅因为旧系统中存在一些重大缺陷并不意味着一切都是错误的).目前,对于206个数据库,完整备份(每周一次)需要大约62分钟,剩余天数DIFF备份需要7到20分钟(FULL后第一天7个,前一天20个)下一个完全).这是按顺序运行它们(单线程). LOG备份过程总共(所有3个实例上的所有DB)每次需要50到90秒(再次,每10分钟).

我意识到我可以为每个数据库运行多个文件,但是a)我不确定多线程和中小型数据库会有多好,并且b)我不想让恢复过程复杂化(有多种原因可以解决为什么处理单个文件的问题.

我也意识到我可以启用压缩(我的测试查询有意禁用它),我向团队推荐了这个,但我注意到内置压缩有点太糟糕了.旧流程的一部分是将每个文件压缩为RAR,我进行了自己的测试,发现是的,RAR版本比本机压缩版本小至少50％.我确实首先尝试使用本机压缩来加速,然后RAR文件,但这些文件虽然比仅仅本地压缩的文件小,但仍然比仅限RAR的压缩版本大一些,并且有足够的差异来证明不使用本机压缩.压缩备份的过程是异步的,每隔X分钟运行一次.如果找到.bak或.trn文件,则会压缩它.这样,压缩每个文件所需的时间不会减慢备份过程.

解决方法

只是我注意到的一些事情：

• How various hardware / load factors influence what should be done.

你在运行24×7实例吗？全天候负载是多少？我注意到你禁用了备份压缩;是按照设计进行测试,还是因为某种原因在将其投入生产时将其关闭？如果您有大量的硬件余量(CPU / RAM),并且在最短的时间内完成备份是至关重要的,那么您需要针对具有该目标的特定硬件调整这些参数.如果您希望确保OLTP工作负载全天候服务,并且不希望备份影响该工作负载,那么您可能需要以相反的方式调整这些参数.您没有确定自己的设计目标,因为您要求提供一般性指导,但是如此明智地说明“它取决于”.

• Are there circumstances in which none of these values should be overridden?

如果您在不再维护实例后担心可支持性,并且不确定替换的能力,则需要保留默认设置.除非您特别需要调整默认值,否则您可能希望保留默认值.正如他们所说,让睡觉的狗撒谎.

• Are there pitfalls for overriding any of these that are not immediately obvious? Using up too much memory and/or disk I/O? Complicating restore operations?

正如您所引用的文件明确指出的那样,过多地提高这些参数肯定会对正常运行时间产生负面影响.与基于生产的所有内容一样,您需要在部署之前对其进行彻底测试,并且除非绝对必要,否则请单独保留设置.

• If I have a server with multiple instances of SQL Server running (a Default Instance and two Named Instances),and if I run the backups of all 3 Instancs concurrently,does that affect how I set these values beyond making sure that the collective (BUFFERCOUNT * MAXTRANSFERSIZE) does not exceed available RAM? Possible I/O contention?

您需要确保在不可预见的情况下留出足够的RAM.我当然会担心使用超过60％或70％的可用ram用于备份操作,除非我100％确定在备份窗口期间不会发生任何其他事情.

我写了一篇博客文章,其中包含一些代码,显示我如何在SQLServerScience.com进行备份性能测试

这可能不是我写过的最好的答案,但是正如The Great One?曾经说过的那样,“你错过了100％不拍摄的镜头”

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!