让备份再快一点点——BUFFERCOUNT与MAXTRANSFERSIZE

来源：互联网发布：像钉钉一样的软件编辑：程序博客网时间：2024/06/05 00:51

普通备份与压缩备份前面已有文章比较过，不再赘述。

一、物理机，设定最大 8GB 内存， DB大小为 2 GB.

--BACKUP DATABASE 成功处理了 101546 页，花费 10.422 秒(76.120 MB/秒)。backup database [test2] to disk=N'E:\test.bak' with Format,Compression--BACKUP DATABASE 成功处理了 101546 页，花费 9.715 秒(81.660 MB/秒)。backup database [test2] to disk=N'E:\test.bak' with Format,BUFFERCOUNT=10,MAXTRANSFERSIZE=4194304,Compression--BACKUP DATABASE 成功处理了 101546 页，花费 9.424 秒(84.181 MB/秒)。backup database [test2] to disk=N'E:\test.bak' with Format,BUFFERCOUNT=20,MAXTRANSFERSIZE=4194304,Compression--BACKUP DATABASE 成功处理了 101546 页，花费 9.230 秒(85.951 MB/秒)。backup database [test2] to disk=N'E:\test.bak' with Format,BUFFERCOUNT=50,MAXTRANSFERSIZE=4194304,Compression--BACKUP DATABASE 成功处理了 101546 页，花费 8.884 秒(89.298 MB/秒)。backup database [test2] to disk=N'E:\test.bak' with Format,BUFFERCOUNT=100,MAXTRANSFERSIZE=4194304,Compression--BACKUP DATABASE 成功处理了 101546 页，花费 8.833 秒(89.814 MB/秒)。backup database [test2] to disk=N'E:\test.bak' with Format,BUFFERCOUNT=200,MAXTRANSFERSIZE=4194304,Compression--BACKUP DATABASE 成功处理了 101546 页，花费 8.797 秒(90.181 MB/秒)。backup database [test2] to disk=N'E:\test.bak' with Format,BUFFERCOUNT=500,MAXTRANSFERSIZE=4194304,Compression--缓冲池中的可用内存不足。backup database [test2] to disk=N'E:\test.bak' with Format,BUFFERCOUNT=1000,MAXTRANSFERSIZE=4194304,Compression

二、物理机，设定最大 14GB 内存， DB大小为 114 GB.

--BACKUP DATABASE 成功处理了 9170343 页，花费 826.506 秒(86.682 MB/秒)。backup database [test] to disk=N'D:\database_backup\test.bak' with Format,Compression,STATS=10--BACKUP DATABASE 成功处理了 9173276 页，花费 852.911 秒(84.025 MB/秒)。backup database [test] to disk=N'D:\database_backup\test.bak' with Format,BUFFERCOUNT=5,MAXTRANSFERSIZE=2097152,Compression,STATS=10--BACKUP DATABASE 成功处理了 9169555 页，花费 837.608 秒(85.525 MB/秒)。backup database [test] to disk=N'D:\database_backup\test.bak' with Format,BUFFERCOUNT=10,MAXTRANSFERSIZE=4194304,Compression,STATS=10--BACKUP DATABASE 成功处理了 9170892 页，花费 830.479 秒(86.272 MB/秒)。backup database [test] to disk=N'D:\database_backup\test.bak' with Format,BUFFERCOUNT=20,MAXTRANSFERSIZE=4194304,Compression,STATS=10--BACKUP DATABASE 成功处理了 9169633 页，花费 804.668 秒(89.027 MB/秒)。backup database [test] to disk=N'D:\database_backup\test.bak' with Format,BUFFERCOUNT=50,MAXTRANSFERSIZE=4194304,Compression,STATS=10--BACKUP DATABASE 成功处理了 9169540 页，花费 793.776 秒(90.248 MB/秒)。backup database [test] to disk=N'D:\database_backup\test.bak' with Format,BUFFERCOUNT=100,MAXTRANSFERSIZE=4194304,Compression,STATS=10--BACKUP DATABASE 成功处理了 9170108 页，花费 822.454 秒(87.106 MB/秒)。backup database [test] to disk=N'D:\database_backup\test.bak' with Format,BUFFERCOUNT=200,MAXTRANSFERSIZE=4194304,Compression,STATS=10

总体来说：

参数的设置必须满足：

BUFFERCOUNT*MAXTRANSFERSIZE < DB / 2

BUFFERCOUNT*MAXTRANSFERSIZE < 最大可用内存 / 4

而且这些比较玄妙，不同的DB，需要设置的参数不一样。

简单来说，这个是比较适合的：

backup database [dbName] to disk=N'D:\xxx.bak' with BUFFERCOUNT=100,MAXTRANSFERSIZE=4194304,Compression,STATS=10

参考：

BACKUP

0 0