Microsoft Dynamics NAV and SQL Server Database Compression

Microsoft SQL Server 2008 introduces two new compression features (available in Enterprise Edition) that are of interest to users with large databases. Data compression applies to individual tables and indexes within a database and comes in two options: ROW and PAGE. Backup compression is a new option when backing up a database to external storage. Both compression techniques can be used to reduce the amount of online storage required for databases. Data compression can be configured for an entire heap (table with no clustered index), clustered index, non-clustered index, or indexed view. For partitioned tables and indexes, the compression option can be configured separately (and differently) for each partition. Compression can also reduce disk utilization and sql memory utilization as dead is stores on disk in a compressed state and also reads in the SQL cache in a compressed state. Compression can add 10 – 30% percent increased CPU utilization depending on what tables and indexes are compressed and what level of compression is used.

For Dynamics NAV we recommend only compressing tables and indexes that have a read to write ration of 80%/20% (This is a conservative threshold) or higher as compressing tables with a higher write ratio can actually decrease performance. We also recommend using ROW compression if the space saving between ROW and PAGE level compression is less that 10%; if the difference is over 10% then we recommend PAGE compression. This is because if the space savings from PAGE compression is close to or similar to ROW compression then it is not recommended to incur the additional overhead associated with PAGE compression. An example of NAV tables that would benefit greatly from compression are the large “Entry” tables such as G/L Entry, Value Entry, and Item Ledger Entry. An example for NAV tables that would not benefit from compression and where performance may actually decrease due to compression are “Line” tables where the data is temporary in nature such as Sales Line, Purchase Line, and Warehouse Activity Line. SQL Server compression is completely transparent to the Dynamics NAV application.

Compression is done on Table or Index basis. On tables with clustered indexes the clustered index is the table so compressing the clustered index is equal to compressing the table. Running the ALTER TABLE.. WITH COMPRESSION is only necessary on HEAPS for all tables with Clustered Indexes you can use the ALTER INDEX.. WITH COMPRESSION.

How to determine the read/write ratio of an index? Luckily SQL keeps track of this for us and all we need to do is extract this data. SQL Server stores this information in the sys.dm_db_index_operational_stats DMV. Remember DMV’s are “recycled” each time the SQL Server service is restarted so if SQL has only been up and running for a day or a week this information will be of minimal use. Ideally you would want the server to be up any running for several weeks and through a month end close to get a true idea of actual data access patterns of your database.

The following query will tell you how long the SQL Server instance has been up and running:

select ‘Sql Server Service has been running for about ‘

       + cast((datediff(hh, create_date, getdate()))/24 as varchar(3)) + ‘ days and ‘

       + cast((datediff(hh, create_date, getdate())) % 24 as varchar(2)) + ‘ hours’

       from sys.databases where name = ‘tempdb’

The following query will give the approximate read write balance of all the used indexes in the database.







                        i.leaf_update_count * 100.0 /

                              (i.range_scan_count + i.leaf_insert_count

                                  + i.leaf_delete_count + i.leaf_update_count

                                  + i.leaf_page_merge_count + i.singleton_lookup_count

                              ) as Writes,

                        i.range_scan_count * 100.0 /

                              (i.range_scan_count + i.leaf_insert_count

                                  + i.leaf_delete_count + i.leaf_update_count

                                  + i.leaf_page_merge_count + i.singleton_lookup_count

                              ) as Reads

              FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i

              JOIN sys.sysobjects o ON = i.object_id

              JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id =


              JOIN sys.sysindexes s ON = x.object_id and s.indid = x.index_id

              WHERE (i.range_scan_count + i.leaf_insert_count

                        + i.leaf_delete_count + leaf_update_count

                        + i.leaf_page_merge_count + i.singleton_lookup_count) <> 0

              AND objectproperty(i.object_id,‘IsUserTable’) = 1


You can also run a simulation with the sp_estimate_data_compression_savings stored procedure in SQL with will run a sample of the data through the chose compression level and give you the estimate of the space saved by enabling compression.

I strongly recommend reading the following MSDN article before engaging in database compression activities.

Note: You can use the following query to check to see if there are currently any compressed indexes in the database.

SELECT Table_Name, p.index_id as Index_ID, as Index_Name,


      WHEN p.data_compression = 1 THEN ‘ROW Compression’

      WHEN p.data_compression = 2 THEN ‘PAGE Compression’

      ELSE ‘ERROR’

      END Compression_Type

FROM sys.partitions p

JOIN sys.objects o ON p.object_id = o.object_id

JOIN sys.sysindexes i ON o.object_id = AND p.index_id = i.indid

AND p.data_compression in (1,2)

ORDER BY, p.data_compression, p.index_id


Michael De Voe

Senior Premier Field Engineer

Microsoft Dynamics

Microsoft Certified Master – SQL Server 2008