為什麼你不要收縮資料庫檔案(國外翻譯)，收縮資料庫國外翻譯

為什麼你不要收縮資料庫檔案(國外翻譯)，收縮資料庫國外翻譯

前言，這幾天查看了很多關於SQL SERVER收縮資料檔案方面的文章，準備寫一篇關於收縮日誌方面的文章，但是突然有種衝動將看過經典的文章翻譯出來，下面這篇文章是翻譯的是Paul Randal – “Why You Should Not Shrink Your Data Files”。有些比較難以翻譯、清晰的地方，我會貼上原文。好了，不囉嗦了，直接看下面的翻譯吧。 

  我最大的一個熱點問題是關於收縮資料檔案，雖然在微軟的時候，我自己寫了相關收縮資料檔案代碼，我再也沒有機會去重寫它，讓它操作起來更方便。我真的不喜歡收縮。

  現在，不要混淆了收縮交易記錄檔和收縮資料檔案，當交易記錄檔的增長失控或為了移除過多的VLF片段（這裡和這裡看到金佰利的優秀文章），然而，收縮交易記錄資料檔案不要頻繁使用（罕見的操作）並且不應是你執行定期維護計劃的一部分。

  收縮資料檔案應該執行得甚至更少。這就是為什麼——資料檔案收縮導致產生了大量索引片段，讓我用一個簡單並且你可以啟動並執行腳步來示範。下面的指令碼將會建立一個資料檔案，建立一個10MB大小的“filler”表，一個10MB大小的“production”聚簇索引，然後分析建立的叢集索引的片段情況。 

USE [master];GO IF DATABASEPROPERTYEX(N'DBMaint2008', N'Version') IS NOT NULL  DROP DATABASE [DBMaint2008];GO CREATE DATABASE DBMaint2008;GOUSE [DBMaint2008];GO SET NOCOUNT ON;GO -- Create the 10MB filler table at the 'front' of the data fileCREATE TABLE [FillerTable](  [c1] INT IDENTITY,  [c2] CHAR (8000) DEFAULT 'filler');GO -- Fill up the filler tableINSERT INTO [FillerTable] DEFAULT VALUES;GO 1280 -- Create the production table, which will be 'after' the filler table in the data fileCREATE TABLE [ProdTable](  [c1] INT IDENTITY,  [c2] CHAR (8000) DEFAULT 'production');CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable]([c1]);GO INSERT INTO [ProdTable] DEFAULT VALUES;GO 1280 -- Check the fragmentation of the production tableSELECT  [avg_fragmentation_in_percent]FROM sys.dm_db_index_physical_stats(  DB_ID(N'DBMaint2008'), OBJECT_ID(N'ProdTable'), 1, NULL, 'LIMITED');GO

執行結果如下

叢集索引的邏輯片段在收縮資料檔案前大約接近0.4%。[但是我測試結果是0.54%，如所示,不過也算是接近0.4%]

現在我刪除filter表，運行收縮資料檔案命令後，重新分析叢集索引的片段化。

-- Drop the filler table, creating 10MB of free space at the 'front' of the data fileDROP TABLE [FillerTable];GO -- Shrink the databaseDBCC SHRINKDATABASE([DBMaint2008]);GO -- Check the index fragmentation againSELECT  [avg_fragmentation_in_percent]FROM sys.dm_db_index_physical_stats(  DB_ID(N'DBMaint2008'), OBJECT_ID(N'ProdTable'), 1, NULL, 'LIMITED');GO

下面是我的執行結果，作者執行結果，請看原文：

原文：

Wow! After the shrink, the logical fragmentation is almost 100%. The shrink operation *completely* fragmented the index, removing any chance of efficient range scans on it by ensuring the all range-scan readahead I/Os will be single-page I/Os.

譯文：

哇，真是恐怖！資料檔案收縮後，索引的邏輯片段幾乎接近100%，收縮資料檔案導致了索引的完全片段化。消除了任何關於它的有效範圍掃描的機會，確保所有執行提前讀範圍掃描的 I/O 在單頁的 I/O操作
為什麼會這樣呢？ 當單個資料檔案收縮操作一次後，它會用GAM位元影像索引找出資料檔案中分配最高的頁，然後儘可能的向前移動到檔案能夠移動的地方，就這樣子，在上面的例子中，它完全反轉了叢集索引，讓它從非片段化到完全片段化。
同樣的代碼用於DBCC SHRINKFILE， DBCC SHRINKDATABASE，以及自動收縮，他們同樣糟糕，就像索引的片段化，資料檔案的收縮同樣產生了大量的I/O操作，耗費大量的CPU資源，並且產生了*load*交易記錄，因為任何操作都會全部記錄下來。
資料檔案收縮決不能作為定期維護的一部分，你決不能啟用“自動收縮”屬性，我嘗試把它從SQL 2005和SQL 2008產品中移除，它還存在的唯一原因是為了更好的向前相容，不要掉入這樣的陷阱：建立一個維護計劃，重建所有索引，然後嘗試回收重建索引耗費的空間採取收縮資料檔案 — — 這就是你做的產生了大量交易記錄，但實質沒有提高效能的零和遊戲。
所以，你為什麼要運行一個收縮呢，？舉例來說，如果你把一個相當大的資料庫刪除了相當大的比例，該資料庫不太可能增長，或者你需要轉移一個資料庫檔案前先清空資料檔案？

譯文：

我很想推薦的方法如下：

建立一個新的檔案組
將所有受影響的表和索引移動到一個新的檔案組用CREATE INDEX ... WITH (DROP_EXISTING=ON)的指令碼，在移動表的同時，刪除表中的片段。
刪掉那些你準備收縮的舊檔案組，你反正要收縮（或縮小它的方式下來，如果它的主檔案組）。
基本上你需要提供一些更多的空間，才可以收縮的舊檔案，但它是一個更清晰的設定。

原文：

The method I like to recommend is as follows:

Create a new filegroup
Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
Basically you need to provision some more space before you can shrink the old files, but it's a much cleaner mechanism.

如果你完全沒有選擇需要收縮記錄檔，請注意這個操作會導致索引的片段化，你應該在收縮資料檔案採取一些步驟消除它可能導致的效能問題，唯一的方式是用DBCC INDEXDEFPAGE或 ALTER INDEX ...REORGANIZE消除索引的片段不要引起資料檔案的增長,這些命令要求擴充空間8KB的頁代替重建一個新的索引在索引重建操作中。
底線 — — 盡量避免不惜一切代價運行資料檔案收縮

所以，還在用作業定期收縮資料檔案或資料庫開啟了“自動收縮”屬性的朋友們，請及時糾正你們的錯誤認識吧！

支援原著，也希望大家支援我辛苦的翻譯勞動，請加上連結瀟湘隱者部落格。