1.2 數據文件空間使用與管理

在1.1.1節里，討論過數據文件的結構，以及它們的管理方法。但是不同類型的用戶數據是怎么存儲在數據文件里的呢？用不同的存儲結構存儲等量的數據，其空間消耗相等么？當用戶添加或者修改數據以后，數據的存儲會發生什么樣的變化？當用戶把數據刪除之后，數據原先所占用的空間一定會釋放出來么？要有效地管理數據文件空間，管理員必須能夠回答這些問題。

本小節會先討論數據文件里表和索引的存儲結構（1.2.1節），以及不同的存儲結構會對數據空間使用產生什么樣的影響（1.2.2節）。清除歷史數據，可以使用DELETE語句，也可以使用TRUNCATE語句，但是這兩種方法得到的效果可能是不一樣的。而最后的效果，和存儲結構也有關系。這部分內容，在1.2.3節里，會做詳細討論。數據文件空間管理的一個比較難的問題，是如何縮小或清空一個現有的數據文件。這需要綜合運用前面介紹的各種知識。在1.2.4節里，會做討論。

1.2.1 表和索引存儲結構

前面講過，在數據文件中，數據以8KB的方式存儲。那這些頁面是怎么組織的呢？這就要談到表格和索引的組織了。

在SQL Server 2005以前，一個表格是以一個B樹或者一個堆（Heap）存放的。每個B樹或者堆，在sysindexes里面都有一條記錄相對應。SQL Server 2005以后，引入了分區表（Table Partition）的概念。在存儲組織上，現在的分區基本上替代了原來表格的概念。一個分區就是一個B樹或者一個堆。而一張表格則是一個到多個分區的組合（見圖1-22）。

SQL Server使用下列三種方法之一來組織其分區中的數據或索引頁：

1.用B樹存儲有聚集索引的表數據頁。

如果一個表格上有聚集索引（Clustered Index），數據行將基于聚集索引鍵按順序存儲。聚集索引按B樹索引結構實現，B樹索引結構支持基于聚集索引鍵值對行進行快速檢索。數據頁面之間用雙向鏈表，緊密相連。

圖1-22 表格的存儲組織結構

2.堆是沒有聚集索引的表。

如果表格上沒有聚集索引，數據行將不按任何特殊的順序存儲，數據頁也沒有任何特殊的順序。數據頁之間沒有鏈表鏈接。

3.非聚集索引。

非聚集索引與聚集索引有一個相似的B樹索引結構。不同的是，非聚集索引不影響數據行的順序。葉級別僅包含索引行，沒有完整的數據。每個索引行包含非聚集鍵值和行定位符。定位符指向（在另一個B樹或者堆中）包含鍵值的數據行。非聚集索引本身也會占用一些數據頁。這些頁面以雙向鏈表相連。

sys.partitions為表或索引中每個分區返回一行。

1.每個堆在sys.partitions中有一行記錄，其index_id = 0。

sys.system_internals_allocation_units中的first_iam_page列指向指定分區中堆數據頁集合的IAM鏈。因為這些頁沒有鏈接，不能從第一頁找到下一頁，所以SQL Server只好使用IAM頁查找數據頁集合中的每一頁。

2.每個表或視圖的聚集索引在sys.partitions中有一行記錄，其index_id = 1。

sys.system_internals_allocation_units中的root_page列指向指定分區內聚集索引B樹的頂端。SQL Server使用索引B樹鏈表能夠從頂端頁面查找到分區中的每個數據頁。

3.為表或視圖創建的每個非聚集索引在sys.partitions中有一行記錄，其index_id > 1。

sys.system_internals_allocation_units中的root_page列指向指定分區內非聚集索引B樹的頂端。

至少有一個LOB列（例如text或image字段）的每個表在sys.partitions中也另外再有一行，其index_id > 250，用以管理LOB頁面。

first_iam_page列指向管理LOB_DATA分配單元中的頁的IAM頁鏈。

換而言之，從一個對象的index_id就能判斷出它是什么類型的存儲方式。如果是0，就說明這張表沒有聚集索引；如果是1，就是聚集索引頁面；如果是大于250，就是text或者image字段；如果在2和250之間，就是非聚集索引頁面。

