First published on MSDN on Mar 16, 2010
Just a short note on a question which came up in a lot of customer meetings around SQL Server Database Compression. Most people I talked to were wondering how pages of a Row or Page Dictionary compressed table are represented in the SQL Server Buffer Pool. The answer is that the representation of the pages is the same as in the data files. Means the rows still are in Row or Page Dictionary form when the page has been read and is stored in the SQL Server Buffer Pool. Only when the data is moved higher in the layers of SQL Server towards the application, the data is going to be assembled into a non-compressed form. As a result the usage of database compression with SQL Server 2008 and later increases memory efficiency as well.
In terms of customers who moved to SQL Server 2008 and applied database compression, we saw quite a few larger implementations around the world. One of our customers in Asia applied Page Dictionary compression to their SAP BW and decreased the volume from 2.3TB to 600GB. The customer did this step together with a hardware refresh on server and storage level. Hence the more on CPU consumption by Page Dictionary compression was easily compensated with the new server hardware. More important the investments for storage decreased dramatically. Another great example of the potential of savings possible with database compression.
Cheers Juergen
