안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 12-9. 인덱스 생성과 관리 - DTA(데이터베이스 엔진 튜닝 관리자)를 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 인덱스 생성과 관리 - DTA(데이터베이스 엔진 튜닝 관리자)입니다.

TL;DR

데이터베이스 엔진 튜닝 관리자 도구를 소개하고 활용하는 방법을 설명합니다. DTA는 SQL Server의 쿼리를 분석하여 최적화된 인덱스와 성능 향상 가이드를 제공하며, 쿼리 선택 방법, 실행 방법, 결과 리포트 등을 자세히 다룹니다.

그래서, 어떻게 해야 인덱스를 잘 생성하나요?

SQL 쿼리 구문에 맞춰 인덱스를 생성해야 하는데 너무 어렵습니다. 애플리케이션에서 사용하는 SQL 구문도 복잡하고, 인덱스 잘 못 만들면 더 느려지고 문제가 많다고 해서 함부로 건드리기가 두렵습니다. 어떻게 해야 인덱스를 잘 생성하나요?

 

클라우드에서 데이터베이스 애플리케이션을 개발하고 서비스하는 회사입니다. 시간이 지나 데이터가 쌓이면서 애플리케이션 응답속도가 느려지고, 데이터베이스 인스턴스는 계속 CPU 100%를 치는 상황이 발생합니다. 원인은 애플리케이션에서 사용하는 몇몇 기능만 수행하면 느려집니다. 테이블 4~5개를 JOIN 하고, 쿼리 하는 데이터가 많은 테이블인데, 사용자가 몰리면 더욱 느려지고 아무리 클라우드 인스턴스를 스케일업 해서 높은 사양으로 바꿔도 그때뿐입니다. 인덱스를 생성하면 된다고 하는데, 인덱스도 어렵고, 쿼리에 맞춰 어떻게 생성해야 하는지는 더욱 어렵습니다.

이럴 때, DTA(Database Engine Tuning Advisor - 데이터베이스 엔진 튜닝 관리자)를 먼저 사용할 수 있습니다.

DTA 소개

DTA는 SQL Server에서 실행되는 쿼리를 분석해, 쿼리 성능을 최적화할 수 있는 인덱스, 파티션 등의 성능을 높일 수 있는 가이드를 제공하는 도구입니다. SQL Server의 내부 구조나 데이터베이스 튜닝에 대한 전문적인 교육을 받지 않아도, 일정 수준 최적화된 결과 리포트를 가이드로 제공합니다.

DTA 사용 방법

분석하기 원하는 (1) 쿼리를 SSMS에서 선택하고 DTA를 수행하거나, (2) SQL Server 쿼리 저장소(Query store)를 이용하거나, (3) SQL 서버의 Profiler 추적 파일 등의 정보에서 DTA를 수행할 수도 있습니다.

이번 SQLER 강좌에서는 (1) SSMS의 쿼리를 선택해 DTA를 실행하고, 인덱스를 생성하겠습니다. 

DTA를 적용할 예제 데이터 생성

아래 예제를 수행해 DTA 수행을 준비합니다.

USE master;
GO

ALTER DATABASE SQLERTestDB SET RECOVERY SIMPLE;
GO

USE SQLERTestDB;
GO

-- IndexDummy 테이블이 존재하면 삭제
DROP TABLE IF EXISTS IndexDummy;
-- 인덱스 테스트 테이블 생성
CREATE TABLE IndexDummy(
idx INT,
dummy_dt DATETIME DEFAULT(GETDATE()),
dummy_str NVARCHAR(39) DEFAULT(CONVERT(NVARCHAR(39), NEWID()))
);
GO

-- 10만 건 데이터를 WHILE문으로 생성.
SET NOCOUNT ON
DECLARE @Counter INT
SET @Counter = 0
WHILE (@Counter < 100000)
BEGIN
    INSERT INTO IndexDummy(idx) VALUES(@Counter)
    SET @Counter = @Counter + 1
END;
GO

-- 완료 후 데이터 조회
SELECT COUNT(*) FROM IndexDummy;
GO

10만 건 데이터를 생성합니다. 테이블이 작을 경우 DTA가 리포트를 제공하지 않을 수 있으니 충분히 큰 테이블을 선택합니다.

SQL 구문으로 DTA 실행 

아래 구문을 SSMS로 복사하고, 첫 SQL 구문을 선택해 DTA를 실행합니다.

SELECT * FROM IndexDummy WHERE idx = 5000

SELECT * FROM IndexDummy WHERE idx < 10

SELECT * FROM IndexDummy WHERE idx < 400

SELECT * FROM IndexDummy WHERE idx < 10000

이미지 - SQL Server DTA 실행

이미지 - DTA 확인

이미지 - DTA 튜닝 옵션 확인

분석 시작을 눌러 분석을 시작합니다.

이미지 - DTA 진행률 확인

DTA가 분석을 진행합니다. 이때 쿼리 패턴에 따라 시간이 오래 걸릴 수 있고, SQL Server 리소스가 많이 사용됩니다. 사용자가 적은 시간대에 실행하거나, 가급적이면 운영 환경이 아닌, 같은 구조의 DB가 실행되는 개발/테스트 시스템에서 수행하세요.

이미지 - DTA 튜닝 권장 구성

분석이 완료되면 권장 구성이 출력됩니다. 개체 정보를 확인하고, 권장 구성 결과 가장 오른쪽 “정의” 항목을 확인하면, SQL 스크립트로 생성된 인덱스나 파티셔닝 등의 가이드 구문을 확인 가능합니다.

이미지 - DTA 튜닝 리포트

DTA가 생성한 보고서입니다. 기본적인 요약 정보와 보고서에 따라 SQL 구문과 인덱스 등을 다양한 보고서 형식으로 확인할 수 있습니다.

여러 쿼리를 선택하고 DTA 실행

DTA는 하나의 SQL 구문뿐만 아니라, 여러 SQL 구문에 대해 가이드를 제공할 수 있습니다.

이미지 - DTA 여러 쿼리 선택

하나의 쿼리로 DTA 분석을 수행했다면, 위와 같이 여러 SQL 쿼리 구문을 묶어서 DTA 분석 실행도 가능합니다. 애플리케이션에서 사용하는 다양한 쿼리 패턴을 DTA에 전달하는 게 좋습니다. 위의 여러 SQL 쿼리 구문을 묶어 분석하는 과정을 한번 더 수행해 인덱스 생성 스크립트를 추출합니다. 더 좋은 방법은 이후에 튜닝 강좌로 진행될 Profiler를 이용하는 방법입니다. 지금은 SSMS에서 쿼리를 통해 실행하는 과정에 집중하시길 바랍니다.

DTA가 자동 생성한 인덱스

최종적으로 아래와 같은 인덱스 구문을 얻을 수 있습니다.

CREATE CLUSTERED INDEX [_dta_index_IndexDummy_c_10_1269579561__K1] ON [dbo].[IndexDummy]
(
    [idx] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

DTA에서 자동 생성된 인덱스 이름이나 설정 등을 확인하고, 최종적으로 인덱스를 생성합니다. 인덱스를 생성하고, 실행계획이나 IO 사용량을 SQLER 강좌를 통해 다시 체크하고, 성능 이슈가 발생했던 SQL 쿼리가 적절하게 인덱스를 타서 실행되는지 다시 애플리케이션의 SQL 구문을 확인합니다.

인덱스는 SQL 쿼리 성능에 매우 중요한 데이터베이스 개체입니다. 반드시 잘 문서화하고, 지속적으로 모니터링하세요.

이렇게 인덱스 강좌를 마무리합니다. SQL Server  튜닝 관련 강좌도 이후에 진행 예정이니 기대해 주세요.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022