MSSQL 압축 스크립트와 예제 및 파티션별 압축 자동화 방법

✅ 1. 파티션별 압축 기본 스크립트 예제

🎯 목표: 특정 파티션만 PAGE 압축하기

ALTER TABLE dbo.YourTableName
REBUILD PARTITION = 5  -- 5번 파티션
WITH (DATA_COMPRESSION = PAGE);

📌 파티션 번호는 파티션 함수의 정의에 따라 다름. 아래에서 확인하는 방법 설명합니다.

---

✅ 2. 파티션 정보 조회

▶ 파티션 번호와 범위 확인 스크립트:

SELECT 
    ps.partition_number,
    prv.value AS RangeValue,
    p.rows AS [RowCount]
FROM 
    sys.partitions p
    JOIN sys.objects o ON p.object_id = o.object_id
    JOIN sys.indexes i ON i.object_id = o.object_id AND p.index_id = i.index_id
    JOIN sys.partition_schemes pscheme ON i.data_space_id = pscheme.data_space_id
    JOIN sys.partition_functions pf ON pscheme.function_id = pf.function_id
    LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id
    JOIN sys.partition_stats ps ON ps.object_id = p.object_id AND ps.index_id = p.index_id AND ps.partition_number = p.partition_number
WHERE 
    o.name = 'YourTableName'
    AND i.index_id IN (0,1)  -- Heap 또는 클러스터드 인덱스
ORDER BY 
    ps.partition_number;

🔎 partition_number를 확인 후, 해당 번호에 대해 압축을 수행할 수 있습니다.

---

✅ 3. 특정 기간(예: 지난달) 파티션만 압축하기

▶ 예: 파티션이 월 단위일 때, 지난달 데이터 압축

DECLARE @partition_number INT = 5;  -- 예: 5번 파티션이 지난달

ALTER TABLE dbo.YourTableName
REBUILD PARTITION = @partition_number
WITH (DATA_COMPRESSION = PAGE);

---

✅ 4. 자동화: 파티션별 압축 스크립트 동적 생성 및 실행

▶ 모든 파티션 대상 압축 스크립트 자동 생성

DECLARE @TableName NVARCHAR(128) = 'YourTableName';
DECLARE @SQL NVARCHAR(MAX) = '';

SELECT 
    @SQL += '
    ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + '
    REBUILD PARTITION = ' + CAST(p.partition_number AS VARCHAR) + '
    WITH (DATA_COMPRESSION = PAGE);
    '
FROM 
    sys.partitions p
    JOIN sys.objects o ON p.object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE 
    o.name = @TableName
    AND p.index_id IN (0,1)
GROUP BY 
    s.name, o.name, p.partition_number;

-- 출력 또는 실행
PRINT @SQL;
-- EXEC sp_executesql @SQL; -- 실제 적용 시 사용

🔄 **스케줄러(SQL Agent)**를 통해 정기적으로 이 스크립트를 실행하면 자동화 가능

---

✅ 5. 자동화 전략 제안 (실무적 접근)

📌 파티션 스위칭 + 압축 (예: 월말 자동 작업)

1. 신규 데이터 → 압축 없는 파티션 테이블 (스테이징 테이블) 에 저장
2. 월말 스케줄러:
   • 해당 월 파티션 압축
   • 파티션 스위칭하여 메인 테이블로 이동

예제:

-- 1. 압축된 테이블 준비 (구조 동일, 압축 적용)
CREATE TABLE dbo.ArchivePartition (
    ...
) ON ps_MonthlyPartitionScheme(MonthColumn);

-- 압축
ALTER TABLE dbo.ArchivePartition 
REBUILD PARTITION = 5 WITH (DATA_COMPRESSION = PAGE);

-- 2. 스위칭
ALTER TABLE dbo.YourTableName SWITCH PARTITION 5 
TO dbo.ArchivePartition PARTITION 5;

---

⚠️ 주의사항

• 압축은 CPU 비용이 큼: 가능하면 업무 시간 외 실행
• 클러스터드 인덱스가 있어야 REBUILD PARTITION 가능
• sp_estimate_data_compression_savings로 압축 효과 미리 분석 권장

---

🧪 압축 효과 미리 보기

EXEC sp_estimate_data_compression_savings  
    @schema_name = 'dbo',
    @object_name = 'YourTableName',  
    @index_id = 1,  -- 클러스터드 인덱스 ID
    @partition_number = 5,  
    @data_compression = 'PAGE';  

---