THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

Whole Database - Data Compression Procs

Data compression is one of my favorite new SQL Server 2008 features. For high-transaction databases, it can dramatically cut the I/O and make SAN shadow copy backups smaller.

As cool as data compression is,  enabling compression means running the compression wizard or alter table/index command for each individual object or index. The good news is that Microsoft has exposed the system stored procedure that estimates the potential gain using compression.

I’ve just posted on a script that includes two stored procedures.

db_compression_estimate estimates the row and page compression gain for every object and index in the database.

db_compression (@minCompression) will check the current compression setting and compare it to potential row and page compression gains. If the compression should be adjusted it performs the alter command. The default @minCompression setting is 25%.

If the estimated gain is equal to or greater than @minCompression parameter then it enables row or page compression whichever is greater gain. If row and page have same gain then it enables row compression.

If estimated gain is less than @mincompression parameter then compression is set to none.

disclaimer: This is a first version of these stored procedures. This version has no error trapping. Also there’s a known issue when using Insert..exec with sp_estimate_data_compression_savings that generates a transaction error. The script was developed for CPT6. please email any issues to 


Published Thursday, March 13, 2008 2:36 PM by Paul Nielsen

Attachment(s): compression.jpg



Josh Jones said:

Wow Paul, thanks. Can't wait to give these a spin.

March 14, 2008 12:04 AM

Aaron Bertrand said:

Sorry I have been quiet recently; been working very hard for the past two weeks on a migration of an

December 12, 2009 9:58 PM

Deepak said:

Hi Paul just ask,can i use Delete command in Insert Query.

June 6, 2010 10:25 AM

David Wendelken said:

I'm a bit confused about the estimation script.

The sp_estimate_data_compression_savings procedure documentation (and your scripts's comments) refer to finding out the compression estimates for tables/objects and indexes.  Yet, this stored procedure (and your cursors) only seem to report on the indexes.

I haven't figured out how much data compression savings I would get if I compressed the table but not the indexes.

How can I do that?

Thanks in advance!

January 12, 2011 5:31 PM

Paul Nielsen said:

When tuning SQL Server, you need to adopt the POV that there is no such thing as a table in SQL Server, only indexes. What you think is a table is really a clustered index.

January 12, 2011 6:43 PM

David Wendelken said:

I was using this against a table dbo.PrincipalObjectAccess in the CRM product.  The table has 5 indexes, one of which is a time-stamp column.  I tossed the timestamp index because the procedure barfs on indexes with time-stamp columns.  That leaves 4 indexes.

The procedure returns 1 row per index.  It does not return an index=0 row for the table.

I expected it to and it didn't, so I asked about it.

Based on what you told me, I created a dummy table in another application with no indexes.

The procedure returns an index=0 record for the table.  I added an index to the table.  The procedure returned two rows, one for the index and one for the table.  This I what I originally expected.

I am very confused by what this procedure is doing.  Why doesn't it return a record for the table in CRM?  The properties for the table say it has no compression on it, so that shouldn't be it.

What is going on?

January 13, 2011 7:44 AM

David Wendelken said:

Further experimenting shows that if the table has a clustered index, the index=0 record disappears.  That's not intuitive, but at least I now understand what it's doing.

Thanks for the help.

January 13, 2011 7:59 AM

Tallarico said:

Great script!

This may have been addressed in a later version, but I noticed that tables that were named with protected words (i.e. Rule, Group) and special characters were not handled.

Ultimately I think I need to establish better policies surrounding nomenclature for our database objects.

Thanks for taking the time to develop this and make it available to the masses!

April 13, 2011 3:53 PM

Chris Wood said:

I implemented this solution and tried it out on a large table 41 million rows...  i copied table A into table B and created the same indexes with compression. The execution plan agrees with the compression estimation that it will only cost about half the I/O. The only problem is the table A returned my query in 18 secs and table returned an indentical query in 25 secs. (I had cleaned the buffers before running the tests) CAN anyone help I was really hoping to implement this DB Wide.

April 28, 2011 12:39 PM

DBA200 said:

Is the script removed ? I can't find it.

July 5, 2011 6:08 AM

Paul Nielsen said:

DAB2000, the scripts are right where they've always been. Click on the link, click on "Scripts" and it's the second script in the list.

July 5, 2011 12:26 PM

Chris Luc said:

nice procs, however it would be even nicer if we could include calculating the U and S percentages as described here:

and either generate the recommentation with consideration of those values or simply present them in the result set so the decision maker is better informed of "normal" activity on given object which can greately impact CPU utilization once compression is implemented.

July 27, 2012 2:26 PM

Oscar Campanini said:

Running the store procedure got an error:

Msg 1701, Level 16, State 1, Line 1

Creating or altering table '#sample_tableDBA05385A6FF40F888204D05C7D56D2B_______________________________________________________________________00000000024A' failed because the minimum row size would be 8389, including 1622 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Msg 2714, Level 16, State 3, Procedure db_compression, Line 276

There is already an object named 'db_compression' in the database.

March 23, 2015 4:01 PM

Cees Meuleman said:

Based on the Microsoft Technote below I have written a stored procedure which will evaluate the use of the tables and suggest a decision to user ow or page compression. In default it will generate the commands based on the suggestion, but alternatively you can also execute the commands directly. Furthermore you can adjust the parameters on which the decision is based.

This is the first version of this script, so please let me know if u run into issues.

Microsoft technote:

Download stored procedure:

October 19, 2015 5:01 AM
New Comments to this post are disabled

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog



news item test
Privacy Statement