SQL Server 2008 释放堆表空间
前些天我遇到一个问题，一个200Gb的库，其中一个表大约2000W行数据，我删除了其中约600W行数据。我想把删除后未使用的空间腾出来。
按照以往的经验，重建这个表上的聚集索引就可以了。可是这次表上只有一个非聚集主键索引。如何释放这个堆表未使用的空间
首先来模拟出我遇到的情况：
USE master
GO
CREATE DATABASE TEST;
GO
USE TEST
GO
--每一行占一个PAGE.
CREATE TABLE tb_Test
(
id INT NOT NULL ,
val CHAR(8000)
);
ALTER TABLE dbo.tb_Test
ADD CONSTRAINT PK_tbTest_id PRIMARY KEY NONCLUSTERED (id);
GO
INSERT INTO dbo.tb_Test
( id, val )
VALUES ( 1, REPLICATE('A',10)),( 2, REPLICATE('B',10)),
( 3, REPLICATE('C',10)),( 4, REPLICATE('D',10)),
( 5, REPLICATE('E',10)),( 6, REPLICATE('F',10)),
( 7, REPLICATE('E',10)),( 8, REPLICATE('F',10)),
( 9, REPLICATE('E',10)),( 10, REPLICATE('F',10));
GO
DBCC SHOWCONTIG('tb_Test');
GO
(10 行受影响)
DBCC SHOWCONTIG 正在扫描 'tb_Test' 表...
表: 'tb_Test' (85575343)；索引 ID: 0，数据库 ID: 12
已执行 TABLE 级别的扫描。
- 扫描页数................................: 10
- 扫描区数..............................: 5
- 区切换次数..............................: 4
- 每个区的平均页数........................: 2.0
- 扫描密度 [最佳计数:实际计数].......: 40.00% [2:5]
- 区扫描碎片 ..................: 80.00%
- 每页的平均可用字节数.....................: 83.0
- 平均页密度(满).....................: 98.97%
每行占用一个页所以共有10页，接下来我删除其它5行。
DELETE FROM dbo.tb_Test
WHERE id<6;
GO
DBCC SHOWCONTIG('tb_Test');
GO
(5 行受影响)
DBCC SHOWCONTIG 正在扫描 'tb_Test' 表...
表: 'tb_Test' (85575343)；索引 ID: 0，数据库 ID: 12
已执行 TABLE 级别的扫描。
- 扫描页数................................: 10
- 扫描区数..............................: 5
- 区切换次数..............................: 4
- 每个区的平均页数........................: 2.0
- 扫描密度 [最佳计数:实际计数].......: 40.00% [2:5]
- 区扫描碎片 ..................: 80.00%
- 每页的平均可用字节数.....................: 4088.5
- 平均页密度(满).....................: 49.49%
仍然占用10页，怎么释放出这“空余”的5页？
尝试1：把非聚集主键索引改成聚集，再把它改回来。因为聚集索引的叶级页是数据本身，所以创建或者重建都会重新组织数据页。
ALTER TABLE [dbo].[tb_Test] DROP CONSTRAINT [PK_tbTest_id];
ALTER TABLE dbo.tb_Test
ADD CONSTRAINT PK_tbTest_id PRIMARY KEY CLUSTERED (id);
ALTER TABLE [dbo].[tb_Test] DROP CONSTRAINT [PK_tbTest_id];
ALTER TABLE dbo.tb_Test
ADD CONSTRAINT PK_tbTest_id PRIMARY KEY NONCLUSTERED (id);
GO
DBCC SHOWCONTIG('tb_Test');
GO
DBCC SHOWCONTIG 正在扫描 'tb_Test' 表...
表: 'tb_Test' (85575343)；索引 ID: 0，数据库 ID: 12
已执行 TABLE 级别的扫描。
- 扫描页数................................: 5
- 扫描区数..............................: 3
- 区切换次数..............................: 2
- 每个区的平均页数........................: 1.7
- 扫描密度 [最佳计数:实际计数].......: 33.33% [1:3]
- 区扫描碎片 ..................: 66.67%
- 每页的平均可用字节数.....................: 83.0
- 平均页密度(满).....................: 98.97%
这种做法有效。但是释放出来的空间不会返还给OS，只是成为数据库的Unused space.
尝试2：收缩数据文件。无论是SHRINKDATABASE或是SHRINKFILE原理都是一样的。执行下面查询时，要回滚“尝试1”的操作。
DBCC SHRINKFILE(TEST);
DBCC SHOWCONTIG('tb_Test');
GO
DBCC SHOWCONTIG 正在扫描 'tb_Test' 表...
表: 'tb_Test' (149575571)；索引 ID: 0，数据库 ID: 12
已执行 TABLE 级别的扫描。
- 扫描页数................................: 5
- 扫描区数..............................: 3
- 区切换次数..............................: 2
- 每个区的平均页数........................: 1.7
- 扫描密度 [最佳计数:实际计数].......: 33.33% [1:3]
- 区扫描碎片 ..................: 66.67%
- 每页的平均可用字节数.....................: 83.0
- 平均页密度(满).....................: 98.97%
这种做法也有效,也可以根据Shrink的设定把空间返还给OS。但是在生产环境中，特别是较大的库执行，影响特别大，容易产生大量碎片，一般不会用到。做为紧急应对的一种方法吧。
总结：
1. 郁闷。我的生产环境中，在做了“尝试1”并未起到效果，最后闲时维护使用“尝试2”才释放出来的。这点才是我做实验和写此文的初衷。
2. 不管什么表，最好还是建立一个聚集索。利于管理使用空间，不然像5行数据占据10行的空间，这种事情会经常发生。
