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.

1 comment:

  1. Windows Movie Maker 是一款简单而又强大的视频制作和编辑软件, 设计用于最新版本的 Windows7, Windows 8, Windows 10。 其经典版本则适用于 Windows Xp 和 Windows Vista。 Windows Movie Maker 包含的功能有 视频效果, 过场动画, 标题和备注, 添加音乐和配音, 时间轴调整, 和 自动视频制作。 新的效果和动画可以随时添加,已有的也可以随意修改,通过XML编码即可。 Windows Movie Maker 同样是一款具备所有基本功能的音频编辑软件。 Windows Movie Maker 能够给音频添加各种效果,例如淡入,淡出等等。 可以直接导出音频文件,也可以导出视频文件,完全按照你的需求定制。电影制作器下载

    ReplyDelete