堆結構

堆是不含聚集索引的表。SQL Server使用“索引分配映射（IAM）”頁將堆的頁面聯系在一起。堆的特點有以下幾個：

1.堆內的數據頁和行沒有任何特定的順序。

在一個堆里的數據完全是隨機存放的。而且SQL Server也假設數據之間沒有任何聯系。

2.頁面也不鏈接在一起。

數據頁之間唯一的邏輯連接是記錄在IAM頁內的信息。頁面與頁面之間沒有什么緊密的聯系。

3.堆中的行一般不按照插入的順序返回。

因為IAM按數據頁在數據文件內存在的順序標示它們，所以這意味著堆掃描會沿每個文件進行。而不是按這些行的插入順序，或者是任何邏輯上的順序。

圖1-23表現了SQL Server數據庫引擎如何使用IAM頁檢索具有單個分區的堆中的數據行。

圖1-23 使用IAM頁檢索堆中的數據行

從上面的介紹我們可以看到，SQL Server對堆的管理是比較簡單的。在算法能力上也是比較弱的。不談性能，光從數據存儲管理上來講，用堆去管理一個超大的表格是比較吃力的。所以在SQL Server里，筆者強烈建議在所有大的、經常使用的表格上都建立聚集索引。聚集索引可以幫助避免很多問題。

聚集索引結構

在SQL Server中，索引是按B樹結構進行組織的。索引B樹中的每一頁稱為一個索引節點。B樹的頂端節點稱為根節點。索引中的底層節點稱為葉節點。根節點與葉節點之間的任何索引級別統稱為中間級。每個索引行包含一個鍵值和一個指針，該指針指向B樹上的某一中間級頁或葉級索引中的某個數據行。每級索引中的頁均被鏈接在雙向鏈接列表中。

數據鏈內的頁和行將按聚集索引鍵值進行排序。所有插入操作都在所插入行中的鍵值與現有行中的排序順序相匹配時執行。B樹頁集合由sys.system_internals_allocation_units系統視圖中的頁指針來定位。

對于某個聚集索引，sys.system_internals_allocation_units中的root_page列指向該聚集索引某個特定分區的頂部。SQL Server將在索引中向下移動以查找與某個聚集索引鍵對應的行。為了查找鍵的范圍，SQL Server將在索引中移動以查找該范圍的起始鍵值，然后用向前或向后指針在數據頁中進行掃描。為了查找數據頁鏈的首頁，SQL Server將從索引的根節點沿最左邊的指針進行掃描。

圖1-24顯示了聚集索引單個分區中的結構。

相對于堆，聚集索引的特點有以下幾個：

1.堆內的數據頁和行有嚴格的順序。

聚集索引保證了表格的數據按照索引行的順序排列。而且SQL Server知道這種順序關系。

2.頁面鏈接在一起。頁面與頁面聯系緊密。

3.樹中的行一般能夠按照索引列的順序返回。

從上面的比較我們也能看出來，建立了B樹以后，SQL Server對數據頁的管理能夠更加快速有效。有些會發生在堆上的問題就不容易在B樹上發生。

圖1-24 聚集索引單個分區的結構

非聚集索引結構

非聚集索引與聚集索引具有相同的B樹結構，它們之間的顯著差別在于以下兩點：

● 基礎表的數據行不按非聚集鍵的順序排序和存儲。

● 非聚集索引的葉層是由索引頁而不是由數據頁組成。

● 建立非聚集索引的表可以是一個B樹，也可以是一個堆。

● 如果表是堆（意味著該表沒有聚集索引），則行定位器是指向行的指針。該指針由文件標識符（ID）、頁碼和頁上的行數生成。整個指針稱為行ID（RID）。

● 如果表有聚集索引或索引視圖上有聚集索引，則行定位器是行的聚集索引鍵。如果聚集索引不是唯一的索引，SQL Server將添加在內部生成的值（稱為唯一值）以使所有重復鍵唯一。SQL Server通過使用存儲在非聚集索引的葉行內的聚集索引鍵搜索聚集索引來檢索數據行。

