Post reply

Page level compression - Indices/Partitions/Heap

  • July 23, 2013 at 11:49 am

    #296671

    I have read a lot of articles on compression but i couldn't find a definitive answer for these:

    i) if a table has no clustered index and has non-clustered indices, do i need to compress the table first and then the nc indices?

    ii)if a table has clustered index and has non-clustered indices, would just compressing the clustered index also compress the nc index?

    iii) When compressing the clustered index, can this be with ONLINE=ON option?

  • July 23, 2013 at 11:58 am

    #1635086

    Having just dealt with compressing a table with both clustered and non-clustered indexes:

    i) I don't believe so. I believe you can compress just the indexes, but you probably won't gain much savings there.

    ii) No.

    iii) I believe it is. I can't say for sure, my job that did the compression of the clustered index doesn't have an "online=on" set, but I also did it during off-hours so I wouldn't affect the users...

    Jason

  • July 23, 2013 at 12:50 pm

    #1635098

    curious_sqldba (7/23/2013)

    i) if a table has no clustered index and has non-clustered indices, do i need to compress the table first and then the nc indices?

    Depends. Do you want the table and the nonclustered indexes compressed? If so, you have to compress the table and the nonclustered indexes. Order is irrelevant, but to be honest it's probably fastest to disable the nonclustered indexes, compress the table then rebuild the nonclustered indexes with compression. Otherwise they all get rebuilt twice.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 23, 2013 at 1:04 pm

    #1635104

    As a side point, isn't compression limited to Enterprise Edition..

    CEWII

  • July 23, 2013 at 1:14 pm

    #1635107

    GilaMonster (7/23/2013)

    curious_sqldba (7/23/2013)

    i) if a table has no clustered index and has non-clustered indices, do i need to compress the table first and then the nc indices?

    Depends. Do you want the table and the nonclustered indexes compressed? If so, you have to compress the table and the nonclustered indexes. Order is irrelevant, but to be honest it's probably fastest to disable the nonclustered indexes, compress the table then rebuild the nonclustered indexes with compression. Otherwise they all get rebuilt twice.

    Thanks. How about this one?

    ii)if a table has clustered index and has non-clustered indices, would just compressing the clustered index also compress the nc index?

  • July 23, 2013 at 1:30 pm

    #1635115

    Nope. You still would need to (if you deem it worth the time and effort) go back and compress the non-clustered indexes.

  • July 26, 2013 at 5:37 am

    #1636104

    Best is to run the compression estimation on each structure and then decide which ones are worth row-compressing, which ones page-compressing, and which ones are best left alone.

    If page compression only gives you 30% space savings, it is probably not a good idea, but if it gives you 80% savings, then it might be worth considering (still not certain, if it is updated frequently it may still be worth leaving alone - it depends!)

    Cheers,

    JohnA

    MCM: SQL2008

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply