Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

SQL Server 2008 Compression

Hi!

Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity.

However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around.
So I decided to give compression a go, since we use Enterprise Edition anyway.

This is the code I use to compress all tables with PAGE compression.


DECLARE @SQL VARCHAR(MAX)
 
DECLARE curTables CURSOR FOR
            SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                    + '.' + QUOTENAME(OBJECT_NAME(object_id))
                    + ' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
            FROM    sys.tables
 
OPEN    curTables
 
FETCH   NEXT
FROM    curTables
INTO    @SQL
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @SQL IS NOT NULL
            RAISERROR(@SQL, 10, 1) WITH NOWAIT
 
        FETCH   NEXT
        FROM    curTables
        INTO    @SQL
    END
 
CLOSE       curTables
DEALLOCATE  curTables


Copy and paste the result to a new code window and execute the statements. One thing I noticed when doing this, is that the database grows with the same size as the table. If the database cannot grow this size, the operation fails.
For me, I first ended up with orphaned connection. Not good.

And this is the code I use to create the index compression statements


DECLARE @SQL VARCHAR(MAX)
 
DECLARE curIndexes CURSOR FOR
            SELECT      'ALTER INDEX ' + QUOTENAME(name)
                        + ' ON '
                        + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                        + '.'
                        + QUOTENAME(OBJECT_NAME(object_id))
                        + ' REBUILD PARTITION = ALL WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE)'
            FROM        sys.indexes
            WHERE       OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
                        AND OBJECTPROPERTY(object_id, 'IsTable') = 1
            ORDER BY    CASE type_desc
                            WHEN 'CLUSTERED' THEN 1
                            ELSE 2
                        END
 
OPEN    curIndexes
 
FETCH   NEXT
FROM    curIndexes
INTO    @SQL
 
WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @SQL IS NOT NULL
            RAISERROR(@SQL, 10, 1) WITH NOWAIT
 
        FETCH   NEXT
        FROM    curIndexes
        INTO    @SQL
    END
 
CLOSE       curIndexes
DEALLOCATE  curIndexes


When this was done, I noticed that the 90GB database now only was 17GB. And most important, complete database now could reside in memory!

After this I took care of the administrative tasks, backups. Here I copied the code from Management Studio because I didn't want to give too much time for this. The code looks like (notice the compression option).


BACKUP DATABASE [Yoda]
TO              DISK = N'D:\Fileshare\Backup\Yoda.bak'
WITH            NOFORMAT,
                INIT,
                NAME = N'Yoda - Full Database Backup',
                SKIP,
                NOREWIND,
                NOUNLOAD,
                COMPRESSION,
                STATS = 10,
                CHECKSUM
GO
 
DECLARE @BackupSetID INT
 
SELECT  @BackupSetID = Position
FROM    msdb..backupset
WHERE   database_name = N'Yoda'
        AND backup_set_id =(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name = N'Yoda')
 
IF @BackupSetID IS NULL
    RAISERROR(N'Verify failed. Backup information for database ''Yoda'' not found.', 16, 1)
 
RESTORE VERIFYONLY
FROM    DISK = N'D:\Fileshare\Backup\Yoda.bak'
WITH    FILE = @BackupSetID,
        NOUNLOAD,
        NOREWIND
GO


After running the backup, the file size was even more reduced due to the zip-like compression algorithm used in SQL Server 2008. The file size? Only 9 GB.

//Peso

Print | posted on Thursday, June 17, 2010 2:16 PM | Filed Under [ Optimization SQL Server 2008 Administration ]

Feedback

Gravatar

# re: SQL Server 2008 Compression

What is the perfomance impact of compressing the database?
6/17/2010 3:03 PM | Marco
Gravatar

# re: SQL Server 2008 Compression

During the compression the table or index is locked, so I don't recommend doing this during office hours.

Afterwards, all outtakes are much faster since all pages are in memory.
The negliable CPU overhead is not noticable.
6/17/2010 3:12 PM | Peso
Gravatar

# re: SQL Server 2008 Compression

Note that a very simple way to implement compression (in this case page level) on all tables in a specified database can be achieved with the following:


sp_msForEachTable 'ALTER TABLE ?
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)'

This uses the Microsoft Stored Proc for looping through all tables. The question mark indicates the placeholder for each table name.


Enjoy!
2/24/2011 2:29 PM | Travis
Gravatar

# re: SQL Server 2008 Compression

Thank you.

Your code saved us so much time. We decided to migrate from SQL Server 2005 to take advantage of the compression based storage savings.
6/19/2011 7:48 PM | Jon
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET