SQL 101 : SQL 2008 Compression

Backup Compression

• Comparison : Comparing backups with and without compression
• Enabling Backup Compression by Default
• Backup Compression Optimizations
  

Table & Index Compression

• Table compression basics
  
• TSQL - Commands to compress database tables
• TSQL - Commands to compress database indexes
• TSQL - Compress all tables and all indexes (Original | Updated - 2016)
• TSQL - Compress uncompressed tables and indexes
  
• TSQL - List compressed / uncompressed objects
• TSQL - Minimising Compression Rebuild Time
  
• Paul Nielson's procs to estimate and perform table compression are here.
• Real world tryout (performance implications)
  

Enabling Data Compression in Scripts generated from Management Studio 2008

1) On the Management Studio menu bar, navigate to 'Tools > Options'

The following menu then appears >


2) Expand 'SQL Server Object Explorer' , click 'Scripting'
3) Scroll down to 'Object Scripting Options' and change 'Script Data Compression Options' to TRUE


Useful link :

SQL 2008 Management Studio - Scripting Improvements

Minimising Data Compression Rebuild Time

ALTER TABLE REBUILD WITH(DATA COMPRESSION=PAGE,MAXDOP=8)

Implementing table compression use server MAXDOP (sp_configure 'max degree of parallelism') if you dont set MAXDOP in statement.

A good explanation of page compression and the MAXDOP setting is here >
http://sqlblog.com/blogs/linchi_shea/archive/2008/05/05/sql-server-2008-page-compression-using-multiple-processors.aspx

SQL 2008 : Uncompressed Objects Procedure

SQL Server Central have published my latest procedure as 'script of the day'.

The procedure is based arond SQL 2008 compression functionality and provides >

1. Lists of tables & indexes without compression
2. Lists of tables & indexes not using the desired compression (e.g. ROW when you've specified a compression type of PAGE)
3. TSQL commands to compress the database objects.

SSC : SQL 2008 : Uncompressed Objects Procedure


March 2011 Update : Putting the function here too now as SSC exclusivity period over...

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
*/

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)
DECLARE @missingindexcompressiontsql VARCHAR(MAX)
DECLARE @missingindextablelist VARCHAR(MAX)
DECLARE @missingindexindexlist VARCHAR(MAX)
DECLARE @missingcompressiontablecount INT
DECLARE @missingcompressionindexcount INT
DECLARE @changeindexcompressiontsql VARCHAR(MAX)
DECLARE @changeindextablelist VARCHAR(MAX)
DECLARE @changeindexindexlist VARCHAR(MAX)
DECLARE @changecompressiontablecount INT
DECLARE @changecompressionindexcount INT
DECLARE @CurrentRow INT
DECLARE @TotalRows INT
DECLARE @Objecttype VARCHAR(10)
DECLARE @objectname VARCHAR(100)
DECLARE @command VARCHAR(1000)
DECLARE @emailsubject VARCHAR(100)
DECLARE @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

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

Testing SQL Version

A simple ammend to my backup stored procedure today.
If it's on a sql 2008 box, use compression and increase the BUFFERCOUNT.

IF CHARINDEX('Sql Server 2008', @@VERSION) <> 0
  BEGIN
  SET @Backupcommand = @Backupcommand + ' ,COMPRESSION, BUFFERCOUNT = 250 '
  END

SQL 2008 : Compression TSQL for uncompressed objects

A CTE (common table expression) to build the commands to compress uncompressed objects -

WITH missingcompression
   AS (SELECT Schema_name(schema_id) + '.' + name                                                                                   AS tablename,
              'ALTER TABLE [' + Schema_name(schema_id) + '].[' + name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' AS command
       FROM   sys.objects
              INNER JOIN sys.partitions
                ON sys.partitions.object_id = sys.objects.object_id
       WHERE  TYPE = 'u'
              AND data_compression = 0
              AND Schema_name(sys.objects.schema_id) <> 'SYS'
       UNION
       SELECT sys_schemas.name + '.' + sys_objects.name                                                                                                   AS tablename,
              'ALTER INDEX [' + sys_indexes.name + '] ON [' + sys_schemas.name + '].[' + sys_objects.name + '] REBUILD WITH ( DATA_COMPRESSION = PAGE ) ' AS command
       FROM   sys.dm_db_partition_stats partition_stats
              INNER JOIN sys.indexes sys_indexes
                ON partition_stats.[object_id] = sys_indexes.[object_id]
                   AND partition_stats.index_id = sys_indexes.index_id
                   AND sys_indexes.type_desc <> 'HEAP'
              INNER JOIN sys.objects sys_objects
                ON sys_objects.[object_id] = partition_stats.[object_id]
              INNER JOIN sys.schemas sys_schemas
                ON sys_objects.[schema_id] = sys_schemas.[schema_id]
                   AND sys_schemas.name <> 'SYS'
              INNER JOIN sys.partitions
                ON sys.partitions.[object_id] = sys_objects.[object_id]
                   AND data_compression = 0)

SELECT command FROM missingcompression ORDER BY tablename ASC, command DESC

The previous version builds all compression commmands for tables and indexes.

SQL 2008 : Apply Compression to all Tables and Indexes

Apply Compression to all Tables and Indexes
(example uses Page Compression)

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'
UNION
SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'

SQL 2008 : Pushing Backups Further

I blogged here about using BUFFERCOUNT to speed up database backups.
Here, I attempted to use MAXTRANSFERSIZE to do more.

The command...

BACKUP DATABASE [ImportData]
TO  DISK = N'd:\ImportData.bak'
WITH NOFORMAT, INIT,  NAME = N'ImportData Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, COMPRESSION, BUFFERCOUNT = 150 , MAXTRANSFERSIZE = 1048576
GO

The default value for MAXTRANSFERSIZE is 1MB (1048576 bytes).
Possible values are multiples of 65536 bytes (64 KB) ranging up to 4MB (4194304 bytes).

The results >

MAXTRANSFERSIZE = 1048576
Processed 216 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1330824 pages in 155.348 seconds (66.927 MB/sec).

MAXTRANSFERSIZE = 2097152
Processed 458 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1331090 pages in 145.383 seconds (71.529 MB/sec).

MAXTRANSFERSIZE = 3145728
Processed 201 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1330913 pages in 145.512 seconds (71.456 MB/sec).

MAXTRANSFERSIZE = 4194304
BACKUP DATABASE successfully processed 1330780 pages in 148.087 seconds (70.206 MB/sec).

The conclusion >

A lot of messing about to only save 10 seconds.
Oh, and performing the backups in this way stressed the server out and it refused connections during the backup period.
Think I'll leave well alone...

SQL 2008 : Index Compression Cheat

Index compression is similar to table compression in terms of how you enable it, i.e. -

ALTER INDEX ALL ON schema.table REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

NB : I have done NO performance testing on the effects of compressing indexes.
This post is for information only, I have still to evaluate whether i'm compresssing indexes myself!

>>>>

Want to implement index compression on all Indexes in a sql 2008 db?
Here's dynamic sql to build those commands...

page compression...

SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'

row compression...

SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);' FROM sys.objects where TYPE = 'u'

and to remove it again...

SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);' FROM sys.objects where TYPE = 'u'

SQL 2008 : Backup Compression Optimizations

Inspired by this SQL Cat article I decided to see how quickly I could potentially perform backups.
I'm using the NUL output so backups are not written to disk.

1st test : Default Compression settings >

BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION

Processed 1222736 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 506 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223242 pages in 160.234 seconds (59.641 MB/sec).

2nd test : Up Buffercount to 50 >

BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 50

Processed 1222768 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 259 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223027 pages in 107.928 seconds (88.530 MB/sec).

3rd test : Up Buffercount to 150

BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 150

Processed 1222800 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 167 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1222967 pages in 98.587 seconds (96.913 MB/sec).

4th test : Up Buffercount to 250 >

BACKUP DATABASE MyDatabase TO DISK = 'NUL' WITH COMPRESSION, BUFFERCOUNT = 250

Processed 1222832 pages for database 'MyDatabase ', file 'MyDatabase ' on file 1.
Processed 263 pages for database 'MyDatabase ', file 'MyDatabase_log2' on file 1.
BACKUP DATABASE successfully processed 1223095 pages in 96.788 seconds (98.725 MB/sec).

Miniscule Improvement (2s) on my system between BufferCount of 150 and 250

Links :
http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

http://blogs.msdn.com/sqlcat/archive/2008/03/02/backup-more-than-1gb-per-second-using-sql2008-backup-compression.aspx

SQL 2008 Table Compression - Real World Tryout

Taking an existing import system, I ran the same test 3 times against an empty databases.
The database was presized to 25MB and shrunk afer each test so that data growth operations would not effect timings.

The test was simply running some existing code for 1 iteration.
It inserted 76015 rows across 15 separate tables.

Uncompressed

• Data Size : 14656 KB
  
• Duration : 79s (962 rows/second)

Row Compression

• Data Size : 12224 KB (83.4%, saving of 16.6%)
  
• Duration : 82s (increase of 4%, 927 rows/second)

Page Compression

• Data Size : 8512 KB (58.1%, saving of 41.9%)
  
• Duration : 87s (increase of 10%, 873 rows/second)

So, substantial disk savings can be had using page based compression at a cost of 10% in import throughput.

This is purely to demonstrate the effects of compression.
The figures are only representative of the test system.

Further efficiency gains can be made with the import

1. simplifying it
2. making import processes run in parallel.
3. placing it on a faster disk subsystem

SQL 2008 : Table Compression

A quick demo of SQL 2008's table compression.
Table compression can be performed by compressing on a ROW or a PAGE basis.

Step 1 : Decide which method to use by estimating the savings compression will bring.
Their is a handy procedure to do this for you.

-- Estimate for row based compression 

EXEC sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  'DummyTestTable' 
     , @index_id =  null 
     , @partition_number =  null 
     , @data_compression =  'ROW'; 
go

-- Estimate for page based compression

EXEC sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  'DummyTestTable' 
     , @index_id =  null 
     , @partition_number =  null 
     , @data_compression =  'PAGE'; 
go

The results


So, the original table size is 66224960 KB

Size when compressed using ROW based compression 54011056 KB (size 82%, saving 18%)
Size when compressed using PAGE based compression 36455952 KB (size 55%, saving 45%)

Step 2 : Compress the table!

ALTER TABLE dbo.DummyTestTable REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

excellent table compression article :
http://www.devx.com/SQL_Server/Article/41171/1763

SQL 2008 : Backup Compression Default

Set server option to turn on sql 2008 backup compression by default...

USE master;
GO
EXEC sp_configure 'backup compression default', '1';
GO
RECONFIGURE WITH OVERRIDE;
GO

SQL 2008 : Backup Compression

A little adventure in testing SQL 2008 Backup Compression.

Firstly, backing up a 10GB database normally -

BACKUP DATABASE [Track] TO  DISK = N'd:\backuptemp\TrackBAK'
WITH NOFORMAT, NOINIT,  NAME = N'Track-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

It took 3m 25s >

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 1276080 pages for database 'Track', file 'Track' on file 1.
100 percent processed.
Processed 1 pages for database 'Track', file 'Track_log' on file 1.
BACKUP DATABASE successfully processed 1276081 pages in 205.729 seconds (48.458 MB/sec).

Now, I'll backup the same database, but this time WITH COMPRESSION -

BACKUP DATABASE [Track] TO  DISK = N'd:\backuptemp\TrackCompressed.BAK'
WITH NOFORMAT, NOINIT,  NAME = N'Track-Compressed Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

It took 1m 34s >

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 1276080 pages for database 'Track', file 'Track' on file 1.
100 percent processed.
Processed 1 pages for database 'Track', file 'Track_log' on file 1.
BACKUP DATABASE successfully processed 1276081 pages in 94.380 seconds (105.630 MB/sec).


Overall SQL 2008 achieved compressing the original to 29% of the full backup's size.
Full Backup : 10,209,369 KB
Compressed backup : 2,977,141 KB