SQL 2008 Compression on NAV Database

SQL Server 2008 has introduced a couple of new features. The feature that interested me most is filtered indexes and compression. In this blog I will talk about my experience with compression. I deal on daily basis with many clients. So I keep a copy of their database on my local pc. As most of you know NAV databases can grow pretty quickly. And keeping all these copies becomes a challenge. I decided to test compression on a 22 gig database. I used the following query to compress all the tables and indexes in the database. This is a 4.0 database with 2009 executables.

DECLARE
@T varchar(100),@SQL varchar(255),@INDEX Varchar(255)

DECLARE T_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = ‘U’

SET NOCOUNT ON
OPEN T_cursor
FETCH NEXT FROM T_cursor INTO @T

WHILE (@@FETCH_STATUS -1) BEGIN
Set @SQL = ‘ALTER TABLE [’+@T+’] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’
Set @INDEX = ‘ALTER INDEX ALL ‘+’ ON [’+@T+ ‘] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’
Exec(@SQL)
Exec(@INDEX)
FETCH NEXT FROM T_cursor INTO @T
END

CLOSE T_cursor
DEALLOCATE T_cursor

The query ran for an hour and database size dropped to 7 gigs. That’s 68 percent drop in space. I’ve used the database for two weeks and have not noticed any difference or issues. SQL Server 2008 allows for data compression in tables, indexes, and partitions, which can save disk space and allow more data to fit into RAM, increasing query performance. Changing objects in NAV does not remove the compression. I would definitely compress test and development environment database. As for production, I would create a separate partition file group and put old data into the file group and compress the file group. SQL 2008 also allows compression for backups as well. I have not tested it yet. There is a trade- off when use compression. You will notice higher CPU usage. For my test I saw 5 to 15 percent more CPU usage, which really not a trade-off considering that on most system the SQL Server CPU utilization is in 15 to 20 percent. The reason is that majority of NAV queries are very simple and do not contain any joins.