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

SQL Server Row based compression Expand / Collapse
Author
Message
Posted Wednesday, November 10, 2010 2:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 2:26 AM
Points: 15, Visits: 120
Hi we want to upgrade our system from sql server 2005 to sql server 2008. we have some large sized tables such as about 60.000.000 rows and about 70gb with indexes. When we compress the data in this table the data size was shrinked from 35gb to 7 gb. This is very good change I want to learn that
Does this has any disadvantage?
Must i compress for indexes also?
Is the compression done as once or does it need maintance as periodically such as rebuilding indexes?
Thank you.
Post #1018427
Posted Wednesday, November 10, 2010 8:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:18 PM
Points: 974, Visits: 687
I believe that you will need to compress the nonclustered indexes separately to the tables.

Infact, just checked BOL:
The compression setting of a table is not automatically applied to its nonclustered indexes. Each index must be set individually.


Hope this helps,
Rich



Post #1018649
Posted Wednesday, November 10, 2010 8:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:18 PM
Points: 974, Visits: 687
Check out "Creating Compressed Tables and Indexes" in BOL with lists lots of considerations.

Hope this helps,
Rich



Post #1018653
Posted Wednesday, November 10, 2010 8:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:20 PM
Points: 32,764, Visits: 14,928
I think the biggest thing is that it requires CPU, so if you are CPU bound, this might not be good for you. BOL has other issues to consider.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1018696
Posted Wednesday, November 10, 2010 11:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
Like Steve said the only disadvantage is if you are CPU bound, decompressing the pages when they move from the storage engine to the relation engine is where the CPU overhead comes into play.

row compression re-writes the data pages to a new format that allows for better utilization of space. smaller data allows for better use of pages so total read's and write's should go down as well as memory required as they are storing less pages.

if you want to see the stats on what compression is supposesed to do, just an estimate, you can run sp_estimate_data_compression_savings http://msdn.microsoft.com/en-us/library/cc280574.aspx.

Page compression inherits Row compression, but not the other way around.

but compression, page and/or row can be applied to "standard" tables, partitioned tables, "standard" indexes, and partitioned indexes differently.

Example:

Table Customer, with Clustered Index Customer_Clustered

you can page compress Customer, and row compress Customer_Clustered. I would take a look at the sp_estimate_data_compression_savings to figure out which route is best for you.

you need to maintain the indexes the same way, but do not need to maintain compressed pages any differently than you would regular pages.


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #1018847
Posted Wednesday, November 10, 2010 11:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 2,740, Visits: 2,949
Bradley B (11/10/2010)
Example:

Table Customer, with Clustered Index Customer_Clustered

you can page compress Customer, and row compress Customer_Clustered.


If the table has a clustered index the table and clustered index are essentially the same thing. So if you page compress the table and then row compress the clustered index, the table/clustered index will be row compressed and duplicate work is done.

The following statements net the same result.

ALTER TABLE Customer REBUILD WITH (DATA_COMPRESSION = PAGE);

ALTER INDEX Customer_Clustered ON Customer REBUILD WITH (DATA_COMPRESSION = PAGE);


Non-clustered indexes however can have different compression than the clustered index/table.
Post #1019067
Posted Wednesday, November 10, 2010 11:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 2:26 AM
Points: 15, Visits: 120
Thank you for your reply. I want to learn that does it need any maintance after the compressing. For example we are rebuilding indexes periodically because they are demaging by the time. Do i have to maintance for the compressed data. Other way, when data is inserting into the table, does it written as compressed or does it need any maintance periodically for index and for table both.
Post #1019072
Posted Wednesday, November 10, 2010 11:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 2,740, Visits: 2,949
If you compress a table the data written to that table will be compressed also but compressed indexes, clustered or non-clustered, require the same maintenance as uncompressed indexes. ie; you need to defrag/rebuild them from time to time
Post #1019075
Posted Thursday, November 11, 2010 8:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
My Bad, I was thinking Non-Clustered and Typing Clustered.

Jeremy was right.

and maintaining indexes does not change, what you would do for non-compressed you would do for compressed.

Here are the list of compression cross applications straight from the camel's mouth. http://msdn.microsoft.com/en-us/library/cc280449.aspx

A whole table that is stored as a heap.

A whole table that is stored as a clustered index.

A whole nonclustered index.

A whole indexed view.

For partitioned tables and indexes, the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting.


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #1019389
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse