🥇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。

來源： www.habr.com

MS SQL Server：加強備份

添加評論 取消回复

添加評論取消回复