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
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]
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
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
Legacy Comments
|
Marco
2010-06-17 |
re: SQL Server 2008 Compression What is the perfomance impact of compressing the database? |
|
Peso
2010-06-17 |
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. |
|
Travis
2011-02-24 |
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! |
|
Jon
2011-06-19 |
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. |