본문 바로가기
카테고리 없음

MS-SQL 2008 R2 데이터 최적화 방법

wonquick 2025. 3. 18. 12:57

MS-SQL 2008 R2 데이터 최적화 방법

MS-SQL 2008 R2

오래된 데이타 테이블 최적화 방법

🔹 1단계: 전체 테이블 데이터 정리 (아카이빙 및 불필요한 데이터 삭제)
📌 오래된 데이터를 분리하여 테이블 크기를 줄이기

1️⃣ 오래된 데이터가 많은 테이블 찾기
아래 SQL을 실행하면 **각 테이블의 데이터 개수(행 수)**를 확인할 수 있습니다.

SELECT t.name AS TableName, i.rows AS RowCounts
FROM sys.tables AS t
JOIN sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
ORDER BY i.rows DESC;

👉 데이터 개수가 많고 오래된 테이블을 우선 정리해야 합니다.

2️⃣ 과거 데이터를 별도 테이블로 이동 (아카이빙)
예제: 10년 이상된 데이터를 아카이빙 테이블로 이동

SELECT * INTO sales_archive
FROM sales
WHERE order_date < '2014-01-01';

DELETE FROM sales
WHERE order_date < '2014-01-01';

✅ 효과
운영 테이블에서는 최근 데이터만 유지 → 속도 향상
과거 데이터는 별도 보관하여 필요할 때만 조회

 

🔹 2단계: 전체 테이블 인덱스 최적화
📌 30개 이상의 테이블을 하나씩 최적화하기 어렵기 때문에, 자동화 스크립트로 최적화 진행

1️⃣ 인덱스 단편화(조각화) 확인하기
SQL Server는 시간이 지나면 인덱스가 단편화되어 쿼리 속도가 느려질 수 있음

SELECT dbschemas.[name] AS 'Schema', 
       dbtables.[name] AS 'Table', 
       dbindexes.[name] AS 'Index',
       indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS indexstats
JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] 
   AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

👉 avg_fragmentation_in_percent 값이 30% 이상이면 인덱스를 재구성하는 것이 좋음

2️⃣ 전체 테이블의 인덱스 재구성 (자동화)

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD';

✅ 효과
조각난 인덱스를 최적화하여 검색 속도를 높임
자동으로 모든 테이블에 적용되므로, 30개 이상의 테이블도 효율적으로 관리 가능

 

🔹 3단계: 자주 실행하는 쿼리 최적화
📌 어떤 쿼리가 가장 느린지 확인하고 최적화
SQL Server에서 sys.dm_exec_query_stats를 사용하면 실행 속도가 느린 쿼리를 찾을 수 있음

1️⃣ 가장 오래 걸리는 쿼리 찾기

SELECT TOP 10 
    total_worker_time/execution_count AS Avg_CPU_Time, 
    total_elapsed_time/execution_count AS Avg_Run_Time, 
    execution_count, 
    text AS QueryText
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY Avg_Run_Time DESC;

👉 Avg_Run_Time 값이 높은 쿼리를 최적화해야 함

2️⃣ 쿼리 실행 계획 확인 및 인덱스 추가 실행 계획을 분석하여 INDEX SCAN이 발생하는 경우 INDEX SEEK로 변경해야 함

SET SHOWPLAN_XML ON;
EXEC sp_executesql N'SELECT * FROM sales WHERE order_date > ''2023-01-01''';
SET SHOWPLAN_XML OFF;

 

INDEX SCAN → 해당 칼럼에 적절한 인덱스 추가

CREATE INDEX idx_sales_order_date ON sales(order_date);

 

✅ 효과
가장 느린 쿼리를 최적화하여 전체적인 응답 속도 개선
불필요한 INDEX SCAN을 제거하여 성능 향상

 

🔹 4단계: 유지보수 자동화 (주기적 최적화 작업)
📌 30개 이상의 테이블을 효율적으로 유지관리하려면 자동화 작업을 추가해야 함

✅ 1️⃣ 매주 자동 인덱스 재구성
SQL Server Agent에서 자동화 스케줄링 설정

CREATE PROCEDURE dbo.RebuildIndexes
AS
BEGIN
    EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD';
END;
GO
EXEC dbo.RebuildIndexes;

👉 매주 실행되도록 예약하면, 자동으로 인덱스를 최적화

✅ 2️⃣ 매일 자동 통계 업데이트

EXEC sp_MSForEachTable 'UPDATE STATISTICS ?';

👉 최신 데이터에 맞게 실행 계획을 업데이트하여 성능 유지

✅ 3️⃣ 매일 자동 백업 실행

BACKUP DATABASE mydb TO DISK = 'C:\Backup\mydb_' + CONVERT(VARCHAR, GETDATE(), 112) + '.bak' WITH FORMAT, INIT;

 

👉 데이터 손실 방지를 위해 자동 백업 설정

 

💡 최적화 프로세스 요약

단계 작업 내용 SQL 코드
1단계 오래된 데이터 아카이빙 SELECT * INTO archive_table FROM sales WHERE order_date < '2014-01-01';
2단계 전체 인덱스 재구성 EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD';
3단계 느린 쿼리 최적화 SELECT TOP 10 total_elapsed_time/execution_count AS Avg_Run_Time, text FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle);
4단계 유지보수 자동화 EXEC sp_MSForEachTable 'UPDATE STATISTICS ?';
5단계 자동 백업 설정 BACKUP DATABASE mydb TO DISK = 'C:\Backup\mydb_' + CONVERT(VARCHAR, GETDATE(), 112) + '.bak';

 

🔹 결론
✅ 30개 이상의 테이블을 효율적으로 최적화하려면

데이터 정리: 오래된 데이터를 별도 테이블로 이동 (아카이빙)
인덱스 최적화: 모든 테이블의 인덱스를 자동으로 재구성
쿼리 최적화: 느린 쿼리를 찾아 실행 계획 분석 및 인덱스 추가
유지보수 자동화: 인덱스 최적화, 통계 업데이트, 자동 백업을 예약 실행
✅ 이 방법을 적용하면

운영 속도 향상
DB 부하 감소
관리 비용 절감

티스토리툴바