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

?

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

? 現在，不要混淆了收縮事務日志文件和收縮數據文件，當事務日志文件的增長失控或為了移除過多的VLF碎片（這里和這里看到金佰利的優秀文章），然而，收縮事務日志數據文件不要頻繁使用（罕見的操作）并且不應是你執行定期維護計劃的一部分。

? 收縮數據文件應該執行得甚至更少。這就是為什么——數據文件收縮導致產生了大量索引碎片，讓我用一個簡單并且你可以運行的腳步來演示。下面的腳本將會創建一個數據文件，創建一個10MB大小的“filler”表，一個10MB大小的“production”聚簇索引，然后分析新建的聚集索引的碎片情況。?

Code Snippet

USE [master];

GO

?

IF DATABASEPROPERTYEX(N'DBMaint2008', N'Version') IS NOT NULL

????DROP DATABASE [DBMaint2008];

GO

?

CREATE DATABASE DBMaint2008;

GO

USE [DBMaint2008];

GO

?

SET NOCOUNT ON;

GO

?

-- Create the 10MB filler table at the 'front' of the data file

CREATE TABLE [FillerTable](

????[c1] INT IDENTITY,

????[c2] CHAR (8000) DEFAULT 'filler');

GO

?

-- Fill up the filler table

INSERT INTO [FillerTable] DEFAULT VALUES;

GO 1280

?

-- Create the production table, which will be 'after' the filler table in the data file

CREATE 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 table

SELECT

????[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表，運行收縮數據文件命令后，重新分析聚集索引的碎片化。

SQL Code Two

-- Drop the filler table, creating 10MB of free space at the 'front' of the data file

DROP TABLE [FillerTable];

GO

?

-- Shrink the database

DBCC SHRINKDATABASE([DBMaint2008]);

GO

?

-- Check the index fragmentation again

SELECT

????[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的頁代替重建一個新的索引在索引重建操作中。

底線 — — 盡量避免不惜一切代價運行數據文件收縮

------------------------------------------------分割線----------------------------------------

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

支持原著，也希望大家支持我辛苦的翻譯勞動，請加上鏈接瀟湘隱者博客。

?

總結

以上是生活随笔為你收集整理的[翻译]为什么你不要收缩数据库文件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。