[MSSQL/SQL SERVER] 실행계획

실행계획 : 쿼리를 실행하기 위해 DB엔진의 쿼리 최적화 프로그램이 결정한 가장 효율적인 방법의 출력물

 

쿼리 처리 과정

구문분석 → 표준화 → 최적화 → 컴파일 → 실행

※ 최적화 단계에서 통계, 조각정보 등으로 실행 계획을 만든다.

• 쿼리분석 : 검색제한자, 조인 조건등을 판단
• 인덱스 선택 : 인덱스의 유무, 통계를 바탕으로 한 유용성 판단 
• 조인 처리 : 조인 방법의 선택, 테이블의 순서 선택

플랜의 재사용을 위해서 메모리에 캐시된다(플랜캐시).

 

실행계획이 업데이트 되는 경우 (recompile)

재컴파일 :

기존의 실행계획을 사용하지 않고 새로운 실행계획을 만들면서 컴파일 하는 방법 (WITH or OPTION으로 사용

 

• 쿼리에서 참조하는 테이블이나 뷰가 변경된 경우(ALTER TABLE 및 ALTER VIEW).
• 단일 프로시저가 변경된 경우. 이 경우 해당 프로시저의 모든 계획이 캐시에서 삭제됩니다(ALTER PROCEDURE).
• 실행 계획에 사용되는 인덱스가 변경된 경우
• UPDATE STATISTICS등의 문에서 명시적으로 생성되거나 자동으로 생성되어 실행 계획에 사용되는 통계가 업데이트된 경우.
• 실행 계획에 사용되는 인덱스가 삭제된 경우
• sp_recompile에 대한 명시적 호출.
• 쿼리에서 참조하는 테이블을 수정하는 다른 사용자가 INSERT 또는 DELETE 문으로 키를 많이 변경한 경우.
• 트리거가 있는 테이블의 경우 inserted 또는 deleted 테이블의 행 수가 현저하게 증가하는 경우.
• WITH RECOMPILE 옵션을 사용하여 저장 프로시저를 실행하는 경우.

 

예상실행 계획과 실제실행 계획이 다른 경우

 

동적 저장프로시저(sp)를 이용할 때 저장프로시저는 처음 호출된 실행계획을 저장한다. 그래서 예상 실행계획을 확인하면 저장된 실행계획이 나오지만 실제로 돌렸을 땐 다른 실행계획이 실행 될 수 있음.

 

 

DBCC FREEPROCCACHE : 계획에 있는 모든 캐시 삭제

특정 캐시 삭제 방법

1. 캐시 탐색 방법

SELECT
databases.name,
dm_exec_query_stats.sql_handle,
dm_exec_query_stats.plan_handle,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.last_execution_time,
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.last_logical_reads,
dm_exec_query_stats.total_logical_writes,
dm_exec_query_stats.last_logical_writes,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats WITH(NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases WITH(NOLOCK)
ON dm_exec_sql_text.dbid = databases.database_id
--AND databases.name = 'DBNAME'
ORDER BY dm_exec_query_stats.sql_handle, dm_exec_query_stats.creation_time

2. 출력 목록중 TSQL_TEXT를 통하여 실행 계획이 잡힌 쿼리를 확인 후 해당 PLAN_HANDLE 확인

3. 2.에서 확인한 PLAN_HANDLE값으로 DBCC FREEPROCCACHE 실행 DBCC FREEPROCCACHE(PLAN_HANDLE) 

 

 

실행계획을 읽는 방법 : 위에서 아래로 오른쪽에서 왼쪽으로 확인한다 이는 쿼리가 실행되는 순서이기도 함.실행계획 노드에 담긴 정보

 

행 개수	연산자에 의해 실제로 출력된 행 수
하위 트리 비용	현재 노드와 모든 하위 노드들의 총 비용
인수	쿼리에서 사용하는 조건자와 매개 변수
예상 행 크기	출력되는 행의 예상 크기
예상 행 수	예상되는 출력 행 수
실행 횟수	쿼리 중 연산이 실행되는 횟수
비용	연산을 실행하는 쿼리 최적화기의 비용. 현재 노드 자체만의 비용.  괄호 안에는 동일 트리 내에서 상대적 비용이 백분율로 표시
물리적 연산	명령문 처리에 사용되는 물리적 구현 알고리즘 EX ) Loops, Merge Join
논리적 연산	명령문 처리에 사용되는 논리 함수 EX ) Inner Join, Outer Join
I/O 비용	연산을 위한 모든 I/O 작업의 예상 비용
CPU 비용	연산을 위한 CPU 작업의 예상 비용

 

좋은 비용의 실행계획 수립에 도움을 줄 수 있는 방법들

인덱스 키 값이 고유하다면 유니크 인덱스로 생성

 

복합인덱스의 경우 컬럼의 순서에 유의 (복합인덱스의 컬럼이 다 사용되면 WHERE절에 호출되는 순서는 상관없이 복합인덱스를 사용한다)

필터드 인덱스는 필터 조건을 명확하게 지정해 주어야함. (WHERE 절에 필터 조건과 동일하게 작성 되어야함. LIKE는 지원 X. 데이터 변환이 비교절의 왼쪽에서 발생하면 오류가 발생한다

EX :  COL1의 데이터 타입이 VARCHAR인 경우  WHERE COL1 =4 → 오류 발생 해결방법 : WHERE COL1 = CONVERT(VARCHAR, 4) ) 

커버링 인덱스는 I/O 절감에 있어 효과적일 수 있다. (룩업을 방지할 수 있다)

비교 작업이 필요한 경우 같은 데이터형으로 맞춰 주어야함. → 형변환이 일어나 인덱스에서 설정된 형식과 달라지면 인덱스를 사용 못한다.

통계 업데이트

통계는 전체 건수의 20%가 변경되면 자동으로 업데이트된다.

수동 업데이트 :  UPDATE STATISTICS 테이블명