• SQL SERVER ->> Data Compression

    最近做了一个关于数据压缩的项目，要把整个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
  • 相关阅读:
    各种读取速度
    索引倒排
    清空mysql数据
    java随机读取文件
    移动文件
    输出字符串数组
    背包问题
    使用bloomfilter
    使用hash拆分文件
    判断文件的编码格式
  • 原文地址：https://www.cnblogs.com/jenrrychen/p/4608891.html
Copyright © 2020-2023  润新知