use tableDB
go
------------------------
-- TABLE 생성
------------------------
create table dbo.noCompress
(
txt char(100)
);
go
create table dbo.Compress
(
txt char(100)
) with ( data_compression=row) ;
go
SET NOCOUNT ON;
------------------------
-- SampleData Insert
------------------------
DECLARE @i INT = 0;
WHILE @i < 40000
BEGIN
INSERTINTO dbo.noCompressvalues('ABC');
INSERTINTO dbo.Compressvalues('ABC');
SET@i+=1;
END
------------------------
-- 공간절약얼마나??
------------------------
exec sp_spaceused 'noCompress'
go
exec sp_spaceused 'Compress'
go
--name rows reserved data index_size unused
--noCompress 40000 4552 KB 4512 KB 8KB 32 KB
--Compress 40000 520 KB 464 KB 8KB 48 KB
------------------------
-- I/O를얼마나줄이느냐??
------------------------
set statistics io on
go
select top 100 *
from noCompress
select top 100 *
from Compress
go
-- 우와,, 많이절약되는구낭
--테이블'noCompress'. 검색수1, 논리적읽기수2, 물리적읽기수0,미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.
--테이블'Compress'. 검색수1, 논리적읽기수1, 물리적읽기수0,미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.
--go
------------------------
-- 압축을하였을경우얼마나절약할수있는지예측프로시져
------------------------
exec sp_estimate_data_compression_savings'dbo', 'noCompress', null, null, 'row';
go
--object_name schema_name index_id partition_number size_with_current_compression_setting(KB) size_with_requested_compression_setting(KB) sample_size_with_current_compression_setting(KB) sample_size_with_requested_compression_setting(KB)
--noCompress dbo 0 1 4520 472 4344 456
--------------------------
--nocompress table 행압축시도
--------------------------
ALTER TABLE dbo.noCompress
REBUILDWITH(DATA_COMPRESSION=ROW);
GO
exec sp_spaceused 'noCompress'
go
--noCompress_int 40000 1160KB 1128 KB 8KB 24 KB
--Compress_int 40000 904 KB 880 KB 8KB 16 KB
'SQL Server 2008' 카테고리의 다른 글
SQL2008의 새로운 기능 - 필터링된 인덱스 (0) | 2010.01.13 |
---|---|
How to Read the Results of SELECT @@VERSION in SQL Server (0) | 2010.01.13 |
DB Logical Rename (2005, 2008...) (0) | 2010.01.06 |
트랜잭션 로그 파일 없이 복구하기 (0) | 2009.12.18 |
DATABASE 생선 옵션 (0) | 2009.12.18 |