Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Page level compression - Indices/Partitions/Heap Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 1,295, Visits: 2,994

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?

Post #1476734
Posted Tuesday, July 23, 2013 11:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 737, Visits: 5,460
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
Post #1476737
Posted Tuesday, July 23, 2013 12:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1476753
Posted Tuesday, July 23, 2013 1:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
As a side point, isn't compression limited to Enterprise Edition..

CEWII
Post #1476760
Posted Tuesday, July 23, 2013 1:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:49 AM
Points: 1,295, Visits: 2,994
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?
Post #1476763
Posted Tuesday, July 23, 2013 1:30 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 737, Visits: 5,460
Nope. You still would need to (if you deem it worth the time and effort) go back and compress the non-clustered indexes.
Post #1476773
Posted Friday, July 26, 2013 5:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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
Post #1477950
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse