1. 인덱스(Index)의 핵심 개념 및 구조
인덱스는 데이터베이스 테이블의 검색 속도를 비약적으로 높여주는 '책의 색인'과 같은 데이터 구조임.
- 도입 효과: 대용량 데이터 환경에서 쿼리 성능을 최대 100배 이상 개선할 수 있음.
- 작동 원리: 데이터 전체를 뒤지는 'Full Table Scan'을 방지하여 디스크 I/O(입출력) 부하를 획기적으로 감소시킴.
기본 구조: B-Tree
대부분의 SQL Server 인덱스는 검색, 삽입, 삭제에 최적화된 B-Tree(B+ Tree) 구조로 작동함.
- Root 노드: B-Tree의 최상위 계층으로, 검색이 시작되는 진입점임.
- Intermediate (중간) 노드: 키 값에 따라 데이터를 적절한 하위 노드로 안내하는 역할임.
- Leaf 노드: 최하위 계층으로, 실제 데이터가 있는 위치 포인터(비클러스터형) 또는 실제 데이터 자체(클러스터형)를 품고 있음.
2. 핵심 테이블 및 인덱스 유형 비교
학습 시 가장 헷갈리기 쉬운 주요 인덱스 및 테이블 유형의 차이점임.
| 구분 | 클러스터형 인덱스 (Clustered) | 비클러스터형 인덱스 (Non-Clustered) | 힙 테이블 (Heap) |
|---|---|---|---|
| 개념 | 데이터 자체를 인덱스 키 순서대로 물리적 정렬 | 데이터와 별개의 공간에 위치 포인터(주소)만 저장 | 인덱스가 전혀 없는 무작위 저장 테이블 |
| 생성 제한 | 테이블당 오직 1개 (보통 PK에 자동 생성) | 테이블당 최대 999개 | (인덱스 없음) |
| 강점 | 특정 범위 검색 및 정렬에 매우 강력함 | 특정 컬럼 조건의 단건 조회에 적합함 | 정렬 과정이 없어 데이터 삽입(INSERT)이 매우 빠름 |
| 단점 | 데이터 삽입/수정 시 정렬 부하 발생 | 과도하게 생성 시 삽입/수정 성능 저하 | 데이터 검색 시 무조건 전체를 스캔해야 하므로 매우 느림 |
기타 특수 인덱스
- 복합 인덱스: 2개 이상의 컬럼을 조합함 (컬럼 순서가 매우 중요).
- 커버링 인덱스: 쿼리에 필요한 모든 컬럼을 포함(
INCLUDE절 사용)해 데이터 페이지 접근 없이 결과 반환함. - 유니크 인덱스: 중복 값을 허용하지 않아 데이터 무결성을 보장함.
- 필터드 인덱스:
WHERE절을 통해 특정 조건의 행만 인덱싱하여 크기 및 유지 비용을 절감함.
3. 성능 저하의 주범: 조각화(Fragmentation)와 유지보수
잦은 데이터 변경(INSERT, UPDATE, DELETE)이 일어나면 페이지가 분할되고 빈 공간이 생기는 '조각화' 현상이 발생하여 성능이 저하됨.
조각화 수준(avg_fragmentation_in_percent)에 따라 아래와 같은 맞춤형 유지보수가 필요함.
| 조각화 수준 | 권장 조치 | 작업 특징 및 장단점 |
|---|---|---|
| 5% 미만 | 조치 불필요 | 성능 영향 미미, 모니터링 유지 |
| 10% ~ 30% | REORGANIZE (재구성) | 리프 페이지만 정렬하는 가벼운 작업. 온라인 수행 가능, 리소스 적게 소모. |
| 30% 초과 | REBUILD (재구축) | 인덱스 삭제 후 완전 새로 생성. 심각한 조각화 해결 및 통계 갱신 가능하나 리소스를 많이 소모. |
4. 성능 최적화를 위한 Best Practice 가이드
현업 개발자 및 DBA가 반드시 확인해야 할 모니터링 및 최적화 원칙임.
- 인덱스 통계 업데이트 (UPDATE STATISTICS): 통계 정보가 오래되면 옵티마이저가 비효율적인 실행 계획을 짭니다. REORGANIZE 후에는 통계가 자동 갱신되지 않으므로 반드시 수동으로 업데이트해야 함.
- 데이터 모니터링:
sys.dm_db_index_physical_stats: 조각화율 파악.sys.dm_db_index_usage_stats: 사용하지 않는 유휴 인덱스 파악 및 제거.
- FILLFACTOR(채우기 비율) 조절:
- 잦은 수정/삽입 테이블: 빈 공간을 두어 페이지 분할을 막기 위해 70~80%로 설정.
- 읽기 위주 테이블: 디스크 효율을 위해 100%에 가깝게 설정.
- 유지보수 자동화: 시스템 부하가 적은 주말/새벽 시간대에 SQL 에이전트, PowerShell 등을 통해 작업을 스케줄링함.
5. [실습] SQL Server 성능 최적화 랩
실습 준비 - SQL VM 연결
- 사용자이름:
[사용자ID] - 암호확인:
[비밀번호] - SSMS 검색 후 진입하여 데이터베이스 복원 수행.
RESTORE DATABASE AdventureWorks2017
FROM DISK = '[백업파일_경로]\AdventureWorks2017.bak'
WITH RECOVERY,
MOVE 'AdventureWorks2017' TO '[데이터파일_경로]\AdventureWorks2017.mdf',
MOVE 'AdventureWorks2017_log' TO '[로그파일_경로]\AdventureWorks2017_log.ldf';Lab 07. 조각화 문제 감지 및 수정 (Detect and Correct Fragmentation Issues)
인덱스 조각화 검사 및 유발
USE AdventureWorks2017
GO
-- 1. 조각화 50% 이상 인덱스 조회 (초기엔 결과 없음)
SELECT i.name Index_Name
, avg_fragmentation_in_percent
, db_name(database_id)
, i.object_id
, i.index_id
, index_type_desc
FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks2017'),object_id('person.address'),NULL,NULL,'DETAILED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 50;
-- 2. 대량 데이터 삽입으로 조각화 유발
INSERT INTO [Person].[Address]
([AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [SpatialLocation], [rowguid], [ModifiedDate])
SELECT AddressLine1, AddressLine2, 'Amsterdam', StateProvinceID, PostalCode, SpatialLocation, newid(), getdate()
FROM Person.Address;
GO논리적 읽기 확인 및 인덱스 재작성 (Rebuild)
조각화된 상태에서 SET STATISTICS IO, TIME ON을 켜고 SELECT 쿼리를 실행해 논리적 읽기(Logical Reads)를 기록함 (예: 94).
-- 조각난 인덱스 다시 작성
ALTER INDEX [IX_Address_StateProvinceID] ON [Person].[Address] REBUILD PARTITION = ALL
WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);재구축 후 동일한 SELECT 쿼리를 실행하면 논리적 읽기 수가 감소함(예: 94 -> 70). 이는 인덱스 재구축이 물리적 순서를 논리적 순서에 가깝게 만들고 빈 공간을 제거하여 쿼리 성능을 향상시켰음을 의미함.
Lab 08. 차단(Blocking) 문제 식별 및 해결
트랜잭션이 완료되지 않아 발생하는 블로킹(Blocking)을 시뮬레이션하고 확장 이벤트(Extended Events)로 식별함.
-- 세션 1 (차단 유발)
USE AdventureWorks2017
GO
BEGIN TRANSACTION
UPDATE Person.Person SET LastName = LastName;
-- ROLLBACK TRANSACTION (실행 보류)
-- 세션 2 (차단됨)
SELECT TOP (1000) [LastName], [FirstName], [Title]
FROM Person.Person WHERE FirstName = 'David';세션 2는 세션 1의 잠금(Lock)으로 인해 대기 상태에 놓임.
스냅샷 격리 수준 적용 (READ_COMMITTED_SNAPSHOT)
읽기 트랜잭션이 쓰기 트랜잭션을 기다리지 않고 수정 전 데이터 버전을 읽도록 하여 블로킹을 해소함.
ALTER DATABASE AdventureWorks2017 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;적용 후 차단을 유발했던 쿼리를 다시 실행해도, 세션 2는 블로킹 없이 마지막으로 커밋된 데이터 버전을 즉시 반환함.
Lab 09. 데이터베이스 설계 문제 (암시적 변환) 식별
실행 계획에서 SELECT 연산자의 경고 아이콘을 통해 암시적 변환(Implicit Conversion)을 확인함.
-- 성능 저하를 일으키는 쿼리
SELECT BusinessEntityID, NationalIDNumber, LoginID, HireDate, JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807; -- 컬럼은 NVARCHAR, 입력값은 INT데이터 타입의 불일치로 옵티마이저가 암시적으로 형 변환을 수행하며, 이로 인해 인덱스를 타지 못하고 추가적인 CPU 오버헤드가 발생함.
해결 방법
- 코드 변경: 리터럴 값을 문자열로 변경 (
WHERE NationalIDNumber = '14417807';) - 데이터 타입 변경: 설계 단계에서 올바른 타입(INT) 지정. (단, 운영 환경에서는 인덱스 재생성으로 인한 다운타임 고려 필요)
Lab 10. Key Lookup 문제 해결 및 커버링 인덱스
비클러스터형 인덱스 사용 시 인덱스에 없는 컬럼을 조회하기 위해 클러스터드 인덱스(실제 테이블)를 다시 참조하는 Key Lookup 부하를 해결함.
SET STATISTICS IO, TIME ON;
-- Key Lookup이 발생하는 쿼리
SELECT [SalesOrderID], [CarrierTrackingNumber], [OrderQty], [ProductID], [UnitPrice], [ModifiedDate]
FROM [AdventureWorks2017].[Sales].[SalesOrderDetail]
WHERE [ModifiedDate] > '2012/01/01' AND [ProductID] = 772;커버링 인덱스(Covering Index) 생성
쿼리에 필요한 모든 컬럼을 인덱스의 INCLUDE 절에 포함시켜 테이블 데이터 페이지 접근(Key Lookup)을 아예 제거함.
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID]
ON [Sales].[SalesOrderDetail] ([ProductID], [ModifiedDate])
INCLUDE ([CarrierTrackingNumber], [OrderQty], [UnitPrice])
WITH (DROP_EXISTING = on);
GO재실행 시 실행 계획에서 Key Lookup이 사라지고 Nonclustered Index Seek만 발생하여 논리적 읽기와 I/O 비용이 대폭 감소함.
쿼리 저장소(Query Store) 및 쿼리 힌트(Query Hint) 활용
쿼리 저장소로 실행 계획 강제 (Forcing a Plan)
옵티마이저가 비효율적인 실행 계획을 선택할 경우, 쿼리 저장소(Query Store)의 'Top Resource Consuming Queries' 보고서를 통해 기간(Duration)이 짧고 효율적인 특정 PlanID를 찾아 강제(Force Plan)할 수 있음.
쿼리 힌트 적용 OPTION (RECOMPILE)
변수를 사용하는 쿼리의 경우, 컴파일 시점에 변수 값을 알 수 없어 부정확한 통계를 기반으로 실행 계획(예: Index Scan)을 짤 수 있음.
DECLARE @SalesPersonID INT;
SELECT @SalesPersonID = 288;
SELECT SalesOrderId, OrderDate
FROM Sales.SalesOrderHeader
WHERE SalesPersonID= @SalesPersonID
OPTION (RECOMPILE); -- 힌트 적용OPTION (RECOMPILE) 힌트를 부여하면 쿼리 실행 직전에 매번 새롭게 컴파일을 수행함. 이 때 할당된 지역 변수 값(288)을 인지하고 이에 맞는 최적의 실행 계획(Index Seek)을 도출하게 됨. 단점으로는 잦은 재컴파일 부하가 발생할 수 있으므로 상황에 맞게 적용해야 함.
