SQL Server 2008 and 2012 table compression the AutoScripting way

This will search all databases on a server for 1. tables of a specific name 2. that are not already compressed and 3. with row counts over 10 million.  The return will be a script that you can look over and decide to run all or pieces of it.

I have been dealing with compressed tables since 2008 and it has been my experience that only very large tables benefit from table compression.  I have proved it in my own performance tests time and time again.  Another item to consider is whether to compress by row or by page, this sample is specific to row compression.

WARNING:  Be sure to set the MAXDOP to half of your available CPU.

Exec sp_MSforeachdb ‘
use ?
select ”ALTER TABLE ?.dbo.[”+ name +”] REBUILD WITH (DATA_COMPRESSION = ROW, MAXDOP = 8);”
FROM ?.dbo.sysobjects oo
inner join ?.sys.partitions SP ON oo.id = sp.object_id
WHERE oo.type = ”U”
and sp.data_compression = 0
and [rows] > 10000000
and sp.index_id in (0,1) — 0=heap , 1=clustered , 2 or greater=nonclustered index
and oo.name in (”tablename1”,”tablename2”,”tablename3′)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: