Whole Database - Data Compression Procs

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

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

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

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!

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?

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.

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!

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.

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

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

http://msdn.microsoft.com/en-us/library/dd894051.aspx

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.