Contents

1 一危 豢 蠍磯蓋
2 伎 豢 column-prefix
3 伎 豢 data-dictionary
4 Overhead
5
6 企 覦 碁煙, 磯 碁煙れ 豢
7 DB 覈 企 覦 碁煙 豢蠍
8 豢覿 譟壱
9 一 豢
10 蟆磯
11 谿瑚襭


SQL Server 2008 襦蟆 覲伎 蠍磯レ企.

1 一危 豢 蠍磯蓋 #

一危 豢 譬襯
  • Row Compression : int 3 4byte螳 1覦危碁
  • Page Compression : 螳 螳れ 1襷 (column-prefix 豢 data dictionary 豢)
  • 企 2螳 伎 碁煙り 譟伎覃 碁煙る 豢.
  • singleton Lookup 蟆曙磯 螻 伎 . (Compression/De-Compression 觜)
  • DDL 讌(襦 れ)
  • XML, BLOB, MAX 一危 .
  • Row Compression Vardecimal Storage Format 谿瑚.

Row Level 一危 豢觜
  • 4覦危 貉, 1覦危 -> 1覦危碁 豢
  • 1覦危 貉, 1覦危 -> 1覦危碁 豢

Common 'byte' pattern?
2螳 16讌襦 一危 0x020406FF, 0x0204CCFF螳 . 蠍一 Common 'byte' pattern '0x0204'企. 4覯讌 byte螳 FF襦 螳, 3覯讌 byte螳 螳螳 '06', 'CC'企襦 企 Common 'byte' pattern . 觜訣 襦 0xFFAABBCCDDEE 0x33AABBCCDDEE螳 . 企 Common 'byte' pattern . 覃 螳螳 2~5覯讌 byte 螳讌襷 1覯讌 byte螳 螳螳 'FF', '33'朱 るゴ蠍 覓語企.

2 伎 豢 column-prefix #

SQL Server column-prefix data襯 襦 貊襯 燕. 貊襯 anchor-record 手 覿襯碁. Anchor-recode 伎 る れ ル. 襷 Common bytepattern 覦蟆讌 覈詩覃 empty null襦 豬蠍. 襷 column-prefix襯 螳讌讌 る Anchor-record 焔讌 . れ 蠏碁手骸 螳 伎 貊れ ル .

data_compression01.jpg

SQL Server 螳螳 貉殊 貊襯 所, Common bytepattern 襭一 伎 Column-prefix襯 谿城.

data_compression02.jpg

SQL Server Page Header 覦襦 れ Anchor-Record襯 襷.

data_compression03.jpg

SQL Server Anhcor record襯 るジ 貊襦 豬蠍. 蠏碁覩襦 SELECT 貎朱Μ襦 譟壱螳 覿螳ロ. SQL Server Anchor-record Column-prefix襷 ロ讌 螻, 螳 一危磯ゼ ロ. 覃 蠏碁朱 豢 一危一 覯螳 伎蠍 覓語企. れ 企 螻殊 蟇一 伎螳 豢 蟆郁骸企.

data_compression04.jpg

蠏碁殊 豢 一危一 2BBB 覩碁 Anchor record 豌 覯讌 Column-prefix AAACCC 1~2覯讌 byte蟾讌 一危郁 螳る 覩語企. 襯 れ 豌 覯讌 貉殊 Anchor record 豌 覯讌 貉殊 螳 AAAAAAAAAAAAAACC 貊 螳 AAAAAAAAAAAAAA朱 14襦 企 10byte伎 譴 .

3 伎 豢 data-dictionary #

Data-dictionary 豢覦覯 column-prefix 豢 覦 伎, data-dictionary朱 蟆 伎 豢 煙 覦覯企. Data-dictionary Anchor-record 覦襦 れ 襷れ伎.

data_compression05.jpg

4 Overhead #

