MS-SQL 실행 계획 확인

MSSQL에선 실행 계획을 통해 인덱스를 타는지, 어떠한 쿼리가 더 빠른지 측정할 수가 있습니다.

쿼리
Set Statistics Profile { ON | OFF } -- 쿼리 결과에 실행 계획을 포함
Set Statistics IO {ON | OFF}         -- 페이지의 입출력 수를 알 수 있다.
Set Showplan_All { ON | OFF }     -- 실행 계획만 보는 옵션

실행 계획은 SQL Server 쿼리 프로세서가 각 문을 실행할 때 취한 단계를 나타내는 계층적 트리를 이루는 행 집합으로 정보를 반환합니다. 출력에 반영된 문에는 문의 텍스트가 있는 단일 행이 포함되고, 이 단일 행 뒤에는 실행 단계에 대한 자세한 정보가 있는 몇 개의 행이 있습니다 아래 표에서는 출력에 포함된 열을 보여 줍니다.

열 이름	설명
Rows	실행된 행수
Executes	Loop 일 경우 Loop가 실행 된 횟수
StmtText	PLAN_ROW 형식이 아닌 행에 대해 이 열에는 Transact-SQL 문의 텍스트가 포함됩니다. PLAN_ROW 유형의 행에 대해서는 이 열에 작업에 대한 설명이 포함됩니다. 이 열에는 물리적 연산자가 포함되며 논리 연산자가 포함될 경우도 있습니다. 이 열 다음에 물리적 연산자가 결정한 설명이 나올 경우도 있습니다. 자세한 내용은 논리 및 물리 연산자 참조를 참조하십시오.
StmtId	현재 일괄 처리에 있는 문의 수입니다. (몇번째 쿼리에 대한 통계인지)
NodeId	현재 쿼리의 노드 ID입니다. (현재 노드의 식별값)
Parent	부모 단계의 노드 ID입니다 (이 값이 같은 것 끼리, 같은 depth라고 생각하면 된다)
PhysicalOp	노드에 대한 물리적 구현 알고리즘입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
LogicalOp	이 노드가 나타내는 관계형 대수 연산자입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
Argument	수행되는 작업에 대한 추가 정보를 제공합니다. 물리적 연산자에 따라 이 열의 내용이 달라집니다.
DefinedValues	이 연산자가 사용하는 값에 대한 쉼표로 구분된 목록을 포함합니다. 이 값은 현재 쿼리에 있었던 계산된 식(예: SELECT 목록이나 WHERE 절에 있음)이거나 이 쿼리를 처리하기 위해 쿼리 프로세서에서 사용한 내부 값입니다. 쿼리 내의 어디에서든 정의된 이 값이 참조될 수 있습니다. PLAN_ROWS 형식의 행에만 해당됩니다.
EstimateRows	이 연산자가 생성한 출력의 예상 행 수입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
EstimateIO	작업에 대한 예상 I/O 비용입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
EstimateCPU	이 연산자에 대한 예상 CPU 비용입니다. PLAN_ROWS 형식의 행에만 해당됩니다.
AvgRowSize	이 연산자를 통해 통과되는 행의 예상 평균 행 크기(바이트)입니다.
TotalSubtreeCost	이 작업 및 모든 자식 작업에 대한 예상(누적) 비용입니다.
OutputList	현재 작업에서 예상하고 있는 열에 대한 쉼표로 구분된 목록을 포함합니다.
Warnings	현재 작업과 연관된 경고 메시지에 대한 쉼표로 구분된 목록을 포함합니다. 경고 메시지에 열 목록과 함께 "NO STATS:()" 문자열이 포함될 경우도 있습니다. 이 경고 메시지는 쿼리 최적화 프로그램이 이 열의 통계에 기초하여 결정을 내리려고 했지만 사용 가능한 통계가 없었음을 나타냅니다. 따라서 쿼리 최적화 프로그램이 추측을 해야 했고 결과적으로 비효율적인 쿼리 계획을 선택했을 수도 있습니다. 쿼리 최적화 프로그램이 더 효율적인 쿼리 계획을 선택할 수 있도록 통계를 만들거나 업데이트하는 방법은 UPDATE STATISTICS를 참조하십시오. 어떤 경우에는 이 열에 조인 조건자 없이 조인(테이블을 수반하는)이 일어났음을 나타내는 "MISSING JOIN PREDICATE" 문자열이 포함되기도 합니다. 실수로 조인 조건자를 삭제하면 예상보다 실행 시간이 긴 쿼리가 만들어지고 큰 결과 집합이 반환됩니다. 이 경고가 나타나면 조인 조건자를 의도적으로 사용하지 않았는지 확인하십시오.
Type	노드 유형. 각 쿼리의 부모 노드에 대해서는 노드 유형이 Transact-SQL 문 유형(예: SELECT, INSERT, EXECUTE 등)입니다. 실행 계획을 나타내는 하위 노드에 대해서는 PLAN_ROW 유형입니다.
Parallel	0 = 연산자가 병렬로 실행되지 않습니다. 1 = 연산자가 병렬로 실행됩니다.
EstimateExecutions	현재 쿼리를 실행하는 동안 이 연산자가 실행될 예상 횟수입니다.

Physical Op & Losical Op 에 사용되는 연산자
1) Bookmark Lookup 실행 계획 연산자
Bookmark Lookup 연산자는 책갈피(행 ID 또는 클러스터링 키)를 사용하여 테이블이나 클러스터형 인덱스에서 해당 행을 조회합니다. Argument 열에는 테이블이나 클러스터형 인덱스에서 행을 조회할 때 사용하는 책갈피 레이블이 포함됩니다. Argument 열에는 행을 조회하는 테이블 또는 클러스터형 인덱스의 이름도 포함됩니다. WITH PREFETCH 절이 Argument 열에 나타나는 경우에 쿼리 프로세서에서는 테이블 또는 클러스터형 인덱스에서 책갈피를 조회할 때 비동기 사전 인출(미리 읽기)을 사용하는 것을 최적의 방법으로 결정합니다.

SQL Server 2008에서는 Bookmark Lookup이 사용되지 않습니다. 대신 Clustered Index Seek 및 RID Lookup이 책갈피 조회 기능을 제공합니다. Key Lookup 연산자도 이 기능을 제공합니다.

Clustered Index는 DATA PAGE가 Index Leaf Level 에 존재를 한다.
Non-Clustered Index의 DATA PAGE는 Index Leaf Level에 존재하지 않는다.

즉, Non-Clustered Index로 해당 값을 Index Seek로 검색을 하였어도 출력될 값을 찾기 위해서는 해당 DATA PAGE 까지 찾아 들어가야 하는것이다. 그래서 MS-SQL 2000에서는 Bookmark lookup이라는것이 발생하는것이다.

2) RID Lookup 실행 계획 연산자
RID Lookup은 제공된 RID(행 식별자)를 사용하여 힙을 조회하는 책갈피 조회입니다. Argument 열은  테이블의 행을 조회하는 데 사용되는 책갈피 레이블 및 행을 조회할 테이블의 이름을 포함합니다.

RID Lookup은 항상 NESTED LOOP JOIN과 함께 사용됩니다.
RID Lookup은 물리 연산자입니다.

이제 RID Lookup을 실제 재현해보기 위해서 Test_Lookup Table에 Unique Non-Clustered Index를 만들어 보겠다.
왜 굳이 Non-Clustered Index를 만드느냐? RID Lookup은 Heap Table에서 만 일어난다. Heap Table은 Clustered Index가 없는 Table을 지칭한다. 그럼 왜 또 구지 Unique Type으로 만드냐? Unique Type이 아니면 Query 실행 될때 Table Scan이 일어난다

3) Key Lookup 실행 계획 연산자
SQL Server 2005 서비스 팩 2에 새로 추가된 Key Lookup 연산자는 클러스터형 인덱스가 있는 테이블의 책갈피 조회입니다. Argument 열에는 클러스터형 인덱스의 이름과 클러스터형 인덱스에서 행을 조회할 때 사용되는 클러스터링 키가 포함됩니다. Key Lookup은 항상 Nested Loops 연산자와 함께 사용됩니다. WITH PREFETCH 절이 Argument 열에 나타나는 경우에 쿼리 프로세서에서는 클러스터형 인덱스에서 책갈피를 조회할 때 비동기 사전 인출(미리 읽기)을 사용하는 것을 최적의 방법으로 결정합니다. 

Table에서 KEY Lookup을 발생시키려면 3가지 조건이 필요하다.
첫번째, Clustered Index가 존재해야 한다.
두번째, 검색될 Non-Clustered Index가 존재해야 한다.
세번째, Index가 걸리지 않은 출력될 목록이 있어야 한다.