SQL SERVER 的压缩功能
请关注个人小站:http://sqlhis.com/
SQL SERVER 2008 及以上提供数据库表压缩功能
1. 压缩分类和比率
压缩分为行(ROW)压缩和页(PAGE)压缩,页压缩的压缩比率更高一些,正常来说一般数据库可以实现50%以上的压缩比率。
2.对性能影响
压缩后对磁盘的压力会减少,但是会增加CPU的压力,对性能的影响需要看机器的具体配置,在实际中情况中,经常是CPU没满但是磁盘已满负荷了。对备份恢复等极为有利,一个大数据库,全备份常常要3-4小时,压缩后,全备时间缩减到2小时。
以下代码生成全库的压缩脚本,注意只是生成脚本,将生成的脚本贴到SQL执行窗口中执行即可。
/*SQL Server 2008 以上,自动进行表压缩
直接运行生成的脚本文件即可
*/
SET NOCOUNT ON
CREATE TABLE #Temp
(
table_name NVARCHAR(1000),
index_name NVARCHAR(1000),
table_size decimal(19,2)
)
CREATE TABLE #tablespaceinfo
(
nameinfo VARCHAR(500) ,
rowsinfo BIGINT ,
reserved VARCHAR(20) ,
datainfo VARCHAR(20) ,
index_size VARCHAR(20) ,
unused VARCHAR(20)
)
INSERT #Temp(table_name,index_name)
SELECT DISTINCT '['+SCHEMA_NAME(schema_id)+'].['+a.name+']', '['+c.name+']'
FROM sys.tables a
INNER JOIN sys.partitions b
ON a.object_id=b.object_id
AND b.data_compression=0
INNER JOIN sys.indexes c
ON a.object_id=c.object_id
AND b.index_id=c.index_id
WHERE a.type='U'
AND SCHEMA_NAME(schema_id)!='cdc'
DECLARE @l_tableName NVARCHAR(max)
WHILE EXISTS(SELECT * FROM #Temp WHERE table_size IS NULL)
BEGIN
SELECT TOP 1 @l_tableName=table_name FROM #Temp
WHERE table_size IS NULL
TRUNCATE TABLE #tablespaceinfo
INSERT #tablespaceinfo
EXEC sp_spaceused @l_tableName
UPDATE #Temp
SET table_size=(SELECT CAST(REPLACE(reserved, 'KB', '') AS INT)*1.0/1024/1024 FROM #tablespaceinfo)
WHERE table_name=@l_tableName
END
--如果要查看压缩项目,
--SELECT * FROM #Temp
--ORDER BY table_size ASC
DECLARE @tablename NVARCHAR(255);
DECLARE @indexname NVARCHAR(255)
DECLARE @tablesize decimal(19,2)
DECLARE @sql NVARCHAR(MAX)
DECLARE @message NVARCHAR(MAX)
DECLARE Info_cursor CURSOR
FOR
SELECT table_name,index_name,table_size
FROM #Temp
ORDER BY table_size ASC
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename ,@indexname,@tablesize
WHILE @@FETCH_STATUS = 0
BEGIN
--ALTER INDEX [MF_NVChange_ID] ON [dbo].[MF_NVChange] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
IF @indexname IS NOT NULL
BEGIN
SET @sql ='ALTER INDEX '+@indexname+ ' ON ' +@tablename + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
SET @message='RAISERROR('''+ @indexname +' ON '+@tablename+' 已完成压缩 原空间='+CAST(@tablesize AS nvarchar(30))+'G'',9,1) WITH NOWAIT'
END
ELSE
BEGIN
SET @sql ='ALTER TABLE ' +@tablename + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
SET @message='RAISERROR('''+@tablename+' 已完成压缩 原空间='+CAST(@tablesize AS nvarchar(30))+'G'',9,1) WITH NOWAIT'
END
PRINT @sql
PRINT @message
--RAISERROR( @message,9,1)
FETCH NEXT FROM Info_cursor
INTO @tablename ,@indexname ,@tablesize
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
DROP TABLE #Temp
DROP TABLE #tablespaceinfo
生成的脚本类似如下,直接黏贴到窗口中执行即可
ALTER TABLE [dbo].[AAAAAAAAAAA] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE) RAISERROR('[dbo].[AAAAAAAAAAA] 已完成压缩 原空间=0.00G',9,1) WITH NOWAIT