SQL Server 備份效能與磁碟效能分析 - II

上一篇 (SQL Server 備份效能與磁碟效能分析 - I)，我們主要介紹如何驗證 EBS Volume 的方式，本篇我們就來介紹在 SQL Server 備份時，該如何進行效能調整的部份。

進行備份前，由於我們要模擬一個比較大的資料庫，所以我找到Stack Overflow 有提供下載，所以我此次測試的資料庫約有50G的大小[1]。

在進行資料庫備份時，我們通常可以透過下列的指令進行備份。

BACKUP DATABASE [StackOverflow2013] TO  DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE successfully processed 6165657 pages in 154.625 seconds (311.522 MB/sec).

首先我們先來說明一下，上述的指令主要就是將資料庫 StackOverflow2013 備份到 S:\db_backup\20210909-test3.bak 的位置，而 stats = 10 的話，就代表每完成10%就顯示出，藉以了解備份的進度，最後可以看出花了多少的時間與大約每秒的備份速度。

從前一篇的說明，我們知道整體的 Throughput 會與 Thread 和 BlockSize 有關，但從Microsoft 的文件上來看[2]，SQL Server Backup 中的 BlockSize 最高只能設定至 64K，所以這就斷了一條路，我們只好從 Thread 下手，我們嘗試將原本的備份檔案，從一個檔案，改寫成多個檔案後，如下列的語法，即可將整體的 Throughput 拉上來。

BACKUP DATABASE [StackOverflow2013] 

TO 

  DISK = N'S:\db_backup\20210909-test1.bak'
  , DISK = N'S:\db_backup\20210909-test2.bak'
  , DISK = N'S:\db_backup\20210909-test3.bak'
  , DISK = N'S:\db_backup\20210909-test4.bak'

WITH 

    NAME = N'StackOverflow2013-Full Database Backup'
    , INIT, STATS = 10

BACKUP DATABASE successfully processed 6165657 pages in 95.388 seconds (504.981 MB/sec).

由於 SQL Server 在備份上是採取當寫一個檔案時，會以一個 Thread 進行，所以上述的動作，我們分成四個 Thread 進行，所以你可以看出最後的 Throughput 可以達到 504MB，但其實如果我將檔案分成更多個時，是否會有更好的表現，我的答案是不一定的，因為這個會取決於你的 Data File 的分檔數量與 Core 數量等，所以這個可以測試確認後，找出一個合適的方式。

除了上述的作法，還有什麼方式可以進行調整，答案是有的，我們可以透過另外二個參數來進行，一個是 BUFFERCOUNT，而另一個就是 MAXTRANSFERSIZE，二個數值的調整會影響到記憶體的使用量，根據微軟的文件說明[2]，可能會造成 OOM 的情況，所以也要請大家注意。

BACKUP DATABASE [StackOverflow2013] TO  DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, BUFFERCOUNT = 7, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536

BACKUP DATABASE successfully processed 6165657 pages in 95.358 seconds (505.140 MB/sec).

其中 BufferCount 仍是預設值，我設定為7，關於此值的設定，會與你的備份設備裝置相關，大約也在7-10之間[3]，所以我仍是以7為主，但在 MaxTransferSize 的部份，預設值為 1,048,576 (1M)，所以我將此值的設定調整加大後，你也可以看到，也是有加快速度的效果。

相反的，如果將這個值設定變小時，會有什麼情況，可想而知，備份的速度將會非常的慢，如下列所示，我將 MaxTransferSize 從預設的 1M 改成 64k後，整體的備份速度就從311MB左右，跌至 63 MB。

BACKUP DATABASE [StackOverflow2013] TO  DISK = N'S:\db_backup\20210909-test.bak' WITH NOFORMAT, NOINIT,  NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, BUFFERCOUNT = 7, MAXTRANSFERSIZE = 65536, BLOCKSIZE = 65536

BACKUP DATABASE successfully processed 6165673 pages in 753.965 seconds (63.887 MB/sec).

另外當我們如果相要進行備份時的預設參數為何，可以透過下列的語法，即可在進行備份時，將相關的參數寫入到 Error Log 中進行確認。

DBCC TRACEON (3213, 3605, -1);

輸出結果：

Backup/Restore buffer configuration parameters
Memory limit:               12151 MB
BufferCount:                7
Sets Of Buffers:            1
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         7 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:         7
Media Buffer size:          1024 KB

最後是否還有其他的方式，其實還是有一招的，就是透過 Windows 的軟體式陣列來達到，所以我用二顆 EBS Volume 來組成一個 Disk Array 0進行，並將測試的結果分享如下。

1. 透過 DiskSpd 進行測試。

2. 透過 SQL Server 預設值進行備份 (不調整任何的參數)。

BACKUP DATABASE [StackOverflow2013] TO  DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT, NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE successfully processed 6165673 pages in 99.999 seconds (481.697 MB/sec).

從上述的測試上來看，的確二顆 EBS Volume 綁成一顆後，可以看到很好的效果，而且從數字上來看也是有很大的成長，而且 Thoughtput 與 IOPS 可以成長到 8000+8000 與 500MB + 500MB 的效果，你要知道，如果你要執行 1000 MB 的 Throughput 時，至少 IOPS 要設定至 64,000 以上才可以，但這個方式就可以用較少的成本達到效果，但我強烈的不建議將 SQL Server Data File 放在此 Disk Array 上，因為一來這是一顆 Disk Array 0，再來軟體式的沒有硬體式的穩定，所以請特別注意使用的方式，千萬不要誤用，很重要!!!很重要!!!很重要!!!

上述的一系列的調整與測試的整理，希望可以提供給大家參考，如果有其他更好的方式，也歡迎提供。

參考連結：
=============
[1]. How to Download the Stack Overflow Database
https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/
[2]. BACKUP (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
[3]. How It Works: How does SQL Server Backup and Restore select transfer sizes
https://techcommunity.microsoft.com/t5/sql-server-support-blog/how-it-works-how-does-sql-server-backup-and-restore-select/ba-p/315454