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
找到这些对象后，需要把它们重建。区里的空间碎片就能被释放出来了。