SQL Server Row based compression
-
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.
-
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[p]
[/p] -
Check out "Creating Compressed Tables and Indexes" in BOL with lists lots of considerations.
Hope this helps,
Rich[p]
[/p] -
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.
-
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
Channel: https://www.youtube.com/@Tales-from-the-Field -
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.
-
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.
-
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
-
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
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply