[MicrosoftDataSchool] 82일차 - Azure SQL Index

RudinP·약 19시간 전

AzureSQL MicrosoftDataSchool

Microsoft Data School 3기

목록 보기

63/64

SQL Server 성능 튜닝 실습 정리 - 인덱스, 실행 계획, Blocking, Query Store

SQL Server 성능 튜닝을 제대로 이해하려면 가장 먼저 알아야 하는 것이 바로 인덱스(Index) 이다.

이번 실습에서도:

Fragmentation
Key Lookup
Execution Plan
Query Store
Blocking

같은 다양한 성능 이슈를 다뤘지만, 결국 대부분의 문제는 인덱스와 연결되어 있었다.

따라서 본격적인 실습 내용을 보기 전에, 먼저 SQL Server 인덱스 개념을 정리하고 시작한다.

인덱스(Index)란?

인덱스는 데이터를 빠르게 찾기 위한 자료구조이다.

책에서 원하는 내용을 찾을 때:

책 전체를 처음부터 끝까지 읽지 않고
맨 뒤의 색인(Index)을 먼저 보는 것과 비슷하다.

SQL Server에서도 인덱스가 없다면 원하는 데이터를 찾기 위해 테이블 전체를 읽어야 한다.

이를 Table Scan이라고 한다.

데이터 양이 적을 때는 큰 문제가 없지만, 데이터가 수백만 건 이상으로 증가하면 성능 차이가 매우 커진다.

인덱스가 필요한 이유

1. 검색 속도 향상

인덱스는 원하는 데이터 위치를 빠르게 찾을 수 있게 해준다.

예를 들어:

SELECT *
FROM Users
WHERE Email = 'test@test.com'

같은 쿼리에서 Email 컬럼에 인덱스가 존재하면 SQL Server는 전체 데이터를 읽지 않고 필요한 데이터만 바로 찾을 수 있다.

2. 디스크 I/O 감소

인덱스가 없으면 SQL Server는 테이블 전체를 읽는다.

즉:

더 많은 페이지 읽기
더 많은 디스크 접근
더 많은 메모리 사용

이 발생한다.

인덱스를 사용하면 필요한 데이터만 읽기 때문에 I/O가 감소한다.

3. 대규모 시스템 필수 요소

실제 운영 환경에서는:

수많은 사용자
동시 요청
대량 데이터

를 처리해야 한다.

인덱스 없이 운영하면:

응답 속도 저하
CPU 사용량 증가
서버 부하 증가

가 발생한다.

따라서 인덱스는 단순 최적화가 아니라 필수 요소에 가깝다.

SQL Server의 인덱스 구조

SQL Server는 대부분 B-Tree(B+Tree) 구조를 사용한다.

구조는 다음과 같다.

구조	역할
Root Node	검색 시작점
Intermediate Node	중간 탐색
Leaf Node	실제 데이터 또는 데이터 위치 저장

B-Tree 구조 동작 방식

예를 들어 다음과 같은 데이터가 있다고 가정한다.

1, 5, 10, 20, 50, 100

SQL Server는 이 값을 트리 형태로 정렬하여 저장한다.

검색 시에는:

Root Node 탐색
중간 노드 이동
Leaf Node 도달

과정을 거친다.

즉:

전체 데이터를 순차 탐색하지 않아도 된다.

이것이 인덱스가 빠른 이유이다.

Clustered Index

Clustered Index는 실제 데이터 자체가 정렬된다.

즉:

데이터 저장 순서
인덱스 순서

가 동일하다.

특징

특징	설명
테이블당 1개만 가능	실제 데이터 순서는 하나만 존재 가능
범위 검색에 강함	BETWEEN, ORDER BY 최적화
Primary Key에 자주 사용	기본 키 생성 시 자동 생성되는 경우 많음

Clustered Index 예시

CREATE CLUSTERED INDEX IX_Users_Id
ON Users(UserId);

이 경우 Users 테이블 데이터 자체가 UserId 기준으로 정렬된다.

Nonclustered Index

Nonclustered Index는 실제 데이터와 별도 구조로 존재한다.

인덱스에는:

키 값
데이터 위치 포인터

만 저장된다.

구조 특징

구조	설명
별도 인덱스 구조 존재	데이터와 분리
Leaf Node	실제 데이터 위치 저장
여러 개 생성 가능	최대 999개

Nonclustered Index 예시

CREATE NONCLUSTERED INDEX IX_Users_Email
ON Users(Email);

이 경우 Email 기반 검색이 빨라진다.

Index Seek vs Table Scan

실행 계획에서 가장 중요하게 보는 것 중 하나가:

Index Seek
Table Scan

이다.

Table Scan

Table Scan은 테이블 전체를 읽는다.

즉:

1행부터 끝까지 전부 읽음

