SQL Server 성능 진단부터 튜닝을 통한 향상까지: 단계별 실전 가이드

1. 시스템 환경 진단

1.1 SQL Server 버전 및 서비스팩 관리

현재 버전 확인 방법

SELECT @@VERSION

주요 확인사항

• SQL Server 에디션별 메모리 제한
  • Standard Edition: 최대 2GB (SQL Server 2000)
  • Standard Edition: 최대 32GB (SQL Server 2005 이상)
  • Enterprise Edition: OS 최대 메모리까지 지원

권장사항

• 최신 서비스팩과 누적 업데이트 적용
• 테스트 환경에서 충분한 검증 후 운영 환경 적용
• 에디션 업그레이드 시 라이선스 정책 확인 필수

1.2 하드웨어 구성 진단

디스크 여유공간 확인

EXEC master..xp_fixeddrives

권장 디스크 구성

• 시스템 파일: C 드라이브
• 데이터 파일(.mdf, .ndf): 별도 드라이브
• 로그 파일(.ldf): 별도 드라이브
• 백업 파일: 별도 드라이브

RAID 구성 권장사항

• RAID 1+0: 성능과 안정성 모두 확보
• 단일 디스크 사용 지양 (장애 발생 시 전체 데이터 손실 위험)

2. 성능 모니터링 기준값

2.1 CPU 성능 지표

항목	정상 범위	비정상 시 대책
% Processor Time	80% 이하	프로세서 추가/업그레이드
% Privileged Time	80% 이하	시스템 프로세스 점검
% User Time	80% 이하	애플리케이션 최적화
Processor Queue Length	2 이하	CPU 병목 해결 필요

2.2 메모리 성능 지표

항목	정상 범위	비정상 시 대책
Available Bytes	4MB 이상	메모리 추가
Pages/sec	0~20	페이징 감소 방안 마련
Page Reads/sec	5 이하	메모리 부족 해결
Buffer Cache Hit Ratio	90% 이상	메모리 추가 또는 쿼리 튜닝

2.3 디스크 성능 지표

항목	정상 범위	비정상 시 대책
% Disk Read Time	40% 이하	디스크 추가/RAID 구성
% Disk Write Time	40% 이하	쓰기 작업 분산
Avg. Disk Queue Length	2 이하	디스크 I/O 최적화

3. 데이터베이스 복구 모델

3.1 복구 모델별 특징

SIMPLE (단순 복구 모델)

• 트랜잭션 로그 자동 truncate
• 시점 복구 불가능
• 마지막 전체 백업 시점으로만 복구 가능

FULL (전체 복구 모델)

• 모든 트랜잭션 로그 보관
• 특정 시점으로 복구 가능
• 주기적인 로그 백업 필수

BULK_LOGGED (대량 로그 복구 모델)

• 대량 작업 시 최소 로깅
• 대량 작업이 없으면 FULL과 동일

3.2 복구 모델 설정 예시

-- 복구 모델 확인
SELECT name, recovery_model_desc 
FROM sys.databases

-- 복구 모델 변경
ALTER DATABASE [DatabaseName] 
SET RECOVERY FULL

4. 쿼리 성능 분석

4.1 악성 쿼리 식별 기준

논리적 읽기 기준

• 1,000 페이지 이상: 튜닝 검토 대상
• 10,000 페이지 이상: 즉시 튜닝 필요
• 100,000 페이지 이상: 긴급 조치 필요

4.2 쿼리 분석 도구 활용

SQL Server Profiler 사용

• Duration, CPU, Reads 기준 정렬
• 반복 실행되는 쿼리 우선 분석
• 실행 계획 수집 및 분석

동적 관리 뷰(DMV) 활용

-- CPU 사용량 상위 쿼리
SELECT TOP 10
    total_worker_time/execution_count AS avg_cpu_time,
    total_logical_reads/execution_count AS avg_logical_reads,
    execution_count,
    SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1 
            THEN LEN(CONVERT(nvarchar(max), text)) * 2
            ELSE statement_end_offset
        END - statement_start_offset)/2) AS query_text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avg_cpu_time DESC

5. 쿼리 튜닝 실전 사례

5.1 인덱스 활용 방해 요인

사례 1: 컬럼 가공으로 인한 인덱스 미사용

❌ 잘못된 예

SELECT * FROM HOM_MEM_USER 
WHERE REPLACE(USER_ID, ' ', '') = 'bpm065'

✅ 개선된 예

SELECT * FROM HOM_MEM_USER 
WHERE USER_ID = 'bpm065'

개선 효과: 140,000 페이지 → 1,000 페이지 미만 (140배 성능 향상)

 

사례 2: 날짜 컬럼 가공

❌ 잘못된 예

WHERE LEFT(COUNSEL_DATE, 6) = '200706'
WHERE SUBSTRING(RECEIPT_DATE, 1, 6) = '200706'

✅ 개선된 예

WHERE COUNSEL_DATE LIKE '200706%'
WHERE RECEIPT_DATE >= '20070601' AND RECEIPT_DATE < '20070701'

5.2 잠금 문제 해결

격리 수준 조정

-- 읽기 작업에서 잠금 회피
SELECT * FROM TableName WITH (NOLOCK)
-- 또는
SELECT * FROM TableName WITH (READUNCOMMITTED)

주의사항

• 더티 리드 가능성 고려
• 중요한 트랜잭션에서는 사용 자제

5.3 저장 프로시저 활용

Ad-hoc 쿼리를 저장 프로시저로 변환

변환 전

-- 애플리케이션에서 직접 실행
SELECT * FROM Orders 
WHERE OrderDate = @OrderDate AND CustomerID = @CustomerID

변환 후

CREATE PROCEDURE sp_GetOrders
    @OrderDate datetime,
    @CustomerID int
AS
BEGIN
    SELECT * FROM Orders 
    WHERE OrderDate = @OrderDate AND CustomerID = @CustomerID
END

장점

• 실행 계획 재사용
• 컴파일 시간 절약
• 네트워크 트래픽 감소

6. 인덱스 전략

6.1 인덱스 생성 가이드라인

기본 원칙

-- 자주 검색되는 컬럼에 인덱스 생성
CREATE INDEX IX_USER_SEQ ON HOM_MEM_USER(USER_SEQ)

-- 복합 인덱스는 선택도가 높은 컬럼을 앞에 배치
CREATE INDEX IX_OrderDate_CustomerID 
ON Orders(OrderDate, CustomerID)

6.2 인덱스 관리

인덱스 단편화 확인

SELECT 
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 30

인덱스 재구성

-- 단편화 30% 이하: REORGANIZE
ALTER INDEX IX_IndexName ON TableName REORGANIZE

-- 단편화 30% 초과: REBUILD
ALTER INDEX IX_IndexName ON TableName REBUILD

7. 데이터베이스 유지보수

7.1 통계 업데이트

-- 특정 테이블 통계 업데이트
UPDATE STATISTICS TableName

-- 전체 데이터베이스 통계 업데이트
EXEC sp_updatestats

7.2 정기 유지보수 계획

주간 작업

• 인덱스 단편화 확인 및 정리
• 통계 업데이트
• 전체 백업

일일 작업

• 차등 백업
• 트랜잭션 로그 백업 (전체 복구 모델인 경우)
• 성능 모니터링 로그 확인

8. 문제 해결 체크리스트

8.1 성능 저하 시 확인 사항

1. 즉시 확인
   • 현재 실행 중인 쿼리 확인
   • 차단(Blocking) 상황 확인
   • CPU/메모리/디스크 사용률 확인
2. 상세 분석
   • 악성 쿼리 식별
   • 실행 계획 분석
   • 인덱스 사용 여부 확인
3. 조치 사항
   • 악성 쿼리 중단
   • 긴급 인덱스 생성
   • 통계 업데이트

8.2 데드락 해결

데드락 추적 설정

-- 데드락 추적 활성화
DBCC TRACEON(1204, -1)

-- 추적 비활성화
DBCC TRACEOFF(1204, -1)

예방 방법

• 트랜잭션 최소화
• 테이블 접근 순서 통일
• 적절한 인덱스 생성

 

SQL Server 성능 최적화는 하드웨어 업그레이드보다 쿼리 튜닝이 우선되어야 합니다. 특히 소수의 악성 쿼리가 전체 시스템 성능을 좌우하는 경우가 많으므로, 정기적인 모니터링과 분석을 통해 문제를 조기에 발견하고 해결하는 것이 중요합니다.

 

주요 개선 포인트

1. 컬럼 가공 없는 쿼리 작성
2. 적절한 인덱스 전략 수립
3. 정기적인 통계 업데이트
4. 복구 모델 선택과 백업 전략
5. 지속적인 성능 모니터링

이러한 기본 원칙을 준수하면 대부분의 성능 문제를 예방하고 해결할 수 있습니다.