Tracy Boggiano has a script to help you figure out which indexes make sense to compress:
We can write procedure check periodically rather a table will benefit from compression or not. There are a few tricks though:
- If you have sparse columns you cannot compress the table, we check for that in lines 70-74.
- Next if is a index type of 1 (a clustered index) it cannot contain LOB data types image, ntext, or text. Which these days shouldn’t be a problem, they have been after all deprecated, so you aren’t using them. We check for these in lines 76-84.
- Finally, we perform the same test for LOB data on index type of 2 (nonclustered indexes) in lines 85-95.
If everything checks out OK we call our second procedure to calculate the percentage of space we would space and if we would save more than 25% then we create script to compression the index. Now we have a @Debug parameter in here so it can just show us what code it would execute to determine what it will do.
Click through for the code for the two procedures Tracy has created.