데이터가 많을수록 매우 느려진다.

Index Seek

Index Seek는 필요한 데이터만 찾는다.

즉:

원하는 위치만 바로 접근

따라서 훨씬 빠르다.

실행 계획에서 확인 가능

실행 계획(Execution Plan)에서:

Table Scan 발생 여부
Index Seek 사용 여부

를 확인할 수 있다.

튜닝에서 가장 기본적으로 보는 부분이다.

Covering Index

Covering Index는 쿼리에 필요한 모든 컬럼이 인덱스에 포함된 경우를 말한다.

즉:

추가 테이블 접근 없이
인덱스만 읽어서
결과 반환 가능

하다.

INCLUDE 사용

CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId)
INCLUDE (OrderDate, TotalAmount);

장점

장점	설명
Key Lookup 제거	추가 테이블 접근 감소
Logical Read 감소	성능 향상
실행 계획 단순화	I/O 절약

Key Lookup

Key Lookup은 SQL Server가:

인덱스로 위치 찾고
원본 데이터 다시 접근

하는 작업이다.

즉:

인덱스만으로 필요한 컬럼이 부족함

을 의미한다.

왜 문제인가?

Key Lookup은:

랜덤 I/O 증가
페이지 접근 증가
성능 저하

를 유발한다.

특히 결과 건수가 많을수록 심각해진다.

Fragmentation(조각화)

데이터가 지속적으로 변경되면 인덱스 페이지 순서가 깨진다.

이를 Fragmentation이라고 한다.

조각화 발생 원인

작업	영향
INSERT	페이지 분할 발생
UPDATE	데이터 이동 발생
DELETE	빈 공간 증가

조각화 문제점

문제	설명
디스크 I/O 증가	페이지 순서 깨짐
Logical Read 증가	더 많은 페이지 읽기
성능 저하	응답 속도 감소

Fragmentation 확인

SQL Server에서는 다음 DMV로 확인 가능하다.

sys.dm_db_index_physical_stats

REORGANIZE vs REBUILD

조각화 해결 방법은 크게 2가지이다.

구분	REORGANIZE	REBUILD
방식	페이지 재정렬	인덱스 새 생성
부하	낮음	높음
통계 갱신	X	O
추천 구간	10~30%	30% 이상

실행 계획(Execution Plan)

SQL Server는 쿼리를 실행하기 전에:

“어떻게 실행하는 것이 가장 효율적인가”

를 계산한다.

이 결과가 실행 계획이다.

실행 계획에서 보는 주요 요소

요소	의미
Index Seek	효율적 검색
Index Scan	인덱스 전체 탐색
Table Scan	테이블 전체 탐색
Key Lookup	원본 데이터 재접근
Hash Match	해시 기반 조인
Sort	정렬 작업

Query Optimizer

실행 계획은 Query Optimizer가 생성한다.

Optimizer는:

인덱스
통계 정보
데이터 분포
비용 계산

을 기반으로 가장 효율적이라고 판단한 플랜을 선택한다.

하지만 항상 완벽하지는 않다.

따라서:

잘못된 실행 계획
비효율 플랜
Parameter Sniffing

같은 문제도 발생한다.

Query Store

SQL Server의 Query Store는:

실행 계획 저장
느린 쿼리 분석
성능 Regression 추적

기능을 제공한다.

특히:

이전보다 느려진 쿼리
플랜 변경
강제 플랜 적용(Force Plan)

분석에 매우 유용하다.

Blocking

Blocking은 트랜잭션이 서로 Lock을 기다리는 현상이다.

예를 들어:

세션 A가 UPDATE 수행
COMMIT 안 함
세션 B가 같은 데이터 읽기 시도

하면 세션 B는 대기 상태가 된다.

Snapshot Isolation

Blocking 완화를 위해 SQL Server는 Snapshot 기반 격리 수준을 제공한다.

대표적으로:

READ_COMMITTED_SNAPSHOT

이 있다.

핵심 개념

기존 Read Committed:

Lock 기다림

Snapshot 기반:

이전 버전(Row Version) 읽음

즉:

Reader와 Writer 충돌 감소

효과가 있다.

이번 실습에서 중요했던 핵심 포인트

이번 실습의 핵심은 단순 SQL 문법이 아니었다.

진짜 중요한 것은:

왜 실행 계획이 그렇게 나오는가
왜 인덱스가 필요한가
왜 Key Lookup이 성능 병목이 되는가
왜 Fragmentation이 Logical Read를 증가시키는가
왜 Blocking이 발생하는가

를 SQL Server 내부 동작 관점에서 이해하는 것이었다.

Lab07 - Fragmentation 실습

이번 실습에서는 조각화를 의도적으로 발생시켰다.

1. 조각화 상태 확인

USE AdventureWorks2017
GO

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

USE AdventureWorks2017
GO

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

이 작업으로 Person.Address 테이블 크기가 증가하며 페이지 분할(Page Split)이 발생한다.

결과적으로 인덱스 조각화율이 크게 증가한다.

3. 논리적 읽기(Logical Read) 측정

SET STATISTICS IO,TIME ON
GO

USE AdventureWorks2017
GO

SELECT DISTINCT (StateProvinceID)
,count(StateProvinceID) AS CustomerCount
FROM person.Address
GROUP BY StateProvinceID
ORDER BY count(StateProvinceID) DESC;
GO

메시지 탭에서 Logical Read 값을 확인할 수 있다.

조각화가 심해질수록 더 많은 페이지를 읽게 된다.

인덱스 REBUILD

조각화 문제 해결을 위해 REBUILD를 수행한다.

USE AdventureWorks2017
GO

ALTER INDEX [IX_Address_StateProvinceID]
ON [Person].[Address]
REBUILD PARTITION = ALL

REBUILD는 인덱스를 새로 만드는 작업이다.

결과:

Fragmentation 감소
Logical Read 감소
쿼리 성능 향상

REORGANIZE vs REBUILD

구분	REORGANIZE	REBUILD
방식	페이지 재정렬	인덱스 새 생성
부하	낮음	높음
통계 갱신	X	O
권장 구간	10~30%	30% 이상

실행 계획(Execution Plan)

SQL Server는 쿼리를 실행하기 전에 “어떻게 실행할지” 계획을 만든다.

이것이 Execution Plan이다.

실행 계획을 통해:

Index Seek
Table Scan
Key Lookup
Hash Match

등이 어떻게 발생하는지 확인할 수 있다.

Lab09 - 암시적 형 변환(Implicit Conversion)

다음 쿼리를 실행했다.

SELECT BusinessEntityID,
       NationalIDNumber,
       LoginID,
       HireDate,
       JobTitle
FROM HumanResources.Employee
WHERE NationalIDNumber = 14417807;

실행 계획에서 경고가 발생했다.

원인은 Implicit Conversion이다.

왜 발생했는가?

NationalIDNumber 컬럼 타입은:

nvarchar(15)

이다.

하지만 비교값은 숫자(INT)이다.

따라서 SQL Server가 내부적으로:

CONVERT_IMPLICIT(...)

를 수행한다.

문제점

Implicit Conversion은 다음 문제를 만든다.

문제	설명
CPU 증가	형 변환 연산 발생
인덱스 비효율	Seek 최적화 방해
실행 계획 악화	Optimizer가 비효율 플랜 선택

해결 방법 1 - 쿼리 수정

WHERE NationalIDNumber = '14417807';

문자열로 맞춰주면 된다.

이렇게 하면:

경고 제거
실행 계획 개선
인덱스 활용 가능

해진다.

해결 방법 2 - 컬럼 타입 변경

ALTER TABLE [HumanResources].[Employee]
ALTER COLUMN [NationalIDNumber] INT NOT NULL;

하지만 여기서 중요한 문제가 발생한다.

인덱스 문제

NationalIDNumber는 기존 인덱스에서 사용 중이었다.

따라서:

인덱스 DROP
ALTER COLUMN
인덱스 재생성

순서가 필요하다.

즉 운영 환경에서는 다운타임 문제가 발생할 수 있다.

Key Lookup 문제

Lab10에서는 Key Lookup 문제를 분석했다.

실행 계획에서:

Index Seek
Key Lookup(cost 99%)

이 발생했다.

왜 Key Lookup이 발생하는가?

현재 인덱스는:

(ProductID)

만 포함한다.

하지만 SELECT에서 필요한 컬럼은 더 많다.

따라서 SQL Server는:

인덱스로 위치 찾기
원본 데이터 다시 접근

을 수행한다.

이 추가 접근이 Key Lookup이다.

Covering Index

해결 방법은 Covering Index이다.

CREATE NONCLUSTERED INDEX
[IX_SalesOrderDetail_ProductID]

ON [Sales].[SalesOrderDetail]
([ProductID],[ModifiedDate])

INCLUDE (
    [CarrierTrackingNumber],
    [OrderQty],
    [UnitPrice]
)

WITH (DROP_EXISTING = on);

핵심:

필요한 컬럼을 INCLUDE로 포함
인덱스만 읽어서 쿼리 해결 가능

결과:

Key Lookup 제거
Logical Read 감소
성능 향상

Query Store

Query Store는 SQL Server의 성능 분석 기능이다.

활성화:

ALTER DATABASE [AdventureWorks2017]
SET QUERY_STORE = ON;

Query Store로 할 수 있는 것

느린 쿼리 찾기
실행 계획 비교
성능 Regression 추적
좋은 실행 계획 강제 적용

Force Plan

실행 계획 중 더 좋은 플랜을 강제로 사용 가능하다.

실습에서는:

빠른 Plan
느린 Plan

두 개를 비교 후:

Force Plan

기능으로 좋은 플랜을 강제 적용했다.

Blocking

Blocking은 트랜잭션이 서로 잠금을 기다리는 현상이다.

Blocking 실습

세션 1

BEGIN TRANSACTION

UPDATE Person.Person
SET LastName = LastName;

트랜잭션 종료 안 함.

세션 2

SELECT TOP (1000)
    [LastName],
    [FirstName],
    [Title]
FROM Person.Person
WHERE FirstName = 'David'

결과:

무한 대기
Blocking 발생

왜 발생했는가?

첫 번째 세션이 Lock을 유지 중이기 때문이다.

두 번째 세션은 같은 데이터 접근 시도 중이라 대기 상태가 된다.

Extended Events

Blocking 추적을 위해 Extended Events를 사용했다.

blocked_process_report

를 통해:

누가 막는지
어떤 쿼리가 원인인지
어떤 세션인지

확인 가능하다.

READ_COMMITTED_SNAPSHOT

Blocking 완화를 위해 Snapshot Isolation 기반 설정을 적용했다.

ALTER DATABASE AdventureWorks2017
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

핵심 개념

기존 Read Committed:

수정 중 데이터 접근 시 대기

READ_COMMITTED_SNAPSHOT:

이전 버전(Row Version) 읽음
Lock 기다리지 않음

즉:

Reader와 Writer 충돌 감소

최종 정리

이번 실습에서는 단순 SQL 작성이 아니라 실제 운영 환경 수준의 SQL Server 성능 튜닝 과정을 경험했다.

특히 다음 내용을 실제로 확인할 수 있었다.

인덱스 구조와 동작 방식
Fragmentation이 성능에 미치는 영향
실행 계획 분석
Implicit Conversion 문제
Key Lookup 제거 방법
Covering Index 설계
Query Store 기반 분석
Blocking 및 Snapshot Isolation

결국 SQL 성능 튜닝의 핵심은:

“왜 SQL Server가 그런 실행 계획을 선택했는가”

를 이해하는 것이라는 점을 확인할 수 있었다.

RudinP

성장하기 위한 기록

이전 포스트

[MicrosoftDataSchool] 81일차 - SQL Server VM → Azure SQL Database 마이그레이션, 데이터베이스 조각화

다음 포스트

[MicrosoftDataSchool] 82일차 - Azure SQL Index

Microsoft Data School 3기

SQL Server 성능 튜닝 실습 정리 - 인덱스, 실행 계획, Blocking, Query Store

인덱스(Index)란?

인덱스가 필요한 이유

1. 검색 속도 향상

2. 디스크 I/O 감소

3. 대규모 시스템 필수 요소

SQL Server의 인덱스 구조

B-Tree 구조 동작 방식

Clustered Index

특징

Clustered Index 예시

Nonclustered Index

구조 특징

Nonclustered Index 예시

Index Seek vs Table Scan

Table Scan

Index Seek

실행 계획에서 확인 가능

Covering Index

INCLUDE 사용

장점

Key Lookup

왜 문제인가?

Fragmentation(조각화)

조각화 발생 원인

조각화 문제점

Fragmentation 확인

REORGANIZE vs REBUILD

실행 계획(Execution Plan)

실행 계획에서 보는 주요 요소

Query Optimizer

Query Store

Blocking

Snapshot Isolation

핵심 개념

이번 실습에서 중요했던 핵심 포인트

를 SQL Server 내부 동작 관점에서 이해하는 것이었다.

Lab07 - Fragmentation 실습

1. 조각화 상태 확인

2. 데이터 대량 INSERT

3. 논리적 읽기(Logical Read) 측정

인덱스 REBUILD

REORGANIZE vs REBUILD

실행 계획(Execution Plan)

Lab09 - 암시적 형 변환(Implicit Conversion)

왜 발생했는가?

문제점

해결 방법 1 - 쿼리 수정

해결 방법 2 - 컬럼 타입 변경

인덱스 문제

Key Lookup 문제

왜 Key Lookup이 발생하는가?

Covering Index

Query Store

Query Store로 할 수 있는 것

Force Plan

Blocking

Blocking 실습

세션 1

세션 2

왜 발생했는가?

Extended Events

READ_COMMITTED_SNAPSHOT

핵심 개념

최종 정리

[MicrosoftDataSchool] 81일차 - SQL Server VM → Azure SQL Database 마이그레이션, 데이터베이스 조각화

[MicrosoftDataSchool] 83일차 - Azure SQL에서 Graph Database 활용하기

0개의 댓글