Kriki’s Dynamics NAV blog

I just noticed that ara3n made a blog-post about SQL2008 crunching (http://mibuso.com/blogs/ara3n/2009/02/17/sql-2008-compression-on-nav-database/) and I decided to add some information too. He did testing on DB-scale to see what happens to the DB. I did testing on some tables with enough records in it. I wanted to know how tables would be crunched with page-crunching or record-crunching.In SQL2008, it is also possible to see how much space the data takes and how much space the indexes take. You can see that if you show the properties of the table and then select page “Storage”. Under “General”, you have “Index Space” and “Data Space”. (hmmmm. I just noticed the info also exists in SQL2005, but it is in page “General”, under “Storage”).


I tested different tables. First I did an optimize from NAV to have the indexes rebuild with a 100% fill factor. This is generally not the best for write-performance, but I wanted to know how the space varied between no compression and row- and page-compression. I took tables with enough records and some with a lot of secondary indexes (table 3 [small records, but a lot of indexes) and table 7 [big records and quite some indexes]), some tables with a few secondary indexes (tables 1,2,4,5) and a table without secondary indexes (table 6 [I wonder why it even has an index space. There is only the clustered index and that index IS the data.]).

Table 1	Rec size in NAV (B)	Total size in NAV (KB)	Index space (KB)	Data space (KB)
no compression	239	25928	10500	14266
row compression	174	18832	10469	7313
page compression	162	17616	10469	6102

Table 2	Rec size in NAV (B)	Total size in NAV (KB)	Index space (KB)	Data space (KB)
no compression	245	75200	39563	32922
row compression	215	66048	39539	24039
page compression	199	61184	39531	19234

Table 3	Rec size in NAV (B)	Total size in NAV (KB)	Index space (KB)	Data space (KB)
no compression	213	334136	215367	110125
row compression	184	288312	215258	65414
page compression	171	268856	215219	46578

Table 4	Rec size in NAV (B)	Total size in NAV (KB)	Index space (KB)	Data space (KB)
no compression	386	21200	10820	9266
row compression	364	19984	10805	8109
page compression	327	17944	10789	6070

Table 5	Rec size in NAV (B)	Total size in NAV (KB)	Index space (KB)	Data space (KB)
no compression	139	27360	3047	23469
row compression	122	24096	3023	20313
page compression	93	18272	3016	14641

Table 6	Rec size in NAV (B)	Total size in NAV (KB)	Index space (KB)	Data space (KB)
no compression	130	226520	625	220555
row compression	119	207448	539	201930
page compression	116	201176	531	195820

Table 7	Rec size in NAV (B)	Total size in NAV (KB)	Index space (KB)	Data space (KB)
no compression	2215	208048	52250	150234
row compression	984	92400	51805	37734
page compression	832	78128	51750	23867

Some trends I noticed:

-The index space gains little from compression.

-In general there is a big gain between no compression and row-compression for data space. The gain between row-compression and page-compression generally is lower, but generally, still interesting.

Conclusions on row/page compression:
With a NAV-DB we generally don’t use the CPU. So most of the time it (or they) are sitting (almost) idle. If you have 4 cores for your NAV-DB, you have CPU-power to spare. Now with compression, we can make good use of that idle CPU-time to take some load of the disks and the memory.

The question I didn’t answer is between choosing row compression or page compression. Row compression generally is less effective than page compression but probably uses less CPU, so this could be the best solution if you only have 4 cores. If you have 8 cores, you might use page compression. This is something that will need more testing to give a good answer instead of the hunch I give here.

I am wondering about 1 thing: if we use page compression, it means that a complete page must be locked to be able to change and compress it, so this could augment locking problems. Is this correct? For the moment I don’t know. Also to be tested.

Backup compression:
I did little tests on this. Just a backup of a CRONUS DB with and without compression. With compression, the backup finished faster. Don’t expect the compression of 7-zip, it is not that good. But it will definitely help to make backups faster while taking up less space. And once again, we can make good use of idle CPU-time to take of some load on the disks that receive the backup.

Final conclusion:
With both row/page compression and backup compression, Microsoft gave us a nice new tool to do SQL-performance tuning. Finally quad-core CPU’s for a SQL NAV-DB won’t be overkill anymore.

Filed under: NAVISION, PERFORMANCE, SQL