CPU るる螳 . 一危一 磯殊 豢襯 譬讌 覈 螻, CPU 襷 讀螳 朱襦 Compression/De-Compression ろ碁 .

5 #

豢襯 譟壱
EXEC sp_estimate_data_compression_savings 'ろる','企',NULL, NULL, 'PAGE'
EXEC sp_estimate_data_compression_savings 'ろる','企', NULL, NULL, 'ROW'

蟯襴
sys.dm_db_index_physical_stats 襯 覓朱Μ 覲企ゼ 視.


USE AdventureWorks2008;
GO
IF OBJECT_ID('dbo.temp') IS NOT NULL
	DROP TABLE dbo.temp;
GO

SELECT A.*
INTO dbo.temp
FROM HumanResources.Employee a, HumanResources.Employee b, HumanResources.Employee c
GO

EXEC sp_spaceused 'dbo.temp';
/*
	rows	: 24389000   
	reserved: 4386456 KB
	data	: 4386392 KB
*/

ALTER TABLE temp
REBUILD 
WITH (DATA_COMPRESSION = PAGE) 
GO

EXEC sp_spaceused 'dbo.temp';
/*
	rows	: 24389000   
	reserved: 297616 KB
	data	: 297432 KB
*/

6 企 覦 碁煙, 磯 碁煙れ 豢 #

alter table ろる覈.企覈 rebuild with (data_compression = row);
alter table ろる覈.企覈 rebuild with (data_compression = page);

alter index 碁煙る on ろる覈.企覈 rebuild with (data_compression = page);
alter index 碁煙る on ろる覈.企覈 rebuild with (data_compression = row);

create index 碁煙る on ろる覈.企覈 (producttype, productkey) 
where 貉朱 in (1,2)
with (data_compression = page);

7 DB 覈 企 覦 碁煙 豢蠍 #

蠍磯蓋朱 page 豢 覃, 磯殊 row 豢朱 襦 ..覈磯 るジ 語 tempdb.dbo.[5E47F809-DACB-4918-B9EC-14CD495E3B3A] 企 譟壱.. 豢 碁煙 覦 企襷 豢.
set statistics io off
set nocount on

if object_id('tempdb.dbo.[5E47F809-DACB-4918-B9EC-14CD495E3B3A]') is not null
	drop table tempdb.dbo.[5E47F809-DACB-4918-B9EC-14CD495E3B3A]
	
create table tempdb.dbo.[5E47F809-DACB-4918-B9EC-14CD495E3B3A]
(
	sql_str varchar(1000)
);

declare @tname varchar(500); 

declare cur cursor for
select query
from (
	select 
		'alter table ' + table_schema + '.' + table_name +  
		' rebuild with (data_compression = page, online=on)' query
	,	(select top 1 data_compression_desc
		from sys.partitions 
		where object_id = object_id(table_schema + '.' + table_name)
		and index_id = 0) compress --heap
	from information_schema.tables
	where table_name <> 'sysdiagrams'
	and table_type = 'BASE TABLE'
	union all
	select
		'alter index ' + b.name + ' on ' + 
		schema_name(a.uid) + '.' + a.name + 
		' rebuild with (data_compression = page, online=on)'
	,	(select top 1 data_compression_desc
		from sys.partitions 
		where object_id = object_id(table_schema + '.' + table_name)
                and rows >= 1000000 -- 1覦焔 蟇 伎襷..
		and index_id = b.index_id) compress 
	from sys.sysobjects a
		inner join sys.indexes b
			on a.id = b.object_id
		inner join information_schema.tables c
			on schema_name(a.uid) + '.' + a.name = table_schema + '.' + table_name
	where c.table_name <> 'sysdiagrams'
	and c.table_type = 'BASE TABLE'			
	and b.name is not null
) t	
where compress = 'NONE' --豢蟇磯 誤螻, 豢 蟇磯 豢.. 襷 磯朱..

