SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Row based compression


SQL Server Row based compression

Author
Message
ocolakoglu
ocolakoglu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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.
RichardDouglas
RichardDouglas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1171 Visits: 707
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



RichardDouglas
RichardDouglas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1171 Visits: 707
Check out "Creating Compressed Tables and Indexes" in BOL with lists lots of considerations.

Hope this helps,
Rich



Steve Jones
Steve Jones
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84233 Visits: 19223
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
My Blog: www.voiceofthedba.com
SQLBalls
SQLBalls
Right there with Babe
Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)

Group: General Forum Members
Points: 740 Visits: 630
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
JeremyE
JeremyE
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4582 Visits: 4070
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.
ocolakoglu
ocolakoglu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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.
JeremyE
JeremyE
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4582 Visits: 4070
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
SQLBalls
SQLBalls
Right there with Babe
Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)

Group: General Forum Members
Points: 740 Visits: 630
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search