List Uncompressed Tables and Indexes
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Not Supported
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Richard Doering
Date: August 2009
Description
This is a more comprehensive Stored Procedure that builds on the code in the tip to compress all tables and indexes.
This SP has very detailed comments, but can be summarised as follows:
Parameters
Database to check
Desired Compression Type (PAGE | ROW)
Optional Email Recipients
Optional Email Profile (mandatory if email profile supplied)
Results Sets
List of tables and indexes that are not compressed with the chosen compression type.
List of tables and indexes that are compressed with the chosen compression type.
TSQL Code to compress the uncompressed tables and indexes.
TSQL Code to alter the compression type on the tables and indexes that are incorrectly compressed.
Richard has also provided a supporting sample SP that wraps the above SP in a loop through all user databases and emails the results.
Code
Check compression type for all tables and indexes in a Database:
DROP PROCEDURE dbo.UncompressedObjects
GO
CREATE PROCEDURE dbo.UncompressedObjects
(
@database VARCHAR(50) = '' ,
@emailrecipients VARCHAR(1000) = '' ,
@emailprofile VARCHAR(50) = '' ,
@compressiontype VARCHAR(4) = 'PAGE'
)
AS
BEGIN
/*
Procedure : dbo.UncompressedObjects
Version : 1.0 (August 2009)
Author : Richard Doering
Web : http://sqlsolace.blogspot.com
Usage :
UncompressedObjects has 2 required parameters, @database and @compressiontype.
@database is the name of the database you want to compress.
@compressiontype is either PAGE or ROW compression.
There are 2 optional parameters @emailrecipients and @emailprofile.
If these are both supplied, the output is sent to the email addresses supplied (providing you have an email profile configured).
Output :
The procedure provides >
Lists of tables & indexes without compression
Lists of tables & indexes not using the desired compression (e.g. ROW when you've specified a compression type of PAGE)
TSQL commands to compress the database objects.
Examples :
EXEC dbo.UncompressedObjects
@database = 'master'
, @compressiontype = 'ROW'
EXEC dbo.UncompressedObjects
@database = 'Adventureworks'
, @compressiontype = 'PAGE'
, @emailrecipients = 'emailaddress@domain.com'
, @emailprofile = 'Profile Name'
*/
SET NOCOUNT ON
-- Check supplied parameters
IF @database = ''
BEGIN
PRINT 'Database not specified'
RETURN
END
IF @database NOT IN (SELECT name FROM sys.databases)
BEGIN
PRINT 'Database ' + @database + ' not found on server ' + @@SERVERNAME
RETURN
END
IF @emailrecipients = '' AND @emailprofile <> ''
BEGIN
PRINT 'Email profile given but recipients not specified'
RETURN
END
IF @emailrecipients <> '' AND @emailprofile = ''
BEGIN
PRINT 'Email recipients given but profile not specified'
RETURN
END
SET @compressiontype = UPPER(LTRIM(RTRIM(@compressiontype)))
IF @compressiontype NOT IN ('PAGE', 'ROW')
BEGIN
PRINT 'CompressionType must be PAGE or ROW'
RETURN
END
-- Declare variables
DECLARE
@indexreport VARCHAR(MAX),
@missingindexcompressiontsql VARCHAR(MAX),
@missingindextablelist VARCHAR(MAX),
@missingindexindexlist VARCHAR(MAX),
@missingcompressiontablecount INT,
@missingcompressionindexcount INT,
@changeindexcompressiontsql VARCHAR(MAX),
@changeindextablelist VARCHAR(MAX),
@changeindexindexlist VARCHAR(MAX),
@changecompressiontablecount INT,
@changecompressionindexcount INT,
@CurrentRow INT,
@TotalRows INT,
@Objecttype VARCHAR(10),
@objectname VARCHAR(100),
@command VARCHAR(1000),
@emailsubject VARCHAR(100),
@dynamicsql VARCHAR(MAX)
-- Create temporary tables.
-- These are used because they're scope is greater than a tablevariable i.e. we can pull results back from dynamic sql.
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '##MissingCompression%')
DROP TABLE ##MissingCompression
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '##ChangeCompression%')
DROP TABLE ##ChangeCompression
CREATE TABLE ##MissingCompression
(
uniquerowid INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL,
objecttype VARCHAR(10),
objectname VARCHAR(100),
command VARCHAR(500)
);
CREATE TABLE ##ChangeCompression
(
uniquerowid INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL,
objecttype VARCHAR(10),
objectname VARCHAR(100),
command VARCHAR(500)
);
-- Work out what indexes are missing compression and build the commands for them
SET @dynamicsql =
'WITH missingcompression AS (SELECT ''Table'' AS objecttype,
s.name + ''.'' + o.name AS objectname,
''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION == ' + @compressiontype + ');'' AS command
FROM ' + @database + '.sys.objects o
INNER JOIN ' + @database + '.sys.partitions p
ON p.object_id = o.object_id
INNER JOIN ' + @database + '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE TYPE = ''u''
AND data_compression = 0
AND Schema_name(o.schema_id) <> ''SYS''
UNION
SELECT ''Index'' AS objecttype,
i.name AS objectname,
''ALTER INDEX ['' + i.name + ''] ON ['' + s.name + ''].['' + o.name + ''] REBUILD WITH ( DATA_COMPRESSION = ' + @compressiontype + ');'' AS command
FROM ' + @database + '.sys.dm_db_partition_stats ps
INNER JOIN ' + @database + '.sys.indexes i
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
AND i.type_desc <> ''HEAP''
INNER JOIN ' + @database + '.sys.objects o
ON o.[object_id] = ps.[object_id]
INNER JOIN ' + @database + '.sys.schemas s
ON o.[schema_id] = s.[schema_id]
AND s.name <> ''SYS''
INNER JOIN ' + @database + '.sys.partitions p
ON p.[object_id] = o.[object_id]
AND data_compression = 0)
-- populate temporary table ''##MissingCompression''
INSERT INTO ##MissingCompression (objecttype, objectname, command)
SELECT objecttype, objectname, command FROM missingcompression ORDER BY objectname ASC, command DESC '
EXEC (@dynamicsql)
SET @dynamicsql =
'WITH changecompression
AS (SELECT ''Table'' AS objecttype,
s.name + ''.'' + o.name AS objectname,
''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ' + @compressiontype + ');'' AS command
FROM ' + @database + '.sys.objects o
INNER JOIN ' + @database + '.sys.partitions p
ON p.object_id = o.object_id
INNER JOIN ' + @database + '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE TYPE = ''u''
AND data_compression <> 0
AND data_compression_desc <> ''' + @compressiontype + '''
AND Schema_name(o.schema_id) <> ''SYS''
UNION
SELECT ''Index'' AS objecttype,
i.name AS objectname,
''ALTER INDEX ['' + i.name + ''] ON ['' + s.name + ''].['' + o.name + ''] REBUILD WITH ( DATA_COMPRESSION = ' + @compressiontype + ');'' AS command
FROM ' + @database + '.sys.dm_db_partition_stats ps
INNER JOIN ' + @database + '.sys.indexes i
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
AND i.type_desc <> ''HEAP''
INNER JOIN ' + @database + '.sys.objects o
ON o.[object_id] = ps.[object_id]
INNER JOIN ' + @database + '.sys.schemas s
ON o.[schema_id] = s.[schema_id]
AND s.name <> ''SYS''
INNER JOIN ' + @database + '.sys.partitions p
ON p.[object_id] = o.[object_id]
AND data_compression <> 0
AND data_compression_desc <> ''' + @compressiontype + ''' )
-- populate temporary table ''##ChangeCompression''
INSERT INTO ##ChangeCompression (objecttype, objectname, command)
SELECT objecttype, objectname, command FROM changecompression ORDER BY objectname ASC, command DESC '
EXEC (@dynamicsql)
-- We now have populated our temporary tables (##MissingCompression & ##ChangeCompression)
-- First, loop objects with no compression.
-- For each object >
-- 1) increment the counter,
-- 2) add the object name to the list for display
-- 3) generate the tsql for compression commands
-- set initial variables
SET @missingindexcompressiontsql = ''
SET @missingindextablelist = ''
SET @missingindexindexlist = ''
SET @missingcompressiontablecount = 0
SET @missingcompressionindexcount = 0
SELECT @TotalRows = Count(* ) FROM ##MissingCompression
SELECT @CurrentRow = 1
WHILE @CurrentRow <= @TotalRows
BEGIN
SELECT @Objecttype = objecttype,
@objectname = objectname,
@command = command
FROM ##MissingCompression
WHERE uniquerowid = @CurrentRow
SET @missingindexcompressiontsql = @missingindexcompressiontsql + @command + Char(10) + Char(10)
IF @Objecttype = 'table'
BEGIN
SET @missingindextablelist = @missingindextablelist + @objectname + Char(10)
SET @missingcompressiontablecount = @missingcompressiontablecount + 1
END
IF @Objecttype = 'index'
BEGIN
SET @missingindexindexlist = @missingindexindexlist + @objectname + Char(10)
SET @missingcompressionindexcount = @missingcompressionindexcount + 1
END
SELECT @CurrentRow = @CurrentRow + 1
END
-- Now deal with Objects that need to change compression type
-- For each object >
-- 1) increment the counter,
-- 2) add the object name to the list for display
-- 3) generate the tsql for compression commands
-- set initial variables
SET @changeindexcompressiontsql = ''
SET @changeindextablelist = ''
SET @changeindexindexlist = ''
SET @indexreport = ''
SET @changecompressiontablecount = 0
SET @changecompressionindexcount = 0
SELECT @TotalRows = Count(* ) FROM ##ChangeCompression
SELECT @CurrentRow = 1
WHILE @CurrentRow <= @TotalRows
BEGIN
SELECT @Objecttype = objecttype,
@objectname = objectname,
@command = command
FROM ##ChangeCompression
WHERE uniquerowid = @CurrentRow
SET @changeindexcompressiontsql = @changeindexcompressiontsql + @command + Char(10) + Char(10)
IF @Objecttype = 'table'
BEGIN
SET @changeindextablelist = @changeindextablelist + @objectname + Char(10)
SET @changecompressiontablecount = @changecompressiontablecount + 1
END
IF @Objecttype = 'index'
BEGIN
SET @changeindexindexlist = @changeindexindexlist + @objectname + Char(10)
SET @changecompressionindexcount = @changecompressionindexcount + 1
END
SELECT @CurrentRow = @CurrentRow + 1
END
-- Build the text output for the report >
-- First for objects missing compression >
IF (@missingcompressionindexcount + @missingcompressiontablecount) > 0
BEGIN
IF (@missingcompressiontablecount) > 0
BEGIN
SET @indexreport = @indexreport + 'Tables not currently utilising ' + @compressiontype + ' compression >' + Char(10) + '--------------------------------------------' + Char(10) + @missingindextablelist + Char(13) + Char(13)
END
IF (@missingcompressionindexcount) > 0
BEGIN
SET @indexreport = @indexreport + 'Indexes not currently utilising ' + @compressiontype + ' compression >' + Char(10) + '---------------------------------------------' + Char(10) + @missingindexindexlist + Char(13) + Char(13)
END
END
-- Now for objects using the incorrect compression type >
IF (@changecompressionindexcount + @changecompressiontablecount) > 0
BEGIN
IF (@changecompressiontablecount) > 0
BEGIN
SET @indexreport = @indexreport + 'Tables with incorrect compression type >' + Char(10) + '--------------------------------------------' + Char(13) + Char(10) + @changeindextablelist + Char(13) + Char(10)
END
IF (@changecompressionindexcount) > 0
BEGIN
SET @indexreport = @indexreport + 'Indexes with incorrect compression type >' + Char(10) + '---------------------------------------------' + Char(13) + Char(10) + @changeindexindexlist + Char(13) + Char(10)
END
END
IF (@missingcompressionindexcount + @missingcompressiontablecount) > 0
BEGIN
SET @indexreport = @indexreport + char(10) + '/* TSQL to implement ' + @compressiontype + ' compression */' + Char(10) + '-----------------------------------' + Char(10) + 'USE [' + @database + ']' + Char(10) + 'GO' + Char(10) + @missingindexcompressiontsql + Char(13) + Char(10)
END
IF (@changecompressionindexcount + @changecompressiontablecount) > 0
BEGIN
SET @indexreport = @indexreport + char(10) + '/* TSQL to change to ' + @compressiontype + ' compression type */' + Char(10) + '-------------------------------------' + Char(10) + 'USE [' + @database + ']' + Char(10) + 'GO' + Char(10) + @changeindexcompressiontsql + Char(13) + Char(10)
END
-- Tidy up. Remove the temporary tables.
DROP TABLE ##MissingCompression
DROP TABLE ##ChangeCompression
-- Display report and email results if there are any required actions >
IF ( (@changecompressionindexcount + @changecompressiontablecount + @missingcompressionindexcount + @missingcompressiontablecount) > 0)
BEGIN
-- Compression changes recommended, display them
PRINT @indexreport
-- If email paramters supplied, email the results too.
IF @emailrecipients <> '' AND @emailprofile <> ''
BEGIN
SET @emailsubject = @@SERVERNAME + ' : Uncompressed object report : ' + @database + ' (' + @compressiontype + ' compression)'
-- send email
EXEC msdb.dbo.sp_send_dbmail
@recipients = @emailrecipients,
@subject = @emailsubject,
@body = @indexreport,
@profile_name = @emailprofile
END
END
ELSE
BEGIN
PRINT 'No database objects to compress'
END
END
GO
Check compression type for all tables and indexes on a SQL Server Instance:
DROP PROCEDURE dbo.UncompressedServerObjects
GO
CREATE PROCEDURE dbo.UncompressedServerObjects AS
BEGIN
SET NOCOUNT ON
DECLARE @CurrentRow INT
DECLARE @TotalRows INT
DECLARE @DatabaseName NVARCHAR(255)
DECLARE @Databases TABLE(
UNIQUEROWID INT IDENTITY ( 1,1 ) PRIMARY KEY NOT NULL,
DATABASENAME NVARCHAR(255)
)
SELECT @CurrentRow = 1
INSERT INTO @Databases (DATABASENAME)
SELECT NAME
FROM SYS.DATABASES
WHERE DATABASE_ID > 4
SELECT @TotalRows = COUNT(*) FROM @Databases
WHILE @CurrentRow <= @TotalRows
BEGIN
SELECT @DatabaseName = DATABASENAME
FROM @Databases
WHERE UNIQUEROWID = @CurrentRow
EXEC dbo.UncompressedObjects
@database = @DatabaseName
, @compressiontype = 'PAGE'
, @emailrecipients = 'emailaddress@domain.com'
, @emailprofile = 'Profile Name'
SELECT @CurrentRow = @CurrentRow + 1
END
END
GO