*** 作系统:WIN SERVER 2012 R2 Standard 数据库系统: sqlSERVER 2016 SP1 Enterprise Evaluation Edition 硬件环境:cpu E7-8891 V3 2.79GHZ 80 CORES 内存 256GB 1.sqlServer数据压缩介绍 行压缩启用压缩只会更改与数据类型相关联的数据的物理存储格式，而不会更改其语法或语义。 当对一个或多个表启用压缩时，不需要更改应用程序。新的记录存储格式主要有以下更改： 1.减少了与记录相关联的元数据开销。 此元数据为有关列、列长度和偏移量的信息。 在某些情况下，元数据开销可能大于旧的存储格式。 2.对于数值类型（例如， integer、 decimal和 float）和基于数值的类型（例如， datetime 和 money）使用可变长度存储格式。 3.通过使用不存储空字符的可变长度格式来存储定长字符串。 页压缩 页压缩包含行压缩,以及前缀压缩和字典压缩,当使用页压缩时，将仅使用行压缩来压缩索引的非叶级别页,行压缩上面已经介绍. 页压缩对表、表分区、索引和索引分区都是类似的。 但对于字符串和其他数据类型而言，前缀压缩和字典压缩的原理都是相同的。 前缀压缩 对于要压缩的每一页，前缀压缩采用以下步骤： 1 对于每一列，将确定一个值，此值可用于减少每一列中的值的存储空间。 2 将创建表示每一列的前缀值的行，并将其存储在紧随页头之后的压缩信息(CI) 结构中。 3 列中重复的前缀值将由指向对应前缀的引用进行替换。如果行中的值与所选前缀值并不完全匹配,则仍会指示存在部分匹配。
2.创建表结构#建立一个具有行压缩的表use his;/****** Object: table [dbo].[test_compress] Script Date: 2018/3/7 13:46:43 ******/SET ANSI_NulLS OFFGOSET QUOTED_IDENTIFIER ONGOCREATE table [dbo].[test_compress_ROW]( ... ... ... CONSTRAINT [test_compress_ROW_pk] PRIMARY KEY CLUSTERED ( ... ... ...)WITH (PAD_INDEX = OFF,STATISTICS_norECOmpuTE = OFF,IGnorE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON,DATA_COMPRESSION=ROW) --压缩选项 ON [PRIMARY]) ON [PRIMARY]GO3.测试非压缩表的性能#可以用如下语句重新配置压缩选项,设置为不压缩 alter table [test_compress_ROW] rebuild with (data_compression=none)#模拟在非压缩表上插入100w条数据,并输出性能数据set statistics io onset statistics time oninsert into [test_compress_ROW]select top 1000000 * from [dbo].[test_origin]order by IDsql Server 分析和编译时间: 表 ‘test_compress_ROW‘。扫描计数 0，逻辑读取 286554 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 ‘test_origin‘。扫描计数 1，逻辑读取 56317 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。sql Server 执行时间: cpu 时间 = 9407 毫秒，占用时间 = 12560 毫秒。(1000000 行受影响)#查看非压缩表上的空间占用exec sp_spaceused [test_compress_ROW]name rows reserved data index_size unusedtest_compress_ROW 1000000 447648 KB 444448 KB 3032 KB 168 KB #前面的插入已经将数据写入到缓存,为了确保真实还原性能问题,我们需要关闭sql Server自身的执行计划及缓存。清除缓存。DBCC DROPCLEANBUFFERS --清除缓冲区DBCC FREEPROCCACHE --删除计划高速缓存中的元素 #在非压缩表上执行select 并记录性能统计信息#这里查询10000条数据作为例子,数据块多更能反映性能对比 select top 10000 * from test_compress_ROWsql Server 分析和编译时间: cpu 时间 = 0 毫秒，占用时间 = 4 毫秒。(10000 行受影响)表 ‘test_compress_ROW‘。扫描计数 1，逻辑读取 575 次，物理读取 1 次，预读 5424 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 sql Server 执行时间: cpu 时间 = 93 毫秒，占用时间 = 870 毫秒。4. 测试行压缩表的性能 #清空数据 truncate table [test_compress_ROW] #可以用如下语句重新配置压缩选项,设置为行压缩 alter table [test_compress_ROW] rebuild with (data_compression=row)#模拟在行压缩表上插入100w条数据,并输出性能数据set statistics io onset statistics time oninsert into [test_compress_ROW]select top 1000000 * from [dbo].[test_origin]order by [backupdate],[sysdate],[sno],[serverID]sql Server 分析和编译时间: cpu 时间 = 0 毫秒，占用时间 = 9 毫秒。表 ‘test_compress_ROW‘。扫描计数 0，逻辑读取 107897 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 ‘test_origin‘。扫描计数 1，逻辑读取 56317 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。sql Server 执行时间: cpu 时间 = 7109 毫秒，占用时间 = 7314 毫秒。(1000000 行受影响)#查看页压缩表上的空间占用exec sp_spaceused [test_compress_ROW]name rows reserved data index_size unusedtest_compress_ROW 1000000 128200 KB 127728 KB 408 KB 64 KB#前面的插入已经将数据写入到缓存,我们需要关闭sql Server自身的执行计划及缓存。清除缓存。DBCC DROPCLEANBUFFERS --清除缓冲区DBCC FREEPROCCACHE --删除计划高速缓存中的元素 #在页压缩表上执行select 并记录性能统计信息#这里查询10000条数据作为例子,数据块多更能反映性能对比 select top 10000 * from test_compress_ROWsql Server 分析和编译时间: cpu 时间 = 0 毫秒，占用时间 = 4 毫秒。(10000 行受影响)表 ‘test_compress_ROW‘。扫描计数 1，逻辑读取 235 次，物理读取 1 次，预读 4952 次，lob 逻辑读取 0 次,lob 物理读取 0 次，lob 预读 0 次。sql Server 执行时间: cpu 时间 = 156 毫秒，占用时间 = 553 毫秒。5.测试页压缩表的性能 #清空数据 truncate table [test_compress_ROW] #可以用如下语句重新配置压缩选项,设置为行压缩 alter table [test_compress_ROW] rebuild with (data_compression=page)#模拟在页压缩表上插入100w条数据,[serverID]sql Server 分析和编译时间: cpu 时间 = 0 毫秒，占用时间 = 0 毫秒。sql Server 分析和编译时间: cpu 时间 = 0 毫秒，占用时间 = 8 毫秒。表 ‘test_compress_ROW‘。扫描计数 0，逻辑读取 80357 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 ‘test_origin‘。扫描计数 1，逻辑读取 56317 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 sql Server 执行时间: cpu 时间 = 19156 毫秒，占用时间 = 19206 毫秒。(1000000 行受影响)#查看页压缩表上的空间占用exec sp_spaceused [test_compress_ROW]name rows reserved data index_size unusedtest_compress_ROW 1000000 172232 KB 171600 KB 544 KB 88 KB #前面的插入已经将数据写入到缓存,数据块多更能反映性能对比 select top 10000 * from test_compress_ROWsql Server 分析和编译时间: cpu 时间 = 0 毫秒，占用时间 = 4 毫秒。(10000 行受影响)表 ‘test_compress_ROW‘。扫描计数 1，逻辑读取 172 次，物理读取 1 次，预读 5248 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。sql Server 执行时间: cpu 时间 = 203 毫秒，占用时间 = 834 毫秒。。
6.性能数据对比 6.1数据大小对比 | 无压缩 | 行压缩 | 行压缩比例 | 数据大小(页压缩) | 页压缩比例 |
|---|---|---|---|---|
| 447648KB | 172232KB | 38.5% | 128200KB | 28.6% |
可以看到:页压缩的数据压缩比更佳！
6.2 insert cpu性能对比| 无压缩 | 行压缩 | 页压缩 |
|---|---|---|
| cpu 时间 = 9407 毫秒 | cpu 时间 = 7109 毫秒 | cpu 时间 = 19156 毫秒 |
可以看到：写入数据时行压缩和页压缩都需要压缩 *** 作,所以cpu占用明显要高出许多！页压缩的cpu占用明显要高出许多！
6.3 select cpu性能对比| 无压缩 | 行压缩 | 页压缩 |
|---|---|---|
| cpu 时间 = 93 毫秒 | cpu 时间 = 156 毫秒 | cpu 时间 = 203 毫秒 |
可以看到 读取数据时行压缩和页压缩都需要解压 *** 作,所以cpu占用明显要高出许多！其中页压缩cpu最高!
6.3 select io性能对比| 无压缩 | 行压缩 | 页压缩 |
|---|---|---|
| 逻辑读取 575 次，物理读取 1 次，预读 5424 次 | 逻辑读取 235 次，物理读取 1 次，预读 4952 次 | 逻辑读取 172 次，物理读取 1 次，预读 5248 次 |
可以看到 读取数据时行压缩和页压缩的逻辑读比非压缩的情况要优化很多,页压缩读取的数据块最少,io性能最好!
7.总结以及建议1.与业务商定归档数据日期界限 2.拆分数据库,早于界限日期为节点归档历史数据,因为这部分数据属于静止冷数据,比较适合设置成为页压缩模式 3.定期将数据迁移到归档数据库 4.晚于界限日期的数据设置为行压缩模式,因为涉及到insert的问题 5.备份策略:完整--差异--日志 8.注意事项 1.数据压缩功能仅在sqlSERVER2008以后的企业版和开发版中可用2.数据压缩可以让一张数据页存储更多的数据行，但是并不能改变单行数据最长8060字节这一限制。3.在一张已经设置了数据压缩的表上创建聚簇索引时，聚簇索引默认继承原表上的压缩选项4.在未设置聚簇索引的表上设置页面压缩时，只有以下情况才会获得页面压缩的实际效果： a.数据使用BulK INSERT语法添加到表中 b.数据使用INSERT INTO ... WITH (TABLOCK)语法添加到表中 c.执行带有页面压缩选项的ALTER table ... REBUILD命令 5.在未设置聚簇索引的表上更改压缩选项，会导致该表上所有非聚簇索引都需要重建，因为这些非聚簇索引指向的数据行地址已经都发生了改变。 6.在改变压缩选项时所需要的临时空间大小与创建索引是所需要的空间是一样的，因此对于分区表，我们可以逐个分区设置压缩选项来减少临时空间的需求压力。 7.sql Server 2008的压缩选项是工作在存储引擎层的，对于sql Server的其他部件来说这一特性是透明的，因此当我们用BulK LOAD的方式 将外面的数据导入sql Server时，会显著的增加cpu的工作载荷，同时将以压缩的数据表导出到外部文件时，可能会消耗比原来多很多的空间。 总结 以上是内存溢出为你收集整理的（4.18）数据压缩全部内容，希望文章能够帮你解决（4.18）数据压缩所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）