/*-------------------------------------------------------------------
Purpose: Generic routine to compress ALL tables and indexes that  
			meet a given set of criteria.
--------------------------------------------------------------------
Revision History:	13/07/2010  Ian Stirk Initial version.
			
Notes: Generally very good for IO bound databases. 
	 Better if DB is in SIMPLE MODE!			
		
Criteria: This routine uses the output from sp_estimate_data_compression_savings to determine what should be compressed. 
			
 -----------------------------------------------------------------*/
 
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @CompressionEstimationSQL NVARCHAR(MAX) 

-- Work table to hold SQL to estimate compression, and to run compression.
CREATE TABLE #BaseSQL (
	CompressionEstimationSQL	VARCHAR(8000)
)

-- Work table to hold results of compression estimations.
CREATE TABLE #CompressionResults (
 [object_name]								SYSNAME
, [schema_name]								SYSNAME
, [index_id]								INT
, [partition_number]							INT
, [size_with_current_compression_setting(KB)]			BIGINT
, [size_with_requested_compression_setting(KB)]			BIGINT
, [sample_size_with_current_compression_setting(KB)]		BIGINT
, [sample_size_with_requested_compression_setting(KB)]	BIGINT
, [estimated_page_savings_percent]				DECIMAL(10,2)
, [AlterTableOrIndex]						SMALLINT		-- 1 = Table, 2 = Index.
, [AlterTableOrIndexSQL]					VARCHAR(8000)	-- SQL for compression.
)


-- Build up SQL for estimating TABLE/HEAP compression.
INSERT INTO #BaseSQL (CompressionEstimationSQL)
SELECT DISTINCT  
'INSERT INTO #CompressionResults 
([object_name], [schema_name], [index_id], [partition_number]
, [size_with_current_compression_setting(KB)], [size_with_requested_compression_setting(KB)]
, [sample_size_with_current_compression_setting(KB)], [sample_size_with_requested_compression_setting(KB)])
EXEC sp_estimate_data_compression_savings '
 + '''' + ss.name  + '''' + ',' 
 + '''' + st.name  + '''' + ','
 + '''' +  CAST(s.indid AS CHAR(3))  + '''' + ',' + 'NULL' + ',' + '''' + 'PAGE' + '''' 
FROM sys.sysindexes s 
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100		-- Ignore system indexes.
AND s.rowcnt > 0		-- 0 = Stats object, ignore these.
AND s.indid IN (0,1)	-- Tables is either a heap (indid = 0), or clustered index (indid = 1).


-- Build SQL to run to estimate compression saving.
SET @CompressionEstimationSQL = ''
SELECT @CompressionEstimationSQL = @CompressionEstimationSQL + compressionEstimationSQL + CHAR(10) FROM #BaseSQL

-- Run to estimate the compression saving.
EXECUTE sp_executesql @CompressionEstimationSQL

-- Set table indicator, and build Compression SQL.
UPDATE #CompressionResults 
SET AlterTableOrIndex = 1 -- TABLE
, AlterTableOrIndexSQL = 'ALTER TABLE [' + schema_name + '].[' + object_name + '] REBUILD WITH (DATA_COMPRESSION = PAGE) ' 


-- Remove table data from work table.
TRUNCATE TABLE #BaseSQL


-- Build up SQL for estimating INDEX compression.
INSERT INTO #BaseSQL (CompressionEstimationSQL)
SELECT DISTINCT  
'INSERT INTO #CompressionResults 
([object_name], [schema_name], [index_id], [partition_number]
, [size_with_current_compression_setting(KB)], [size_with_requested_compression_setting(KB)]
, [sample_size_with_current_compression_setting(KB)], [sample_size_with_requested_compression_setting(KB)])
EXEC sp_estimate_data_compression_savings '
 + '''' + ss.name  + '''' + ',' 
 + '''' + st.name  + '''' + ','
 + CAST(s.indid AS CHAR(3)) + ',' + 'NULL' + ',' + '''' + 'PAGE' + '''' 
FROM sys.sysindexes s 
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100	-- Ignore system indexes.
AND s.rowcnt > 0	-- 0 = Stats object, ignore these.
AND s.indid >= 2	-- Ignore heaps and clustered indexes (these are in the tables SQL given previously).


-- Build SQL to run to estimate compression saving.
SET @CompressionEstimationSQL = ''
SELECT @CompressionEstimationSQL = @CompressionEstimationSQL + compressionEstimationSQL + CHAR(10) FROM #BaseSQL

-- Run to estimate the compression saving.
EXECUTE sp_executesql @CompressionEstimationSQL  
  

-- Set index indicator (used to subsequently build Compression SQL).
-- Note: additional joins needed to get the index name...
UPDATE c
SET AlterTableOrIndex = 2 -- INDEX
, AlterTableOrIndexSQL = 'ALTER INDEX [' + s.name + '] ON [' + c.schema_name + '].[' + c.object_name + '] REBUILD WITH (DATA_COMPRESSION = PAGE)' 
FROM #CompressionResults c 
INNER JOIN sys.sysindexes s ON c.index_id = s.indid 
INNER JOIN sys.tables st ON st.[object_id] = s.[id] 
AND st.name = c.object_name
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id] 
AND ss.name = c.schema_name
WHERE AlterTableOrIndex IS NULL


-- Calculate compression saving as a percentage.
UPDATE #CompressionResults 
SET estimated_page_savings_percent = 
([size_with_current_compression_setting(KB)] - [size_with_requested_compression_setting(KB)]) * 100 
/  [size_with_current_compression_setting(KB)]
WHERE [size_with_current_compression_setting(KB)] > 0


-- Build SQL to compress the tables and indexes.
-- Set up rule of what to compress e.g. exclude tables
DECLARE @CompressTablesAndIndexesSQL NVARCHAR(MAX)
SET @CompressTablesAndIndexesSQL = ''

SELECT @CompressTablesAndIndexesSQL = @CompressTablesAndIndexesSQL + AlterTableOrIndexSQL + CHAR(10) 
FROM #CompressionResults
WHERE estimated_page_savings_percent > 0


-- Debug.
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000

WHILE (@StartOffset < LEN(@CompressTablesAndIndexesSQL))
BEGIN
	PRINT SUBSTRING(@CompressTablesAndIndexesSQL, @StartOffset, @Length)
	SET @StartOffset = @StartOffset + @Length
END

PRINT SUBSTRING(@CompressTablesAndIndexesSQL, @StartOffset, @Length) 


-- Run to compress the tables and indexes...
EXECUTE sp_executesql @CompressTablesAndIndexesSQL


-- Tidy up.
DROP TABLE #BaseSQL
DROP TABLE #CompressionResults