----为什么DBCC SHRINKFILE会不起作用----
-->>TravyLee生成测试数据
if OBJECT_ID('testdb')is not null
drop database testdb
go
create database testdb;
go
use testdb
go
if OBJECT_ID('test') is not null
drop table test
go
create table test
(
a int,
b nvarchar(3900)
)
go
declare @i int
set @i=1
while @i<=1000
begin
insert into test VALUES( 1,REPLICATE(N'a',3900))
insert into test VALUES( 2,REPLICATE(N'b',3900))
insert into test VALUES( 3,REPLICATE(N'c',3900))
insert into test VALUES( 4,REPLICATE(N'd',3900))
insert into test VALUES( 5,REPLICATE(N'e',3900))
insert into test VALUES( 6,REPLICATE(N'f',3900))
insert into test VALUES( 7,REPLICATE(N'g',3900))
insert into test VALUES( 8,REPLICATE(N'h',3900))
set @i=@i+1
end
--select * from test
使用DBCC SHOWCONTIG命令来查看这个表的存储数据
dbcc showcontig('test')
--结果一
/*
DBCC SHOWCONTIG 正在扫描 'test' 表...
表: 'test' (2121058592)；索引 ID: 0，数据库 ID: 9
已执行 TABLE 级别的扫描。
- 扫描页数................................: 8000
- 扫描区数..............................: 1002
- 区切换次数..............................: 1001
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 99.80% [1000:1002]
- 区扫描碎片 ..................: 0.20%
- 每页的平均可用字节数.....................: 279.0
- 平均页密度(满).....................: 96.55%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
从上述结果中可以看到这个表的数据的存储申请了8000页
现在删除每个区里面的7个页面,只保留a=5的这些记录
delete test where a<>5
go
使用系统存储过程sp_spaceused 查看表的空间信息
sp_spaceused test
go
/*
name rows reserved data index_size unused
-------- ----------- -- -------------------------------------------
test 1000 64008 KB 32992 KB 8 KB 31008 KB
*/
使用DBCC SHOWCONTIG命令查看存储情况
DBCC SHOWCONTIG(test)
--结果二
/*
DBCC SHOWCONTIG 正在扫描 'test' 表...
表: 'test' (2121058592)；索引 ID: 0，数据库 ID: 9
已执行 TABLE 级别的扫描。
- 扫描页数................................: 4124
- 扫描区数..............................: 1002
- 区切换次数..............................: 1001
- 每个区的平均页数........................: 4.1
- 扫描密度 [最佳计数:实际计数].......: 51.50% [516:1002]
- 区扫描碎片 ..................: 0.20%
- 每页的平均可用字节数.....................: 6199.0
- 平均页密度(满).....................: 23.41%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
我们把结果一和结果二做一个比较
-------------------------------------------
结果 扫描页数 扫描区数
-------------------------------------------
一 8000 1002
-------------------------------------------
二 4124 1002
-------------------------------------------
通过上面的表的数据的对比我们容易发现还有将近一半的页面没有被释放
这时我们来对我们去对文件进行收缩:
DBCC SHRINKFILE(1,40)
/*
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------------------------------------------------------------------------------
9 1 8168 288 1160 1160
*/
通过这个结果,我们来计算一下数据文件中正在被使用的大小
--(8168*8.0)/1024=63.812500M
--正好是1000个区大小
这种情况就证明了我们收缩数据库的DBCC SHRINKFILE(1,40)
指令并没有起到应有的作用
那么我们如何解决这个问题呢?
如果这个标有聚集索引,我们可以通过重建索引把页面从排一次,
但这个表没有聚集索引
接下来我创建聚集索引:
create clustered index test_a_idx on test(a)
go
--使用DBCC SHOWCONTIG(test)命令查看表的存储情况
DBCC SHOWCONTIG(test)
/*
DBCC SHOWCONTIG 正在扫描 'test' 表...
表: 'test' (2121058592)；索引 ID: 1，数据库 ID: 9
已执行 TABLE 级别的扫描。
- 扫描页数................................: 1000
- 扫描区数..............................: 125
- 区切换次数..............................: 124
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 100.00% [125:125]
- 逻辑扫描碎片 ..................: 0.00%
- 区扫描碎片 ..................: 0.00%
- 每页的平均可用字节数.....................: 273.0
- 平均页密度(满).....................: 96.63%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
通过上述结果可以发现,创建聚集索引之后,原先存放在
堆里的数据以B树的方式从新存放。
原先的页面被释放出来了，占用的分区也被释放出来了。
这个时候再使用DBCC SHRINKFILE就有效果了
DBCC SHRINKFILE(1,40)
/*
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
----------------------------------------------------------------------------------
9 1 5120 288 1168 1168
*/
以上现象是因为数据存储页面分散在区里，造成了SHRINKFILE效果不佳。
在一个有聚集索引的表上，这个问题可以通过重建索引来解决。
如果这些去里面放的是text或者image类型的数据，
SQL Server会用单独的页面来存储这些数据。
如果存储这一类页面的区发生了这样的问题，和堆一样
做索引重建也不会影响到他们。简单的方法就是把这些可能有问题的对象
都找出来，然后重建他们。可以使用DBCC EXTENTINFO这个命令打开数据
文件里区的分配信息。然后计算每个对象理论上的区的数目和实际的数目，
如果实际数目远远大于理论数目，那这个对象就是碎片过多，
可以考虑重建对象
--还是以刚才的数据为例演示如何找出这些需要重建的对象
drop table test
go
if OBJECT_ID('test') is not null
drop table test
go
create table test
(
a int,
b nvarchar(3900)
)
go
declare @i int
set @i=1
while @i<=1000
begin
insert into test VALUES( 1,REPLICATE(N'a',3900))
insert into test VALUES( 2,REPLICATE(N'b',3900))
insert into test VALUES( 3,REPLICATE(N'c',3900))
insert into test VALUES( 4,REPLICATE(N'd',3900))
insert into test VALUES( 5,REPLICATE(N'e',3900))
insert into test VALUES( 6,REPLICATE(N'f',3900))
insert into test VALUES( 7,REPLICATE(N'g',3900))
insert into test VALUES( 8,REPLICATE(N'h',3900))
set @i=@i+1
end
go
delete from test where a<>5
go
--创建表extentinfo用来存放分区信息
if OBJECT_ID('extentinfo') is not null
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
create proc inport_extentinfo
as dbcc extentinfo('testdb')
go
insert extentinfo
exec inport_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 actual_extent_count actual_page_count possible_extent_count possible_extents/actual_extents
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2137058649 0 72057594038976512 8 998 4115 515 51.603206412
*/
select object_name(2137058649) as TName
/*
TName
----------------
test
*/
--此时我们可以找到这个存在未被释空间放的表，
--这时我们就需要对这些对象进行重建处理