所以非聚集索引不會去改變或改善數據頁的存儲模式。它的B樹結構只針對自己的索引頁面。如果問題是由堆的特性導致的，加一個非聚集索引不能帶來根本的改善。

圖1-25說明了單個分區中的非聚集索引結構。

圖1-25 單個分區中的非聚集索引結構

1.2.2 比較存儲結構對空間使用的影響

讓我們通過創建3張結構相同，只是索引配置不同的表格，并且插入同樣數量的記錄，來比較存儲結構對最終空間使用的影響。

1.首先我們先創建一個和[Sales].[SalesOrderDetail]同結構的表格，這個表格上沒有一個索引，所以它是一個堆。

    USE [AdventureWorks]
    GO
    drop table [Sales].[SalesOrderDetail_hash]
    GO
    CREATE TABLE [Sales].[SalesOrderDetail_hash](
        [SalesOrderID] [int] NOT NULL,
        [SalesOrderDetailID] [int] ,
        [CarrierTrackingNumber] [nvarchar](25) NULL,
        [OrderQty] [smallint] NOT NULL,
        [ProductID] [int] NOT NULL,
        [SpecialOfferID] [int] NOT NULL,
        [UnitPrice] [money] NOT NULL,
        [UnitPriceDiscount] [money] NOT NULL ,
        [LineTotal] numeric (38,6),
        [rowguid] [uniqueidentifier] ,
        [ModifiedDate] [datetime]
    ) ON [PRIMARY]
    GO
    insert into [Sales].[SalesOrderDetail_hash]
    select * from [Sales].[SalesOrderDetail]
    go
    dbcc showcontig('[Sales].[SalesOrderDetail_hash]')
    go

DBCC SHOWCONTIG的結果顯示這個表格共使用了1 494個頁，190個區。

    DBCC SHOWCONTIG scanning 'SalesOrderDetail_hash' table...
    Table: 'SalesOrderDetail_hash' (1947153982); index ID: 0, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 1494
    - Extents Scanned..............................: 190
    - Extent Switches..............................: 189
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 98.42% [187:190]
    - Extent Scan Fragmentation ...................: 4.74%
    - Avg. Bytes Free per Page.....................: 52.0
    - Avg. Page Density (full).....................: 99.36%

