1

Need to compress a giant table of 1.5TB in sql server 2008, which is the best option Page or row compression, please let me know the steps to do it.

  • 2
    This entirely depends on the data. Test it (perhaps on a subset of the data), and see what the difference is. – Max Vernon Sep 27 '16 at 2:48
  • Why would you like to compress the table anyway? Are you short on disk space? Got a IO bottleneck? Looking for performance improvement? If you already have a lot of CPU pressure, compression might decrease your performance. – vonPryz Sep 27 '16 at 7:49
  • Due to Short of disk space. – Sravan Sep 30 '16 at 3:03
2

There is no such a thing as "best option". If page would always be superior, why bother offering row as sub-par alternative or vice versa? It really depends on a lot of things, so further analysis is needed. Start by reading some materials about data compression. MS has published a technical article Data Compression: Strategy, Capacity Planning and Best Practices that's a bit old, but fits your Sql Server version.

By the way, Sql Server 2008 has been out of mainstream support since Q2 2014 (and this is assuming you are running the latest SP4 patchlevel). You really should consider upgrading into more recent a version of Sql Server.

0

First of all I would suggest to partition the table according to dependent queries if it's not partitioned and index the table accordingly (better to include partition base column in all indexes). Because querying such big table would cause to slow performance of the query and it will consume more CPU and memory as well.

Compression

Row level : compression does not compress instead it allows the usage of only the actual amount of storage required. Less compression achieved by this but less CPU overhead as well.

Page Level : compression eliminate duplicate value and keep only one. when we enable page level compression, row level compression is automatically enabled. More CPU overhead.

Though Page Level compression is superior (save more space), compression depends on the type of data you stored in the table, data duplication and it's HEAP or non HEAP. Non HEAP table gives better compression.

A table has three types of allocation unit IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA. The data stored in LOB_DATA and ROW_OVERFLOW_DATA does not get compressed.

I would suggest to go through https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx and analyze you environment accordingly before go ahead.

Thanks

  • 2
    Partitioning is not a silver bullet for improving query performance. – vonPryz Sep 27 '16 at 7:26
  • 2
    Suggesting partitioning without knowing the query patterns might not be a good idea. Partition elimination only happens when queries contain predicates on the partitioning key. – spaghettidba Sep 27 '16 at 7:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.