最近做了一个关于数据压缩的项目，要把整个SQL SERVER服务器下所有的表对象要改成页压缩。于是趁此机会了解了一下SQL SERVER下压缩技术。
这篇文章几乎就是完全指导手册了
https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
当然这里还有技术wiki page
https://msdn.microsoft.com/en-us/library/cc280449.aspx
那看了这么多，这里总结一下：
1）SQL SERVER下一共有两种压缩类型：Row Compression和Page Compression
2）Row Compression把固定长度的数据列存成可变长度的格式，然后每列取4位（bits）来记录数据的长度，NULL和0除了那4位对数据存储空间不做任何影响。翻译过来是这样，但其实还是不是很懂。然而Page Compression则易懂得多了，前缀压缩和字典压缩技术。先进行前缀压缩，然后对整个页面进行字典压缩达到压缩比的最大化。
3）想知道一个数据库是否已经应用了压缩，查下这张DMV:select * from sys.dm_db_persisted_sku_features;
4）预估空间上的节省（Estimated space savings）
这个存储过程可以用来预估单张表大概可以节省多少数据库存储空间:sp_estimate_data_compression_savings. 整个计算过程是取样放到tempdb下而不是全表扫描。
5）什么样的数据压缩比率最大最有效？空值，大部分空间都用不到的固定长度数据（字符或者数字），前缀值重复率高的数据。
6）压缩对LOB、ROW_OVERFLOW数据页和FILESTREAM数据不奏效
7）压缩完的数据页从磁盘读进内存是不解压的，数据页只有在参与排序（sort）、连接（Join）、筛选（filter）或者被更新的时候才会被解压缩
8）解压缩的过程并非说在内存里面复制出另外一份数据来解压缩，而是用CPU计算后解压。
9）压缩技术对于表扫描的作用显然要比查找大，这样才能体现出在大存储空间下压缩对IO优化的优势
10）那么按理来说，压缩提升了IO和内存，以CPU作为代价。而Row Compression的CPU代价显然要比Page Compression要低，但是Page Compression的压缩比要更高。文章中说到Row Compression仅比没压缩的CPU多出10 percent，我没有实际测试过。
11）对于Page Compression，建议是用在那些少更新的大表、服务器上CPU的占用还算低的情况下。而虽然前面提到了数据页在参与排序（sort）、连接（Join）、筛选（filter）或者被更新的时候才会被解压缩，但是文章中提到了如果查询语句的本身就存在复杂的聚合运行逻辑和连接，那压缩对其造成的影响将不是一个大的方面。其实按道理来讲，可以说压缩技术即便消耗了更多的CPU资源，但是考虑到在一个大的压缩比的前提下，即便参与数据在参与聚合或者连接的时候需要被解压缩，但是这一切毕竟是在内存里面，比起没有压缩的页面需要重新被读取到内存里面（一般情况下，如果是数据仓库的话，数据页面很难停留在内存里面太久），我想要更划算，毕竟内存的读写能力和磁盘的读写能力不是能相提并论的。
12）那是决定Row Compression还是Page Compression？原则是U（update）和S（scan）。被更新得越频繁的表越适合Row Compression，更多参与查询的表越适合Page Compression。不过我觉得，这个还要更数据量的大小有关。比如如果某张表的数据量非常大，10几个G或者数亿行数据行的数据量的话，Page Compression的高压缩比的优势太大了。下列语句可以查出数据库中表更新和扫描的频率
--Update SELECT o.name AS [Table_Name], x.name AS [Index_Name], i.partition_number AS [Partition], i.index_id AS [Index_ID], x.type_desc AS [Index_Type], i.leaf_update_count * 100.0 / (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + i.leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count ) AS [Percent_Update] FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i JOIN sys.objects o ON o.object_id = i.object_id JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id WHERE (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count) != 0 AND objectproperty(i.object_id,'IsUserTable') = 1 ORDER BY [Percent_Update] ASC --Scan SELECT o.name AS [Table_Name], x.name AS [Index_Name], i.partition_number AS [Partition], i.index_id AS [Index_ID], x.type_desc AS [Index_Type], i.range_scan_count * 100.0 / (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + i.leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count ) AS [Percent_Scan] FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i JOIN sys.objects o ON o.object_id = i.object_id JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id WHERE (i.range_scan_count + i.leaf_insert_count + i.leaf_delete_count + leaf_update_count + i.leaf_page_merge_count + i.singleton_lookup_count) != 0 AND objectproperty(i.object_id,'IsUserTable') = 1 ORDER BY [Percent_Scan] DESC
13）页压缩级别索引/表在非叶级其实是用行压缩来压缩数据的，只有叶级才是用的页压缩；
那么如果要压缩，需要注意些什么呢？或者说对系统有哪些影响呢？
压缩都是通过REBUILD INDEX来完成。
SORT_IN_TEMPDB选项影响tempdb的空间增长
ONLINE影响用户数据库的空间增长和事务日志文件大小的增长
RECOVERY MODE影响事务日志文件大小的增长
表或者索引在被压缩的过程中，旧的索引在被替换前是和被压缩后的索引同时存在用户数据库里面的，替换后才把空间还给文件组，那么最起码一条索引所需要的空间只是索引现有空间大小+（索引现有空间大小-savings）
Online选项会消耗更多的CPU
对于tempdb空间增长的影响
前面提到关于Page Compression的压缩比比Row Compression大这个事情，其实这个比例并没有我想象中那么大。我拿了一张两千三百多万行数据的表进行测试发现，两者的压缩比率差异其实不大。数据来源于sys.sysindexes这张系统视图。
下图可以是Row Compression，non-compression和和Page Compression的数据页面占用情况。第一行记录为Row Compression，第二是non-compression，第三是Page Compression
而对于是否Page Compression会影响到数据插入的性能，虽然贴出的连接里面的文章有一部分讲到和提供数据关于这部分，不过我自己还是做了一个实验。我找来了一个文件，文件大小200MB左右，行数为58万行。分别插入到没有压缩和有页压缩的表。
结果是：没有页压缩的花了3分49秒，而又页压缩反而只花3分8秒就完成了整个插入。当然这样听起来好像不make sense，也和文章中的结果有出入。不过由于受到数据量已经当时磁盘繁忙程度的这些因素的影响。不过，我觉得这个从一方面也是可以说明页压缩对与BULK INSERT时的性能影响还是小的。
如何和何时压缩？
1）加入WITH ONLINE选项可以提高可用性，因为不需要锁表；WITH OFFLINE则是提高了速度，因为表被锁住了，而且比起ONLINE用到更少的资源；
2）一般来说，同一时间内重建索引的工作任务越少越好，因为越多的并发操作会造成对tempdb/用户数据库的空间增长。当然，如果系统的空间，内存和CPU可以承受得了的话，多个工作任务同时在工作可以加快整个完成的进度；
3）顺序最好是从小到大。小的索引需要的资源和空间更少，更快完成，完成之后就可以释放空间出来给下一个任务用；
4）SORT_IN_TEMPDB选项推荐射程ON。如果设置为OFF就意味着索引数据的排序是在用户数据库中进行的，设想下如果此时别的表也在进行DML操作，而重建索引也要向SQL Server请求区的分配，这样容易造成最后索引的区的物理连续性差的问题。另一个关于SORT_IN_TEMPDB为ON比OFF好的地方是，数据排序在TEMPDB里面以为着如果TEMODB在另外一个磁盘上，这整个read和write的操作就是分开的。数据的读取就不会受到写的干扰（在不考虑其他的干扰因素的情况，就单从两者本身来看）。
下面是关于Merge时，压缩表和非压缩表的性能统计
| No. | NonDCP | DCP | Source Records |
Target Records |
||||
| Duration (mm:ss) |
CPU
Time (ms) |
Elapse
Time (ms) |
Duration (mm:ss) |
CPU
Time (ms) |
Elapse
Time (ms) |
|||
| 1 | 2:30 | 210,429 | 147,379 | 5:22 | 968,206 | 316,477 | 448,211 | 23,735,427 |
| 2 | 2:51 | 229,576 | 169,303 | 7:48 | 930,565 | 466,884 | ||
| 3 | 1:54 | 208,043 | 113,002 | 4:06 | 979,345 | 244,221 | ||
| 4 | 2:33 | 194,640 | 150,900 | 6:07 | 1,003,645 | 362,521 | ||
| 5 | 3:22 | 224,155 | 198,991 | 10:21 | 1,004,464 | 618,668 | ||
| 6 | 1:03 | 172,507 | 61,761 | 4:06 | 858,540 | 244,295 | ||
| 1 | 0:38 | 139,386 | 37,557 | 6:45 | 366,696 | 405,294 | 200,000 | |
| 2 | 0:43 | 222,052 | 43,329 | 6:33 | 367,742 | 393,252 | ||
| 3 | 1:19 | 189,433 | 78,971 | 13:41 | 500,763 | 820,577 | ||
| 4 | 2:22 | 169,807 | 141,150 | 16:59 | 528,438 | 1,017,131 | ||
| 5 | 2:14 | 172,927 | 131,527 | 19:46 | 477,566 | 1,181,484 | ||
| 6 | 1:42 | 170,828 | 99,636 | 12:44 | 522,478 | 760,967 | ||
| 1 | 1:57 | 153,987 | 115,494 | 11:53 | 504,149 | 710,508 | 100,000 | |
| 2 | 1:54 | 189,744 | 111,644 | 11:00 | 502,167 | 657,959 | ||
| 3 | 0:26 | 137,482 | 26,334 | 12:58 | 508,766 | 778,036 | 10,000 | |
| 4 | 1:17 | 159,919 | 76,088 | 18:09 | 507,877 | 1,087,612 | ||
| 1 | 6:48 | 95,315 | 306,116 | 7:02 | 228,791 | 336,436 | 200,000 | 10,000,000 |
| 2 | 1:03 | 94,927 | 62,243 | 6:03 | 220,648 | 362,619 | ||
| 3 | 1:27 | 84,284 | 86,727 | 5:51 | 233,049 | 350,586 | ||
| 1 | 2:32 | 102,183 | 115,536 | 5:59 | 231,521 | 288,669 | 448,211 | |
| 2 | 3:22 | 89,213 | 201,905 | 3:45 | 210,788 | 224,190 | ||
| 3 | 1:40 | 121,226 | 99,773 | 6:03 | 233,159 | 361,897 | ||
| 1 | 5:05 | 78,362 | 247,758 | 5:19 | 194,986 | 262,280 | 200,000 | 9,000,000 |
| 2 | ||||||||
| 3 | ||||||||
| 1 | 10:32 | 100,422 | 547,041 | 21:58 | 261,801 | 339,602 | 448,211 | |
| 2 | ||||||||
| 3 | ||||||||
| 1 | 5:42 | 65,363 | 306,369 | 5:10 | 163,240 | 266,966 | 200,000 | 7,000,000 |
| 2 | 5:21 | 60,248 | 298,421 | 5:15 | 159,027 | 270,725 | ||
| 3 | ||||||||
| 1 | 10:58 | 103,022 | 633,648 | 4:56 | 192,536 | 263,619 | 448,211 | |
| 2 | 9:34 | 101,491 | 556,684 | 6:29 | 218,027 | 345,312 | ||
| 3 | ||||||||
| 1 | 6:03 | 52,056 | 245,197 | 5:50 | 131,914 | 235,124 | 200,000 | 5,000,000 |
| 2 | 0:37 | 58,690 | 36,526 | 3:09 | 135,253 | 186,867 | ||
| 3 | 0:27 | 54,552 | 26,333 | 19:54 | 126,673 | 180,134 | ||
| 1 | 7:00 | 81,388 | 401,669 | 3:54 | 165,345 | 209,988 | 448,211 | |
| 2 | 4:38 | 89,667 | 276,572 | 4:51 | 183,504 | 290,928 | ||
| 3 | 0:47 | 68,671 | 45,619 | 2:34 | 134,988 | 153,485 | ||
| 1 | 6:51 | 65,751 | 410,657 | 3:38 | 107,484 | 217,321 | 448,211 | 2,000,000 |
| 2 | 2:13 | 57,971 | 63,175 | 2:47 | 85,988 | 105,135 | ||
| 3 | 1:51 | 62,573 | 107,375 | 1:58 | 102,711 | 114,285 | ||
| 1 | 5:43 | 37,176 | 274,401 | 3:37 | 76,924 | 142,695 | 200,000 | |
| 2 | 0:38 | 34,495 | 37,384 | 3:26 | 72,385 | 204,838 | ||
| 3 | 1:17 | 36,603 | 73,846 | 2:20 | 58,438 | 133,778 | ||
| 4 | 0:51 | 38,515 | 49,970 | 2:27 | 64,054 | 145,694 | ||
| 1 | 0:53 | 22,124 | 52,304 | 1:03 | 47,768 | 62,393 | 100,000 | |
| 2 | 0:33 | 22,870 | 31,904 | 1:08 | 46,894 | 65,880 | ||
| 3 | 0:40 | 23,554 | 39,867 | 1:13 | 49,951 | 72,627 | ||
| 4 | 1:09 | 23,100 | 62,986 | 1:41 | 52,775 | 93,955 | ||
下面是做Insert Into时，两者的差异
| rows | Page Compression | None Compression | ||||||||
| Duration | Compilation | Execution | Duration | Compilation | Execution | |||||
| CPU time | elapsed time | CPU time | elapsed time | CPU time | elapsed time | CPU time | elapsed time | |||
| 2000000 | 2:37 | 15 | 18 | 166422 | 157319 | 1:18 | 15 | 19 | 85972 | 78137 |
| 2000000 | 3:51 | 25 | 25 | 176109 | 231247 | 2:55 | 15 | 50 | 97391 | 174410 |
| 2000000 | 2:47 | 16 | 17 | 179870 | 167471 | 1:12 | 20 | 20 | 82822 | 72273 |
| 500000 | 0:46 | 16 | 18 | 44287 | 46121 | 0:28 | 16 | 22 | 25584 | 27876 |
| 500000 | 0:44 | 0 | 27 | 45539 | 44261 | 0:26 | 15 | 74 | 25508 | 26172 |
| 500000 | 0:41 | 16 | 18 | 43319 | 41406 | 0:25 | 16 | 19 | 25461 | 24834 |
| 100000 | 0:16 | 15 | 19 | 8704 | 16694 | 0:17 | 20 | 20 | 5663 | 16672 |
| 100000 | 0:12 | 15 | 44 | 9469 | 11129 | 0:06 | 31 | 34 | 5149 | 6590 |
| 100000 | 0:09 | 15 | 18 | 8801 | 9500 | 0:05 | 16 | 19 | 4837 | 5521 |
| 50000 | 0:12 | 0 | 91 | 4306 | 7309 | 0:09 | 31 | 610 | 2666 | 5676 |
| 50000 | 0:04 | 21 | 21 | 4116 | 4628 | 0:10 | 31 | 45 | 3199 | 10549 |
| 50000 | 0:14 | 31 | 92 | 4430 | 11728 | 0:13 | 15 | 19 | 2606 | 10496 |
下面是做BULK INSERT时的性能统计
| rows | file size | Page Compression | None Compression | ||||||||
| Duration | Compilation | Execution | Duration | Compilation | Execution | ||||||
| CPU time | elapsed time | CPU time | elapsed time | CPU time | elapsed time | CPU time | elapsed time | ||||
| 4330601 | 1500mb | 11:20 | 438893 | 680193 | 5:00 | 0 | 548 | 186078 | 298339 | ||
| 4330601 | 1500mb | 8:28 | 0 | 809 | 406976 | 507049 | 3:09 | 0 | 28 | 144020 | 189424 |
| 4330601 | 1500mb | 9:01 | 0 | 375 | 412700 | 540650 | 5:12 | 0 | 41 | 167202 | 311729 |
| 580431 | 220mb | 4:02 | 0 | 404 | 67673 | 262059 | 3:41 | 0 | 188 | 27285 | 220897 |
| 580431 | 220mb | 7:11 | 297 | 4561 | 71042 | 417186 | 3:50 | 358 | 2804 | 26848 | 209304 |
| 100000 | 36mb | 0:19 | 0 | 537 | 11310 | 18454 | 0:06 | 0 | 21 | 4570 | 6436 |
| 100000 | 36mb | 0:22 | 0 | 280 | 10764 | 21940 | 0:03 | 0 | 10 | 3042 | 3412 |
| 100000 | 36mb | 7:17 | 0 | 18 | 4399 | 5294 | 0:12 | 0 | 170 | 8268 | 11954 |
| 5000 | 1.8mb | 0:01 | 0 | 29 | 577 | 1085 | 0:00 | 0 | 209 | 172 | 190 |
| 5000 | 1.8mb | 0:01 | 0 | 20 | 780 | 935 | 0:00 | 0 | 20 | 187 | 648 |
| 5000 | 1.8mb | 0:01 | 0 | 29 | 640 | 913 | 0:00 | 0 | 29 | 141 | 181 |
更新于2015年7月14日
最近在测试这个DATA COMPRESSION的时候发现这个东西和非压缩相比的性能差异太大。可以说很差。我们专门测试了MERGE语句。
服务器配置:
CPU数量：48
内存：250GB
| Source Rows | Target Rows | Affected Rows | Clean Buffer | MAXDOP(server) | Page Compression | None Compression | ||||||||
| Duration | Compilation | Execution | Duration | Compilation | Execution | |||||||||
| CPU time | elapsed time | CPU time | elapsed time | CPU time | elapsed time | CPU time | elapsed time | |||||||
| 448211 | 23735427 | 448211 | No | 8 | 0:42:46 | 0 | 0 | 804137 | 2564483 | 0:03:06 | 0 | 0 | 153412 | 182691 |
| 448211 | 23735427 | 448211 | No | 12 | 0:20:49 | 187 | 950 | 1056924 | 1248271 | 0:04:33 | 0 | 0 | 170978 | 273445 |
| 448211 | 23735427 | 448211 | No | 8 | 0:33:01 | 0 | 0 | 813337 | 1982926 | 0:02:12 | 0 | 0 | 159322 | 132068 |
| 100000 | 23735427 | 100000 | No | 8 | 0:08:33 | 187 | 293 | 406305 | 513433 | 0:00:59 | 172 | 890 | 125767 | 57942 |
| 448211 | 10448211 | 448211 | No | 8 | 0:22:12 | 0 | 0 | 409812 | 1322453 | 0:01:33 | 0 | 0 | 85502 | 75388 |
| 448211 | 10448211 | 448211 | No | 8 | 0:14:02 | 0 | 0 | 418488 | 842584 | 0:01:29 | 0 | 0 | 112305 | 87517 |
| 448211 | 10448211 | 448211 | No | 8 | 0:18:06 | 0 | 0 | 421351 | 1086079 | 0:01:27 | 0 | 0 | 112305 | 87517 |
| 448211 | 7448211 | 448211 | No | 8 | 0:10:47 | 0 | 0 | 320241 | 641359 | 0:08:33 | 0 | 0 | 87281 | 497746 |
| 448211 | 7448211 | 448211 | No | 8 | 0:11:53 | 0 | 0 | 319274 | 713046 | 0:03:14 | 0 | 0 | 97454 | 193725 |
| 448211 | 7448211 | 448211 | No | 8 | 0:19:17 | 0 | 0 | 294811 | 1157597 | 0:05:07 | 0 | 0 | 90200 | 307424 |
| 448211 | 5448211 | 448211 | No | 8 | 0:06:10 | 0 | 0 | 238120 | 368797 | 0:01:10 | 0 | 0 | 62965 | 67726 |
| 448211 | 5448211 | 448211 | No | 8 | 0:14:05 | 0 | 0 | 246781 | 845589 | 0:01:20 | 0 | 0 | 72868 | 79859 |
| 448211 | 5448211 | 448211 | No | 8 | 0:08:48 | 0 | 0 | 240345 | 527858 | 0:01:22 | 0 | 0 | 72337 | 82615 |
| 448211 | 10448211 | 448211 | Yes | 8 | 0:02:55 | 156 | 684 | 393410 | 174062 | 0:02:29 | 156 | 974 | 91367 | 147993 |
| 448211 | 10448211 | 448211 | Yes | 8 | 0:18:21 | 234 | 842 | 435104 | 1100792 | 0:02:53 | 156 | 365 | 95832 | 172780 |
| 448211 | 10448211 | 448211 | Yes | 8 | 0:12:22 | 171 | 936 | 474614 | 741331 | 0:03:40 | 171 | 1328 | 105097 | 219210 |
| 448211 | 7448211 | 448211 | Yes | 8 | 0:02:34 | 421 | 1014 | 297963 | 153330 | 0:03:53 | 297 | 10692 | 78715 | 222858 |
| 448211 | 7448211 | 448211 | Yes | 8 | 0:09:52 | 124 | 466 | 319444 | 591567 | 0:01:24 | 203 | 229 | 80859 | 83397 |
| 448211 | 7448211 | 7448211 | Yes | 8 | 0:14:24 | 141 | 460 | 332657 | 862597 | 0:00:33 | 125 | 769 | 74397 | 32070 |
| 448211 | 7448211 | 7448211 | No | 12 | 0:09:38 | 250 | 3376 | 333971 | 574568 | 0:01:11 | 297 | 768 | 92164 | 70542 |
| 448211 | 5448211 | 448211 | Yes | 8 | 0:06:46 | 203 | 4521 | 230489 | 401281 | 0:04:38 | 140 | 4696 | 69610 | 273695 |
| 448211 | 5448211 | 448211 | Yes | 8 | 0:09:36 | 171 | 271 | 230588 | 575856 | 0:03:49 | 171 | 728 | 74331 | 228040 |
| 448211 | 5448211 | 448211 | Yes | 8 | 0:09:50 | 140 | 818 | 271810 | 589009 | 0:03:55 | 140 | 886 | 84321 | 233986 |
| 448211 | 5448211 | 448211 | No | 12 | 0:04:06 | 187 | 2423 | 246825 | 243315 | 0:03:11 | 0 | 0 | 78174 | 190007 |