open cur;
fetch next from cur into @tname;
while @@FETCH_STATUS not in (-1, -2)
begin
	--exec(@tname);
	print @tname;
	insert tempdb.dbo.[5E47F809-DACB-4918-B9EC-14CD495E3B3A] values(@tname);
	fetch next from cur into @tname;
end

close cur;
deallocate cur;

if object_id('tempdb.dbo.[5E47F809-DACB-4918-B9EC-14CD495E3B3A]') is not null
	drop table tempdb.dbo.[5E47F809-DACB-4918-B9EC-14CD495E3B3A]

8 豢覿 譟壱 #

一 覲企 企 碁煙 覦 一 覲企慨蠍 覓語襯 谿瑚.
select * --data_compression_desc 貉 谿瑚
from sys.partitions 
where object_id = object_id('dbo.aaa')

drop table if exists #temp
select
    concat(schema_name(schema_id), '.', name) table_name
,   b.compression_cnt
,   b.none_cnt
,   b.rows
into #temp
from sys.objects a
    cross apply (
        select 
            count(case when data_compression_desc =  'NONE' then 1 end) none_cnt
        ,   count(case when data_compression_desc <> 'NONE' then 1 end) compression_cnt
        ,   sum(rows) rows
        from sys.partitions 
        where object_id = a.object_id
    ) b
where a.type = 'u'

select * from #temp order by 1

9 一 豢 #

蠍磯蓋朱 れ螻 螳 .
ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO


ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

ろ襴渚碁 襷れ 覲伎. 企 豢.
set nocount on
set statistics io off
declare
	@bdt char(8)
,	@edt char(8)
,   @p int
,   @sql varchar(4000)

set @bdt = '20110928'

while (@bdt <= '20300301')
begin
	set @edt = convert(char(8), dateadd(dd, 1, @bdt), 112)
    set @p = $partition.一(@bdt)

    set @sql = '
    alter table 企覈
    rebuild partition = ' + convert(varchar, @p) + '
    with (data_compression = page)'
    --exec (@sql)

    print @sql
	--print @bdt + ', ' + @edt
	set @bdt = @edt
end

碁煙 豢
set nocount on
set statistics io off
declare
	@bdt char(8)
,	@edt char(8)
,   @p int
,   @sql varchar(4000)

set @bdt = '20111123'
while (@bdt <= '20121027')
begin

	set @edt = convert(char(8), dateadd(dd, 1, @bdt), 112)
    set @p = $partition.一(@bdt)

    set @sql = '
    alter index 碁煙る 
    on ろる覈.企覈
    rebuild partition = ' + convert(varchar, @p) + '
    with (data_compression = page)'
    --exec (@sql)

    print @sql
	--print @bdt + ', ' + @edt
	set @bdt = @edt
end

10 蟆磯 #

ろ瑚屋螻 朱朱 page 豢 譬 豢 . 豢 企至 讌 る. 覘.. 企郁碓 覃 覘螳 殊? 願 豢 覦覯 覦蠖 襷企. 覓企 企襴蟆 蟆る 螳 企蓋.

一危 譴覲旧 焔 覓語襯 願屋蠍 覦覯 譴 豌 願屋覦覯朱 蠏螳 . 豺 る 企 豢蠍一企 伎 讌朱 螻牛 一危 譴覲 蟇 蠍一 伎覃 蟆企. 企 襭 1覈 觸蟇 覲企 伎覃 ..

11 谿瑚襭 #


[http]SQL Server 2008 Page Compression: Performance impact on table scans(http://sqlblog.com/blogs/linchi_shea/archive/2008/05/16/sql-server-2008-page-compression-performance-impact-on-table-scans.aspx)

谿瑚襭襯 渚覃 3覦 襷 蟇伎 螻螳 企 れ 52豐 蟇碁Μ, 豢 3豐襷 . 豢 讌 2263 Page襯 曙螻, 豢 534 Page襯 曙. (.. ろ 譟磯 蠑語.. 2263 page襯 52豐? <;;)