Script – Implement SQL Server Data Compression

SQL Server Data Compression feature lets you compress tables and individual indexes. The compression does incur a few extra CPU cycles every time you read or write to that table or index, but this overhead is hugely compensated by much better IO performance. In most cases, increasing a little more work for the CPU for significantly lesser IO needs will result in faster query return times.

You will obviously need to test compression to see if it works well in your environment, and also compare the differences between PAGE vs. ROW compression.

If you plan to implement compression for every table and index in the environment then below is the script you can use. It will generate a T-SQL which you can execute for rebuilding every table (including heaps) and index with PAGE compression. I have added a delay of 1 second after every command so as to reduce the risk of database running out of log space.

WITH cte_TableList (
	RowNum
	,TableName
	,IndexName
	,Index_ID
	,Data_Compression
	)
AS (
	SELECT (
			ROW_NUMBER() OVER (
				ORDER BY ('[' + s.name + '].[' + o.name + ']')
				)
			)
		,'[' + s.name + '].[' + o.name + ']' AS TableName
		,i.name AS IndexName
		,i.index_id
		,p.data_compression
	FROM sys.objects o
	INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
	INNER JOIN sys.partitions p ON o.object_id = p.object_id
	INNER JOIN sys.indexes i ON o.object_id = i.object_id
		AND p.index_id = i.index_id
	WHERE o.type = 'U'
		AND p.data_compression != 2
	)
	,cte_Cmds (
	RowNum
	,Cmd
	)
AS (
	SELECT ((Rownum - 1) * 2)
		,'ALTER TABLE ' + TableName + ' REBUILD WITH (DATA_COMPRESSION = PAGE) ;'
	FROM cte_TableList
	WHERE index_id = 0
	
	UNION ALL
	
	SELECT ((Rownum - 1) * 2) + 1
		,'ALTER INDEX ALL ON ' + TableName + ' REBUILD WITH (DATA_COMPRESSION = PAGE) ;'
	FROM cte_TableList
	WHERE index_id < 2
	)
	,cte_Final (
	RowNum
	,Cmd
	)
AS (
	SELECT (RowNum * 4)
		,'PRINT ''' + Cmd + '''; '
	FROM cte_Cmds
	
	UNION
	
	SELECT (RowNum * 4) + 1
		,Cmd
	FROM cte_Cmds
	
	UNION
	
	SELECT (RowNum * 4) + 2
		,'PRINT ''Completed at: '' + CONVERT(VARCHAR(32), SYSDATETIME(), 120);'
	FROM cte_Cmds
	
	UNION
	
	SELECT (RowNum * 4) + 3
		,'WAITFOR DELAY ''00:00:01''; '
	FROM cte_Cmds
	)

SELECT Cmd
FROM cte_Final
ORDER BY RowNum

You can obviously modify this to implement ROW data compression instead.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

SQL Shots with Dapinder Arora | SQL Server Solutions