Saturday, October 15, 2016

When to use Row and Page Compression in SQL Server

http://www.sql-datatools.com/2016/10/data-compression-in-sql-server.html
SQL Server supports row and page compression on tables, indexes and partitions inside a database to reduce the size of the database. This feature can lead to reduced I/O and better performance. However, it can also result in additional CPU usage in some cases, outweighing the benefits of data compression. SQL Server query optimiser does not cost the overhead of expanding the compressed data, which can lead to plan regression.
We can enable or disable ROW or PAGE compression online or offline. Enabling compression on a heap is single threaded for an online operation. There are lots of situations where we can use row and page compression inside our database but we should take care of the followings-


  • A table cannot be enabled for compression when the maximum row size plus the compression overhead exceeds the maximum row size of 8060 bytes. If a table contains any column having data type char (8000) or char (59) then it will increase the overhead on the system while trying to compress the table and not able to compress this. Compression enforces the following two rules:
  1. An update to a fixed-length type must always succeed.
  2. Disabling data compression must always succeed. Even if the compressed row fits on the page, which means that it is less than 8060 bytes; SQL Server prevents updates that would not fit on the row when it is uncompressed.
  • System tables do not go under compression.
  • Compression can allow more rows to be stored on a page, but does not change the maximum row size of a table or index.
  • For row and page compression, the row-size check is performed when the object is initially compressed, and then checked as each row is inserted or modified.
  • When a list of partitions is specified, the compression type can be set to ROW, PAGE, or NONE on individual partitions. If the list of partitions is not specified, all partitions are set with the data compression property that is specified in the statement. When a table or index is created, data compression is set to NONE unless otherwise specified. When a table is modified, the existing compression is preserved unless otherwise specified.
  • Nonclustered indexes do not inherit the compression property of the table. To compress indexes, you must explicitly set the compression property of the indexes. By default, the compression setting for indexes will set to NONE when the index is created.
  • When a clustered index is created on a heap, the clustered index inherits the compression state of the heap unless an alternative compression state is specified.
  • When a heap is configured for page-level compression, pages receive page-level compression only in the following ways:
  1. Data is bulk imported with bulk optimisations enabled.
  2. Data is inserted using INSERT INTO ... WITH (TABLOCK) syntax and the table does not have a nonclustered index.
  3. A table is rebuilt by executing the ALTER TABLE ... REBUILD statement with the PAGE compression option.
  • New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.
  • Changing the compression setting of a heap requires all nonclustered indexes on the table to be rebuilt so that they have pointers to the new row locations in the heap.

No comments:

Post a Comment

Popular Posts