Introduction :
First of all, I've already evaluated and am using SQL 2008 Table & Index Compression.
This procedure is to help you to do the same. Obviously if you're reading this you're most likely a DBA and will dutifully evaluate whether compression is appropriate for your environment. Personally I'm very impressed and feel the storage savings far outweigh the additional processor overhead.
Usage :
UncompressedObjects has 2 required parameters, @database and @compressiontype.
@database is the name of the database you want to compress.
@compressiontype is either PAGE or ROW compression.
There are 2 optional parameters @emailrecipients and @emailprofile.
If these are both supplied, the output is sent to the email addresses supplied (providing you have an email profile configured).
Output :
The procedure provides >
- Lists of tables & indexes without compression
- Lists of tables & indexes not using the desired compression (e.g. ROW when you've specified a compression type of PAGE)
- TSQL commands to compress the database objects.
Examples :
EXEC dbo.UncompressedObjects
@database = 'MyDatabase'
, @compressiontype = 'ROW'
EXEC dbo.UncompressedObjects
@database = 'Adventureworks'
, @compressiontype = 'PAGE'
, @emailrecipients = 'emailaddress@domain.com'
, @emailprofile = 'Profile Name'
NB :
I've also supplied stored procedure 'UncompressedServerObjects' which simply executes UncompressedObjects for each user database. I schedule this via SQL Agent so I know if objects are created without compression.
I hope people find this script useful.
Richard Doering
http://sqlsolace.blogspot.com
