SQL2008数据库压缩测试

执行检测当前表大小

exec sp_spaceused 'xrm_info_busi_up_dzd'

结果

name	rows	reserved	data	index_size	unused
xrm_Info_Busi_Up_Dzd	308184     	364304 KB	362992 KB	592 KB	720 KB

网上的直接计算压缩语句(来源:http://www.cnblogs.com/saville/archive/2013/03/12/2955558.html)

exec sp_MSforeachtable  
@precommand=N' 
create table ##( 
id int identity, 
name sysname collate Chinese_PRC_90_CI_AI , 
rows int, 
reserved Nvarchar(50), 
data varchar(50), 
indexdata varchar(50), 
unused varchar(50))', 
@command1=N'insert into ##(name,rows,reserved,data,indexdata,unused) exec sp_spaceused ''?'' 
update ## set data=SUBSTRING(data, 1, LEN(data) - 2) where id=scope_identity() AND LEN(data) >=2', 
@postcommand=N'SELECT ''ALTER TABLE '' + TABLENAME + '' REBUILD WITH ( DATA_COMPRESSION = PAGE )'' FROM sys.tables A 
JOIN 
(SELECT C.name + ''.'' + A.name AS TABLENAME, object_id FROM ## A 
JOIN sys.objects B 
ON A.name = B.name  
JOIN sys.schemas C 
ON B.schema_id = C.schema_id 
WHERE CAST(data AS int) > 100000 AND object_id IN (SELECT object_id FROM sys.tables)) B 
ON A.object_id = B.object_id AND type = ''U'';drop table ##' 

执行结果:

ALTER TABLE dbo.xrm_Info_Busi_Up_Dzd REBUILD WITH ( DATA_COMPRESSION = PAGE )

do it.

命令已成功完成。

再执行

exec sp_spaceused 'xrm_info_busi_up_dzd'

新结果

name	rows	reserved	data	index_size	unused
xrm_Info_Busi_Up_Dzd	308184     	305040 KB	303808 KB	512 KB	720 KB

　节省了 16.3%空间。

 

取消压缩

ALTER TABLE [dbo].[xrm_Info_Busi_Up_Dzd] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = NONE
)

　

参考：http://technet.microsoft.com/en-us/library/cc280449(v=sql.100).aspx

 

另查看所有表大小相关

select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages/1024)+'Mb' used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,
rows,* from sysindexes 
where indid=1 
order by reserved desc

　以上sql2008r2有效。来源http://www.itlearner.com/article/2720