Skip to content

Determining Which Indexes to Compress Among Thousands

Our Problem

In a shared in environment you may have hundreds of databases with the same schema but depending on the data loaded into them not all of them will benefit from being compressed so you don’t want to incur the overhead of compression on all the of the data.

Our Solution

We can write procedure check periodically rather a table will benefit from compression or not.  There are a few tricks though:

  1. If you have sparse columns you cannot compress the table, we check for that in lines 70-74.
  2. 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.
  3. 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.

NOTE: This objects are created in a DBA database if you create them in a different database you will need to edit line 99 in the first procedure to indicate which database you placed the procedures in.

Finale

Things I could be see be added is logging when an index is compressed to a table so if something happens to performance you could correlate it back to this event.  You may also not want to hard code the value of 25 percent.  Also in the first procedure you may want to have a way to tell it which databases you want it to consider for operation.  We actually have our procedure where it doesn’t do every database just the once that have identical schemas but different customer data.

 

6 thoughts on “Determining Which Indexes to Compress Among Thousands

  1. Richard says:

    Line 11 in the first script should read database_id > 4 instead of id > 4 .

    Reply
    • Tracy Boggiano says:

      Thanks for the catch. We have a custom view we use for our databases so I had replaced that part with the sys.databases. It has been corrected.

      Reply
  2. Richard says:

    In line 97 the called procedure should be prefixed with the name of the database where the stored procedures resides because inside the dynamic SQL a USE statement is used and the current database changes. EXEC .dbo.IndexMaint_CalcCompression.

    Reply
    • Tracy Boggiano says:

      Thanks. I added a note to that I create these in a DBA database so that will need to be edited if you create it in a different database.

      Reply
  3. Jon says:

    I don’t see the @debug parameter in the script. Am I missing it or is it in a different version?

    Reply
    • Tracy Boggiano says:

      I must have had that in a different version, I will update an add it to this version.

      Reply

Leave a Comment