SQL SERVER 的压缩功能

大家好，又见面了，我是你们的朋友全栈君。

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

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/166637.html原文链接：https://javaforall.cn