Case study of using SQL Server compression – How it saved from a complex situation and saved dollar

Today I am going to discuss one of my personal experiences on using native SQL Server compression technologies which saved thousands of dollars for one of my client.

I was summoned in a situation when one application’s database grew to around 300 GB from only 2 weeks run in production. Hence something was needed to be done real soon before application starts failing for database.

At the beginning I collected some facts as follows:

• Database have only one flat data file of around 300GB (240GB worth of data) in a SAN drive of 1 TB.
• Data is growing in a rate of 18 GB / day.
• Database is in simple recovery and only 1 nightly backup is being taken.
• Application developers are waiting to load historical data from last 1 year on this database.

These basic information allowed me to come up with a conclusion that the database will outgrow it’s capacity with in next 7 weeks even there is no historical data load. Moreover managing the database from DR and performance point will be very challenging soon.

After this basic analysis, I pushed the development team to write an archival strategy and to load the old data on a slower archival database server. This ensured that no historical data will be loaded in the already troubled production. Also all agreed to keep only last 6 months data on the production server. This ensured at any point of time production will not have more than 6 months worth of data and allowed me to have a clear visibility of the final database size after around 6 months.(Note: As the database was getting data from hardware, the growth rate of data is not expected to change). Also I requested developers to split big tables in smaller pieces using business logic.

At next step, final projected size of the database was impossible to accommodate in the current capacity as the data drive was 1.5 TB and projected datafile size is 2.9 TB. Hence I had to design solutions purely from SQL Server DBA’s perspective. For this, following observations were helpful:

1. Verified that all the data types are uncompressed and there are huge number of rows contains null value. (typical datatypes were varchar, int etc)
2. Data are being loaded from hardwires directly. Hence type and volume of data should not change.
3. Server is using only 15%-20% CPU.

This proved that this database is a good candidate to use compression techniques. I enabled page level compression on identified large tables and got an outstanding 70% compression ratio. This means the maximum projected data size on production will be .9TB after 6 months. CPU utilization went up very modestly to around 25% in peak hours.

Next step was to make sure that this database is backed up properly. I enabled SQL Server Native Backup Compression and got a compression ration of around 60%. This means a projected full backup size of the completely grown database after 6 month will be around 600GB. Timing of full backup is expected less than 4 hours as existing database was backed up in less than 30 minutes. Transaction log backup was set at 15 minutes interval and it was not a problem to accommodate in the existing environment.

Last step was a manual review of indexing of the databases. With a fine touch, the application’s performance improved significantly. (Average response time improved to 70%)

So at end of the week, we all could walk out happily with out costing customer a dime on additional hardware or software related expenses.

To conclude, lessons learned from this incident are as below:

1. Make sure that you are involving your DBA from development stage of an application. They can give a deeper view on a database and it’s logical or physical design as they will consider all aspects of a database when it will be in real action. Also anyway they will be guardian of the database so it is no harm to make them aware on insight of the database.

2. Do not rush to buy expensive hardware or third part tool /solution for resolving a situation. While there are many great tools available in market, SQL Server comes with many powerful futures which can be good enough to satisfy almost any requirement. On the other hand, hardware upgrade should be considered only when it is absolutely necessary.