SQL Server 2008 Compression
- by Peter Larsson
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
