（4.18）数据压缩

转自：https://www.cnblogs.com/chinesern/p/8886878.html

SqlServer数据压缩测试

环境说明

操作系统: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 OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE 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]

GO
3.测试非压缩表的性能
#可以用如下语句重新配置压缩选项,设置为不压缩 
alter table [test_compress_ROW] rebuild with (data_compression=none)


#模拟在非压缩表上插入100w条数据,并输出性能数据
set statistics io on
set statistics time on
insert into [test_compress_ROW]
select top 1000000 * from  [dbo].[test_origin]
order by id

SQL 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  unused
test_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_ROW

SQL 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 on
set statistics time on
insert 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  unused
test_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_ROW

SQL 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  unused
test_compress_ROW   1000000   172232 KB  171600 KB  544 KB      88 KB 



#前面的插入已经将数据写入到缓存,数据块多更能反映性能对比  
select top 10000 * from test_compress_ROW

SQL 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数据大小对比