2.現在我們創建一個同樣的，但是有聚集索引的表格。所以這是一棵B樹。

    USE [AdventureWorks]
    GO
    drop table [Sales].[SalesOrderDetail_C]
    GO
    CREATE TABLE [Sales].[SalesOrderDetail_C](
        [SalesOrderID] [int] NOT NULL,
        [SalesOrderDetailID] [int] ,
        [CarrierTrackingNumber] [nvarchar](25) NULL,
        [OrderQty] [smallint] NOT NULL,
        [ProductID] [int] NOT NULL,
        [SpecialOfferID] [int] NOT NULL,
        [UnitPrice] [money] NOT NULL,
        [UnitPriceDiscount] [money] NOT NULL ,
        [LineTotal] numeric (38,6),
        [rowguid] [uniqueidentifier] ,
        [ModifiedDate] [datetime],
    CONSTRAINT [PK_SalesOrderDetailC_SalesOrderID_SalesOrderDetailID] PRIMARY KEY
    CLUSTERED
    (
        [SalesOrderID] ASC,
        [SalesOrderDetailID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    insert into [Sales].[SalesOrderDetail_C]
    select * from [Sales].[SalesOrderDetail]
    go
    dbcc showcontig('[Sales].[SalesOrderDetail_C]')WITH ALL_INDEXES
    go

DBCC SHOWCONTIG的結果顯示這個表格共使用了1 494個頁，190個區。所以建立聚集索引并沒有增加新的空間存儲需求。

    DBCC SHOWCONTIG scanning 'SalesOrderDetail_C' table...
    Table: 'SalesOrderDetail_C' (1963154039); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 1494
    - Extents Scanned..............................: 190
    - Extent Switches..............................: 189
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 98.42% [187:190]
    - Logical Scan Fragmentation ..................: 0.47%
    - Extent Scan Fragmentation ...................: 24.21%
    - Avg. Bytes Free per Page.....................: 52.0
    - Avg. Page Density (full).....................: 99.36%

3.現在我們創建一個同樣的，但是主鍵建立在非聚集索引上的表格。所以它是一個堆加一個B樹。

    USE [AdventureWorks]
    GO
    drop table [Sales].[SalesOrderDetail_N]
    GO
    CREATE TABLE [Sales].[SalesOrderDetail_N](
        [SalesOrderID] [int] NOT NULL,
        [SalesOrderDetailID] [int] ,
        [CarrierTrackingNumber] [nvarchar](25) NULL,
        [OrderQty] [smallint] NOT NULL,
        [ProductID] [int] NOT NULL,
        [SpecialOfferID] [int] NOT NULL,
        [UnitPrice] [money] NOT NULL,
        [UnitPriceDiscount] [money] NOT NULL ,
        [LineTotal] numeric (38,6),
        [rowguid] [uniqueidentifier] ,
        [ModifiedDate] [datetime],
    CONSTRAINT [PK_SalesOrderDetailN_SalesOrderID_SalesOrderDetailID] PRIMARY KEY
    nonclustered
    (
        [SalesOrderID] ASC,
        [SalesOrderDetailID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    insert into [Sales].[SalesOrderDetail_N]
    select * from [Sales].[SalesOrderDetail]
    go
    dbcc showcontig('[Sales].[SalesOrderDetail_N]')WITH ALL_INDEXES
    go

DBCC SHOWCONTIG的結果顯示這個表格空間使用有兩塊：堆和B樹。堆共使用了1 494個頁，190個區。B樹使用了285個頁，38個區。所以加起來這個表一共用了1 494+285=1 779頁，190+38=228區。

    DBCC SHOWCONTIG scanning 'SalesOrderDetail_N' table...
    Table: 'SalesOrderDetail_N' (2027154267); index ID: 0, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 1494
    - Extents Scanned..............................: 189
    - Extent Switches..............................: 188
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 98.94% [187:189]
    - Extent Scan Fragmentation ...................: 21.69%
    - Avg. Bytes Free per Page.....................: 52.0
    - Avg. Page Density (full).....................: 99.36%
    DBCC SHOWCONTIG scanning 'SalesOrderDetail_N' table...
    Table: 'SalesOrderDetail_N' (2027154267); index ID: 2, database ID: 5
    LEAF level scan performed.
    - Pages Scanned................................: 285
    - Extents Scanned..............................: 38
    - Extent Switches..............................: 37
    - Avg. Pages per Extent........................: 7.5
    - Scan Density [Best Count:Actual Count].......: 94.74% [36:38]
    - Logical Scan Fragmentation ..................: 1.05%
    - Extent Scan Fragmentation ...................: 94.74%
    - Avg. Bytes Free per Page.....................: 8.2
    - Avg. Page Density (full).....................: 99.90%

用表1-4可以比較3種結構的不同。

表1-4 等量數據的三種存儲方式比較

可以看到，在同樣的字段上，建立聚集索引并沒有增加表格的大小。而建立非聚集索引卻增加了不小的空間。

有一種說法，當一個表格經常發生變化時，如果在這張表上建立聚集索引，會容易遇到頁拆分（page split）。所以建立聚集索引會影響性能。基于這種考慮，很多數據庫設計者不愿意在SQL Server的表格上建立聚集索引。但是一張表不建索引性能又不能接受，所以他們又加了一些非聚集索引，以期得到好的性能。

SQL Server的這種堆和樹的存儲方式，決定了上面這種設計是一個既浪費空間，性能也不一定好的設計。剛才的測試就說明了空間上的浪費。最近SQL Server產品組在SQL Server 2005上做了一個比較，對比有聚集索引和沒有聚集索引的表格在SELECT、INSERT、UPDATE、DELETE上的性能。因為SELECT、UPDATE、DELETE有記錄搜尋的動作，所以很自然的，有聚集索引大大提高了性能。但出人意料的是，在INSERT這一項上，兩者也沒什么差別。并沒有出現聚集索引影響INSERT速度的現象。所以再次強烈建議，在一個大的表格上一定要建一個聚集索引，除非你的工作負荷壓力測試顯示出相反的結果。

1.2.3 DELETE和TRUNCATE之間的區別（KB913399）

在壓縮或清空數據文件之前，首先要先清除不再需要的數據。很自然地，會想到使用DELETE命令。DELETE命令可以刪掉不要的記錄，但是DELETE命令真的能釋放所有這些記錄申請的空間么？用我們在1.2.2節練習中創建的3張表作例子，繼續分析它們的不同。假設刪除所有的記錄。

    delete [Sales].[SalesOrderDetail_hash]
    go
    delete [Sales].[SalesOrderDetail_C]
    go
    delete [Sales].[SalesOrderDetail_N]
    go

再用DBCC SHOWCONTIG看看是不是空間都釋放出來了。

堆結構（[Sales].[SalesOrderDetail_hash]）還保留了82個頁面，14個區。

    DBCC SHOWCONTIG scanning 'SalesOrderDetail_hash' table...
    Table: 'SalesOrderDetail_hash' (1947153982); index ID: 0, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 82
    - Extents Scanned..............................: 14
    - Extent Switches..............................: 13
    - Avg. Pages per Extent........................: 5.9
    - Scan Density [Best Count:Actual Count].......: 78.57% [11:14]
    - Extent Scan Fragmentation ...................: 21.43%
    - Avg. Bytes Free per Page.....................: 7946.6
    - Avg. Page Density (full).....................: 1.82%

B樹上（[Sales].[SalesOrderDetail_C]）只保留了1個頁面，1個區。

    DBCC SHOWCONTIG scanning 'SalesOrderDetail_C' table...
    Table: 'SalesOrderDetail_C' (1963154039); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 1
    - Extents Scanned..............................: 1
    - Extent Switches..............................: 0
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 7983.0
    - Avg. Page Density (full).....................: 1.37%

堆+B樹的結構（[Sales].[SalesOrderDetail_N]）在兩個數據結構上都有保留。

    DBCC SHOWCONTIG scanning 'SalesOrderDetail_N' table...
    Table: 'SalesOrderDetail_N' (2027154267); index ID: 0, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 82
    - Extents Scanned..............................: 13
    - Extent Switches..............................: 12
    - Avg. Pages per Extent........................: 6.3
    - Scan Density [Best Count:Actual Count].......: 84.62% [11:13]
    - Extent Scan Fragmentation ...................: 15.38%
    - Avg. Bytes Free per Page.....................: 7946.6
    - Avg. Page Density (full).....................: 1.82%
    DBCC SHOWCONTIG scanning 'SalesOrderDetail_N' table...
    Table: 'SalesOrderDetail_N' (2027154267); index ID: 2, database ID: 5
    LEAF level scan performed.
    - Pages Scanned................................: 1
    - Extents Scanned..............................: 1
    - Extent Switches..............................: 0
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 8077.0
    - Avg. Page Density (full).....................: 0.21%

下面用表1-5再來比較一下。

表1-5 3種存儲方式下剩余頁面和區數量比較

從上面的測試可以看出，DELETE命令并不能完全釋放表格或索引的數據結構以及它們申請的頁面。在這一點上，SQL Server 2005以后的版本比以前的版本做得好一些，樹比堆做得更好一些。

SQL Server還提供了另一條命令。如果整張表的數據都不要了，可以用TRUNCATE命令。與DELETE語句相比，TRUNCATE TABLE具有以下優點：

1.所用的事務日志空間較少。

DELETE語句每次刪除一行，并在事務日志中為所刪除的每行記錄一個項。TRUNCATE TABLE通過釋放用于存儲表數據的數據頁來刪除數據，并且在事務日志中只記錄頁釋放這個動作，而不記錄每一行。

2.使用的鎖通常較少。

當使用行鎖執行DELETE語句時，將鎖定表中各行以便刪除。TRUNCATE TABLE始終鎖定表和頁，而不是鎖定各行。

3.表中將毫無例外地不留下任何頁。

執行DELETE語句后，表仍會包含空頁。例如，必須至少使用一個排他（LCK_M_X）表鎖，才能釋放堆中的空頁。如果執行刪除操作時沒有使用表鎖，表（堆）中將包含許多空頁。對于索引，刪除操作會留下一些空頁，盡管這些頁會通過后臺清除進程迅速釋放。

TRUNCATE TABLE刪除表中的所有行，但表結構及其列、約束、索引等保持不變。若要刪除表定義及其數據，請使用DROP TABLE語句。

在上面3張表格上，如果運行TRUNCATE TABLE命令，DBCC SHOWCONTIG就都返回0了。

所以，為了更及時地刪除數據，釋放空間，可以采用的方法有：

（1）在表格上建立聚集索引。

（2）如果所有數據都不要了，要使用TRUNCATE TABLE而不是DELETE。

（3）如果表格本身不要了，就直接DROP TABLE。

看到這個問題有些用戶不禁要有些擔心，我DELETE了數據，SQL Server卻沒有完全釋放空間，這不是空間泄漏么？久而久之，我的數據庫里會不會充斥著這些“沒用”的頁面，把我的空間都浪費掉了？這倒不必擔心，雖然這些頁面沒有被釋放掉，但當表格里插入新的數據時，這些頁面是會被重新使用的。所以這些頁面并沒有被“泄漏”掉，會留給SQL Server重用。

如果真的看著這些頁面礙事，而表格又不能整個被刪除掉，處理起來有時候倒有點費事。如果表格有聚集索引，重建一下索引能釋放掉這些頁面，還是挺簡單的。但是如果沒有，可能就要重建一張新表，把數據從舊表里倒過去，然后再刪除舊表，釋放空間；或者在這張表上建一個聚集索引。這樣有點折騰。所以，如果表沒用了，就直接刪掉它；如果表還有用，那這些頁面將來再有新數據插入時，還會被利用上。一般沒必要一定要逼著SQL Server把它們釋放出來。

1.2.4 為什么DBCC SHRINKFILE會不起作用

數據庫管理員為了控制文件的大小，可能有時候要收縮文件，或者要把某些數據文件清空以便從數據庫里刪除。這時有兩個命令可供選擇：

    DBCC SHRINKDATABASE
    ( 'database_name' | database_id | 0
    [ ,target_percent ]
    [ , { NOTRUNCATE | TRUNCATEONLY } ] )
    [ WITH NO_INFOMSGS ]
    -- 收縮指定數據庫中的所有數據文件和日志文件的大小。
    DBCC SHRINKFILE
    (     { 'file_name' | file_id }
    { [ , EMPTYFILE ]     | [ [ , target_size ] [
    , { NOTRUNCATE | TRUNCATEONLY } ] ]    }
    ) [ WITH NO_INFOMSGS ]
    -- 收縮當前數據庫指定數據文件或日志文件的大小，
    -- 或者通過將數據從指定的文件移動到相同文件組中的其他文件來清空文件，以允許從數據庫中刪除該文件。

由于DBCC SHRINKDATABASE一次運行會同時影響所有的文件（包括數據文件和日志文件），使用者不能指定每個文件的目標大小，其結果可能不能達到預期的要求。所以建議還是先做好規劃，對每個文件確定預期目標，然后使用DBCC SHRINKFILE來一個文件一個文件地做比較穩妥。

計劃收縮數據文件時，要考慮到以下幾點：

1.首先要了解數據文件當前的使用情況。

收縮量的大小不可能超過當前文件的空閑空間的大小。如果想要壓縮數據庫的大小，首先就要確認數據文件里的確有相應未被使用的空間。如果空間都在使用中，那就要先確認大量占用空間的對象（表格或索引)。然后通過歸檔歷史數據，先把空間釋放出來。

2.主數據文件（Primary File）是不能被清空的。能被完全清空的只有輔助數據文件。

3.如果要把一個文件組整個清空，要刪除分配在這個文件組上的對象（表格或索引），或者把它們移到其他文件組上。DBCC SHRINKFILE不會幫你做這個工作。

把數據文件里面該清除的數據和對象清除完、確認數據文件（組）有足夠的空閑空間后，管理員就可以下DBCC SHRINKFILE命令來縮小或清空指定文件了。如果是要縮小文件，就填上需要的tearget_size，如果是要清空文件，就選擇EMPTYFILE。SQL Server在做DBCC SHRINKFILE的時候，會掃描數據文件并對正在讀的頁面加鎖，所以對數據庫的性能會有所影響。但是這不是一個獨占的行為。在做SHINKFILE的時候，其他用戶照樣可以對數據庫進行讀寫操作。所以不需要安排專門的服務器停機時間來做，一般在數據庫維護的時段就可以進行。可以在進程中的任一點停止DBCC SHRINKFILE操作，任何已完成的工作都將保留。如果操作沒有在規定的時間內完成，也可以安全地停止它。

可是，有時候明明看到數據文件里有空間，為什么就是不能壓縮或者清空它呢？這通常是因為數據文件里面雖然有很多空的頁面，但是這些頁面分散在各個區里，使得整個文件沒有很多空的區。

需要說明的是，DBCC SHRINKFILE做的，都是區一級的動作。它會把使用過的區前移，把沒在使用中的區從文件中移除。但是，它不會把一個區里面的空頁移除、合并區，也不會把頁面里的空間移除、合并頁面。所以，一個數據庫中有很多只使用了一兩個頁面的區，DBCC SHRINKFILE的效果會不明顯。

下面來舉一個例子。先創建一個空數據庫，創建一個每一行都會占用一個頁面的表格。表格上沒有聚集索引，所以是一個堆。往里面插入8 000條數據。

    create database test_shrink
    go
    use test_shrink
    go
    create table show_extent
    (a int,
    b nvarchar(3900))
    go
    declare @i int
    set @i = 1
    while @i <=1000
    begin
    insert into show_extent values (1, REPLICATE ( N'a' ,3900 ))
    insert into show_extent values (2, REPLICATE ( N'b' ,3900 ))
    insert into show_extent values (3, REPLICATE ( N'c' ,3900 ))
    insert into show_extent values (4, REPLICATE ( N'd' ,3900 ))
    insert into show_extent values (5, REPLICATE ( N'e' ,3900 ))
    insert into show_extent values (6, REPLICATE ( N'f' ,3900 ))
    insert into show_extent values (7, REPLICATE ( N'g' ,3900 ))
    insert into show_extent values (8, REPLICATE ( N'h' ,3900 ))
    set @i = @i +1
    end
    dbcc showcontig('show_extent')
    go

可以看到它申請了8 000頁。

    DBCC SHOWCONTIG scanning 'show_extent' table...
    Table: 'show_extent' (2059154381); index ID: 0, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 8000
    - Extents Scanned..............................: 1004
    - Extent Switches..............................: 1003
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 99.60% [1000:1004]
    - Extent Scan Fragmentation ...................: 4.88%
    - Avg. Bytes Free per Page.....................: 279.0
    - Avg. Page Density (full).....................: 96.55%

現在我們刪除每個區里面的7個頁面，只保留a=5的這些記錄。

    delete show_extent where a <>5
    go
    sp_spaceused show_extent
    go
    dbcc showcontig('show_extent')
    go

出乎我們意料的是，這個表格里現在還有一半的頁面在使用（參見上一節的討論），沒有一個區被釋放。

    name           rows   reserved      data         index_size    unused
    -------------- ------ ----------- ------------ ------------- ---------
    show_extent    1000   64072 KB      32992KB     8KB          31072KB
    DBCC SHOWCONTIG scanning 'show_extent' table...
    Table: 'show_extent' (2059154381); index ID: 0, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 4124
    - Extents Scanned..............................: 1004
    - Extent Switches..............................: 1003
    - Avg. Pages per Extent........................: 4.1
    - Scan Density [Best Count:Actual Count].......: 51.39% [516:1004]
    - Extent Scan Fragmentation ...................: 4.88%
    - Avg. Bytes Free per Page.....................: 6199.0
    - Avg. Page Density (full).....................: 23.41%
    database_name     database_size     unallocated space
    ---------------- ---------------- ------------------
    test_shrink       187.13 MB         0.56 MB
    reserved          data             index_size        unused
    ---------------- ---------------- ---------------- ----------
    65152 KB          33464KB          576KB           31112KB

這時候去SHRINKFILE是沒有效果的。結果就告訴你這個數據文件的正在使用中的大小就是約64 MB（8 160×8KB）。正好就是1 000個區的大小。

    dbcc shrinkfile (1, 40)
    DbId    FileId      CurrentSize MinimumSize UsedPages     EstimatedPages
    ------ ----------- ----------- ----------- ----------- --------------
    9      1            8168        280          8160         8160

面對這種情況怎么辦？如果這個表有一個聚集索引，那么我們可以通過重建索引的方式把頁面重排一次。現在的這個表沒有聚集索引，那么我們為它建一個，也能達到同樣的效果。

    create clustered index show_I
    on show_extent (a)
    go
    dbcc showcontig('show_extent')
    go
    DBCC SHOWCONTIG scanning 'show_extent' table...
    Table: 'show_extent' (2073058421); index ID: 1, database ID: 9
    TABLE level scan performed.
    - Pages Scanned................................: 1000
    - Extents Scanned..............................: 125
    - Extent Switches..............................: 124
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 100.00% [125:125]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 273.0
    - Avg. Page Density (full).....................: 96.63%

索引建立以后，原先存儲在堆里的頁面里的數據以B樹的方式重新存放。原先的頁面被釋放。所以占用的分區也被釋放出來。這時候再去SHRINKFILE就有效果了。

    dbcc shrinkfile (1, 40)
    DbId    FileId   CurrentSize  MinimumSize UsedPages    EstimatedPages
    ------ -------- ----------- ----------- ----------- --------------
    9      1         5120         280          1168        1168

如果實在不想建聚集索引，可能只有把這張表的數據先移走，然后清空表格，再把數據插回來。這實在是比較麻煩。還是有聚集索引管理起來比較方便。

剛才談到的是數據存儲頁面分散在區里，造成的SHRINKFILE效果不佳的情況。在一個有聚集索引的表格上，這種問題可以用重建索引來解決。如果這些區里面放的是text或者image之類的數據類型，SQL Server會用單獨的頁面存放這些數據。如果存儲這一類頁面的區發生了這樣的問題，和堆一樣，做索引重建也不會影響到它們。

那怎么辦呢？簡單的處理方法，就是把這些可能有問題的對象都找出來，然后重建它們。可以利用DBCC EXTENTINFO這個命令打出數據文件里的所有區的分配信息。然后計算每個對象理論上區的數目和實際的數目。如果實際數目遠大于理論的數目，那這個對象就是碎片過多，管理員應該要考慮重建對象。

還是以剛才的test_shrink數據庫為例，在刪除掉除了5以外的其他數據以后，運行下面的查詢。

    use test_shrink
    go
    drop table extentinfo
    go
    create table extentinfo
    ( [file_id] smallint,
    page_id int,
    pg_alloc int,
    ext_size int,
    obj_id int,
    index_id int,
    partition_number int,
    partition_id bigint,
    iam_chain_type varchar(50),
    pfs_bytes varbinary(10) )
    go
    drop proc import_extentinfo
    go
    create procedure import_extentinfo
    as dbcc extentinfo('test_shrink')
    go insert extentinfo
    exec import_extentinfo
    go
    select [file_id],obj_id, index_id, partition_id, ext_size,
    'actual extent count'=count(*), 'actual page count'=sum(pg_alloc),
    'possible extent count'=ceiling(sum(pg_alloc)*1.0/ext_size),
    'possible extents / actual extents' =
    (ceiling(sum(pg_alloc)*1.00/ext_size)*100.00) / count(*)
    from extentinfo
    group by [file_id],obj_id, index_id,partition_id, ext_size
    having count(*)-ceiling(sum(pg_alloc)*1.0/ext_size) > 0
    order by partition_id, obj_id, index_id, [file_id]

不出所料，剛才的那個表格被報告出來。

    file_id obj_id      index_id    partition_id          ext_size
    ------- ----------- ----------- -------------------- -----------
    1    2073058421 1               72057594038386688     8
    actual extent count actual page count possible extent count
    ------------------- ----------------- ---------------------
    1125                 2007               251
    possible extents / actual extents
    ----------------------------------
    22.311111111

找到這些對象后，需要把它們重建。區里的空間碎片就能被釋放出來了。