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

  我最大的一個(gè)熱點(diǎn)問題是關(guān)于收縮數(shù)據(jù)文件，雖然在微軟的時(shí)候，我自己寫了相關(guān)收縮數(shù)據(jù)文件代碼，我再也沒有機(jī)會(huì)去重寫它，讓它操作起來更方便。我真的不喜歡收縮。

  現(xiàn)在，不要混淆了收縮事務(wù)日志文件和收縮數(shù)據(jù)文件，當(dāng)事務(wù)日志文件的增長失控或?yàn)榱艘瞥^多的VLF碎片（這里和這里看到金佰利的優(yōu)秀文章），然而，收縮事務(wù)日志數(shù)據(jù)文件不要頻繁使用（罕見的操作）并且不應(yīng)是你執(zhí)行定期維護(hù)計(jì)劃的一部分。

  收縮數(shù)據(jù)文件應(yīng)該執(zhí)行得甚至更少。這就是為什么——數(shù)據(jù)文件收縮導(dǎo)致產(chǎn)生了大量索引碎片，讓我用一個(gè)簡單并且你可以運(yùn)行的腳步來演示。下面的腳本將會(huì)創(chuàng)建一個(gè)數(shù)據(jù)文件，創(chuàng)建一個(gè)10MB大小的“filler”表，一個(gè)10MB大小的“production”聚簇索引，然后分析新建的聚集索引的碎片情況。 

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

執(zhí)行結(jié)果如下

聚集索引的邏輯碎片在收縮數(shù)據(jù)文件前大約接近0.4%。[但是我測試結(jié)果是0.54%，如上圖所示,不過也算是接近0.4%]

現(xiàn)在我刪除filter表，運(yùn)行收縮數(shù)據(jù)文件命令后，重新分析聚集索引的碎片化。

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

下面是我的執(zhí)行結(jié)果，作者執(zhí)行結(jié)果，請看原文：

原文：

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.

譯文：

哇，真是恐怖！數(shù)據(jù)文件收縮后，索引的邏輯碎片幾乎接近100%，收縮數(shù)據(jù)文件導(dǎo)致了索引的完全碎片化。消除了任何關(guān)于它的有效范圍掃描的機(jī)會(huì)，確保所有執(zhí)行提前讀范圍掃描的 I/O 在單頁的 I/O操作
為什么會(huì)這樣呢？ 當(dāng)單個(gè)數(shù)據(jù)文件收縮操作一次后，它會(huì)用GAM位圖索引找出數(shù)據(jù)文件中分配最高的頁，然后盡可能的向前移動(dòng)到文件能夠移動(dòng)的地方，就這樣子，在上面的例子中，它完全反轉(zhuǎn)了聚集索引，讓它從非碎片化到完全碎片化。
同樣的代碼用于DBCC SHRINKFILE， DBCC SHRINKDATABASE，以及自動(dòng)收縮，他們同樣糟糕，就像索引的碎片化，數(shù)據(jù)文件的收縮同樣產(chǎn)生了大量的I/O操作，耗費(fèi)大量的CPU資源，并且生成了*load*事務(wù)日志，因?yàn)槿魏尾僮鞫紩?huì)全部記錄下來。
數(shù)據(jù)文件收縮決不能作為定期維護(hù)的一部分，你決不能啟用“自動(dòng)收縮”屬性，我嘗試把它從SQL 2005和SQL 2008產(chǎn)品中移除，它還存在的唯一原因是為了更好的向前兼容，不要掉入這樣的陷阱：創(chuàng)建一個(gè)維護(hù)計(jì)劃，重新生成所有索引，然后嘗試回收重建索引耗費(fèi)的空間采取收縮數(shù)據(jù)文件 — — 這就是你做的生成了大量事務(wù)日志，但實(shí)質(zhì)沒有提高性能的零和游戲。
所以，你為什么要運(yùn)行一個(gè)收縮呢，？舉例來說，如果你把一個(gè)相當(dāng)大的數(shù)據(jù)庫刪除了相當(dāng)大的比例，該數(shù)據(jù)庫不太可能增長，或者你需要轉(zhuǎn)移一個(gè)數(shù)據(jù)庫文件前先清空數(shù)據(jù)文件？

譯文：

我很想推薦的方法如下：

創(chuàng)建一個(gè)新的文件組
將所有受影響的表和索引移動(dòng)到一個(gè)新的文件組用CREATE INDEX ... WITH (DROP_EXISTING=ON)的腳本，在移動(dòng)表的同時(shí)，刪除表中的碎片。
刪掉那些你準(zhǔn)備收縮的舊文件組，你反正要收縮（或縮小它的方式下來，如果它的主文件組）。
基本上你需要提供一些更多的空間，才可以收縮的舊文件，但它是一個(gè)更清晰的設(shè)置。

原文：

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.

如果你完全沒有選擇需要收縮日志文件，請注意這個(gè)操作會(huì)導(dǎo)致索引的碎片化，你應(yīng)該在收縮數(shù)據(jù)文件采取一些步驟消除它可能導(dǎo)致的性能問題，唯一的方式是用DBCC INDEXDEFPAGE或 ALTER INDEX ...REORGANIZE消除索引的碎片不要引起數(shù)據(jù)文件的增長,這些命令要求擴(kuò)展空間8KB的頁代替重建一個(gè)新的索引在索引重建操作中。
底線 — — 盡量避免不惜一切代價(jià)運(yùn)行數(shù)據(jù)文件收縮

所以，還在用作業(yè)定期收縮數(shù)據(jù)文件或數(shù)據(jù)庫開啟了“自動(dòng)收縮”屬性的朋友們，請及時(shí)糾正你們的錯(cuò)誤認(rèn)識吧！

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

最新評論