SSMS, SQL Server Profiler 를 사용하여 부족한 인덱스 찾아내기

 

 

첫번째 방법. 전체적으로 필요한 인덱스 찾아내기

두번째 방법. 쿼리 하나만 실행해서 인덱스가 있는지 찾아내기

데이터 정리 오래된 데이터를 주기적으로 정리하는 방법

데이터 정리 Query Store의 사용량 확인

 

 

 

 

먼저 첫번째 방법. 전체적으로 필요한 인덱스 찾아내기

 

 

1. SSMS 에서 다음 명령어를 이용해 SQL Server의 Query Store 를 활성화 한다.

ALTER DATABASE [DatabaseName]  
SET QUERY_STORE = ON;  

ALTER DATABASE [DatabaseName]  
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

• READ_WRITE 모드는 쿼리와 실행 계획을 기록하고 분석할 수 있는 상태를 의미한다.

2. 도구 - SQL Server Profiler 를 선택해 Profiler를 실행한다.

3. 서버의 정보를 입력하고 연결을 누른다

 

4. Trace Properties 창이 표시되는데 Event Selection 탭을 선택한다. 

(다른 설정은 만지지 않아도 된다)

 

5. 아래 세가지를 체크한다

 - Performance의 Showpaln XML

 - TSQL의 SQL:BatchComplated

 - TSQL의 SQL:BatchStarting

 

만약 Event 에서 위 세가지 항목을 찾기 어렵다면 Show all events 를 눌러 전체 이벤트를 표시하고

Performance와 TSQL 카테고리에서 하위 항목을 찾으면 된다.

 

6. 조회 쿼리가 있는 사이트를 여기저기 돌아다닌다. (웹사이트 기준)

 

7. 아래 쿼리를 실행해 실행 계획과 Missing index를 확인한다.

SELECT 
    qsq.query_id,
    qsp.plan_id,
    qt.query_sql_text,
    rs.avg_duration AS AvgDuration,
    qsp.query_plan
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text AS qt ON qsq.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats AS rs ON qsp.plan_id = rs.plan_id
WHERE rs.last_execution_time >= DATEADD(DAY, -7, GETDATE()) -- 최근 7일간 실행된 쿼리
ORDER BY rs.avg_duration DESC;

 

• query_sql_text: 실행된 쿼리 텍스트.
• query_plan: XML 형식으로 실행 계획을 확인할 수 있음.

위 쿼리의 실행 결과

 

 

8. query_paln 컬럼에서 Missing Index가 존재하는 경우 query_plan 컬럼에 <MissingIndexes> 태그가 포함된다.

아래 쿼리를 실행해 MissingIndex를 추출한다.

SELECT
    mid.statement AS TableName,
    mid.equality_columns AS EqualityColumns,
    mid.inequality_columns AS InequalityColumns,
    mid.included_columns AS IncludedColumns,
    CAST('CREATE INDEX [IX_' + mid.statement + '_' + mid.equality_columns + '] ON ' 
         + mid.statement + ' (' + ISNULL(mid.equality_columns, '') 
         + CASE WHEN mid.inequality_columns IS NOT NULL THEN ', ' + mid.inequality_columns ELSE '' END 
         + ')' 
         + CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END AS NVARCHAR(MAX)) AS CreateIndexScript
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY migs.avg_user_impact DESC;

 

 

실행 결과

 

9. CreateIndexScript 에 Index 생성 쿼리가 추출되었음을 확인할 수 있다.

 

10. 끝

 

 

두번째 방법. 쿼리 하나만 실행해서 인덱스가 있는지 찾아내기

1. SSMS 의 상단 탭에서 [실제 실행 계획 포함] 을 선택한다. ( 단축키 Ctrl + m )

 

이미지의 화살표 부분에 있다. 

 

2. Select 문을 실행한다.

 

3. 하단의 결과 / 메세지 / 실행계획 탭에서 실행계획 탭을 선택한다.

 

4. 인덱스가 없는 경우 초록색 글씨로 인덱스 없음 이라는 문구와 함께 Create 쿼리를 확인할 수 있다.

 

5. 초록색 인덱스 없음 글자 위에서 우클릭 하면 '누락된 인덱스 세부 정보' 라는 메뉴가 표시된다.

 

 

 

6. '누락된 인덱스 세부 정보' 를 클릭하면 자동으로 생성된 Create 쿼리를 새창에서 확인할 수 있다.

 

7. 주석을 제거하고 실행하면 끝

 

데이터 정리 오래된 데이터를 주기적으로 정리하는 방법

ALTER DATABASE [DatabaseName]  
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

 

• QUERY_STORE: 데이터베이스의 Query Store를 설정.
• CLEANUP_POLICY: 오래된 쿼리 데이터를 자동으로 정리.
• STALE_QUERY_THRESHOLD_DAYS = 30: 최근 30일 동안 실행되지 않은 쿼리 데이터는 정리 대상으로 지정.

 

 

데이터 정리 Query Store의 사용량 확인

SELECT desired_state_desc, actual_state_desc, current_storage_size_mb, max_storage_size_mb  
FROM sys.database_query_store_options;

 

 

• desired_state_desc: Query Store에 설정된 상태 (예: ON, OFF).
• actual_state_desc: Query Store의 실제 상태 (현재 실행 중인 상태).
• current_storage_size_mb: Query Store가 현재 사용하는 저장소 크기(MB).
• max_storage_size_mb: Query Store에 허용된 최대 저장소 크기(MB).

 

 

 

태그

 

SSMS / SQL SERVER / index / 인덱스 / MSSQL 프로파일러 / SQL SERVER PROFILER / 인덱스 쿼리 찾기 / Missing Index  / SSMS 실행 계획 / Query Store