SQL Server 2008 襦蟆 覲伎 蠍磯レ企.
2 伎 豢 column-prefix #
SQL Server column-prefix data襯 襦 貊襯 燕. 貊襯 anchor-record 手 覿襯碁. Anchor-recode 伎 る れ ル. 襷 Common bytepattern 覦蟆讌 覈詩覃 empty null襦 豬蠍. 襷 column-prefix襯 螳讌讌 る Anchor-record 焔讌 . れ 蠏碁手骸 螳 伎 貊れ ル .
SQL Server 螳螳 貉殊 貊襯 所, Common bytepattern 襭一 伎 Column-prefix襯 谿城.
SQL Server Page Header 覦襦 れ Anchor-Record襯 襷.
SQL Server Anhcor record襯 るジ 貊襦 豬蠍. 蠏碁覩襦 SELECT 貎朱Μ襦 譟壱螳 覿螳ロ. SQL Server Anchor-record Column-prefix襷 ロ讌 螻, 螳 一危磯ゼ ロ. 覃 蠏碁朱 豢 一危一 覯螳 伎蠍 覓語企. れ 企 螻殊 蟇一 伎螳 豢 蟆郁骸企.
蠏碁殊 豢 一危一 2BBB 覩碁 Anchor record 豌 覯讌 Column-prefix AAACCC 1~2覯讌 byte蟾讌 一危郁 螳る 覩語企. 襯 れ 豌 覯讌 貉殊 Anchor record 豌 覯讌 貉殊 螳 AAAAAAAAAAAAAACC 貊 螳 AAAAAAAAAAAAAA朱 14襦 企 10byte伎 譴 .
豢襯 譟壱
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
*/
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
ろ瑚屋螻 朱朱 page 豢 譬 豢 . 豢 企至 讌 る. 覘.. 企郁碓 覃 覘螳 殊? 願 豢 覦覯 覦蠖 襷企. 覓企 企襴蟆 蟆る 螳 企蓋.
一危 譴覲旧 焔 覓語襯 願屋蠍 覦覯 譴 豌 願屋覦覯朱 蠏螳 . 豺 る 企 豢蠍一企 伎 讌朱 螻牛 一危 譴覲 蟇 蠍一 伎覃 蟆企. 企 襭 1覈 觸蟇 覲企 伎覃 ..
![[http]](/moniwiki/imgs/http.png)