[MSSQL] 활성 쿼리 통계를 이용한 쿼리 속도 개선 (인덱스설정방법)

SQL Management의 쿼리실행결과 항목을 이용하여 인덱스가 누락된 표시하고 속도를 개선하는 방법에 대해 알아보겠습니다. 아래 쿼리문은 TestIndexTable 테스트 테이블만들고  천만건 가량 데이터를 넣는 부분입니다.  데이터를 많이넣고 테스트하기 위해 천만건 가량을 넣고 실 운영서버에는 느려질수 있으니 테스트 환경에서 진행하시기 바랍니다. 

-- 1. 테스트용 테이블 생성
IF OBJECT_ID('dbo.TestIndexTable', 'U') IS NOT NULL
    DROP TABLE dbo.TestIndexTable;

CREATE TABLE dbo.TestIndexTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,  -- PK 자동 생성
    Name NVARCHAR(50),
    Email NVARCHAR(100),
    Age INT,
    RegDate DATETIME
);

-- 2. 샘플 데이터 1천만 건 생성
-- 숫자 테이블 CTE를 이용해 대량 데이터 생성
WITH N1 AS (SELECT 1 AS n UNION ALL SELECT 1),          -- 2
     N2 AS (SELECT 1 AS n FROM N1 a CROSS JOIN N1 b),    -- 4
     N3 AS (SELECT 1 AS n FROM N2 a CROSS JOIN N2 b),    -- 16
     N4 AS (SELECT 1 AS n FROM N3 a CROSS JOIN N3 b),    -- 256
     N5 AS (SELECT 1 AS n FROM N4 a CROSS JOIN N4 b),    -- 65,536
     N6 AS (SELECT 1 AS n FROM N5 a CROSS JOIN N5 b),    -- 4,294,967,296 (충분히 큼)
     Numbers AS (SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num FROM N6)
INSERT INTO dbo.TestIndexTable (Name, Email, Age, RegDate)
SELECT 
    CONCAT(N'User', num),
    CONCAT('user', num, '@test.com'),
    (num % 60) + 18,  -- 18~77세 랜덤 나이
    DATEADD(DAY, -num % 365, GETDATE()) -- 최근 1년 랜덤 날짜
FROM Numbers;

SELECT COUNT(*) AS TotalRows FROM dbo.TestIndexTable;

1. 활성 쿼리 통계를 이용한 분석
SQL Management를 실행하고 상단의 "활성쿼리 통계포함"을 선택 후 쿼리문을 실행합니다.

2. 누락된 인덱스 정보 추가
인덱스의 영향을 받지 않으면 아래와 같이 "인덱스 없음" 이라는 부분이 나오며  마우스 우 클릭하여 누락된 인덱스 세부정보 항목을 선택합니다. 

3. 인덱스 추가 
인덱스를 추가하면 96%이상 향상될수 있다고 알려주고 인덱스를 스크립트 문으로 만들어 줍니다. Age와 RegDate값으로 where문에 검색을 하고있어서 복합 인덱스로 설정을 하게 알려주네요.  

4.인덱스가 정상적으로 동작하는지 확인 
인덱스를 생성한 후 다시 쿼리문을 실행하여 살펴보면 아래와 같이 IX_Age_RegDate인덱스가 반영되어 쿼리비용이 감소하는 부분을 확인 할 수 있습니다. 

SQL Management에서 해당부분으로 누락된 인덱스를 분석하여 알려줍니다. gpt를 이용하면 활성쿼리 통계에 대한 부분을 스크린 캡쳐하여 올려줘도 분석하여 알려주니 참고하시기 바랍니다.