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가 걸리지 않은 출력될 목록이 있어야 한다.
'IT > DB' 카테고리의 다른 글
인덱스 정리 (0) | 2009.09.30 |
---|---|
인덱스가 있지만 인덱스를 안 타는 경우 (0) | 2009.09.30 |
Inline 쿼리 VS 저장 프로시저 (0) | 2009.09.30 |
SUBQUERY와 INLINE-VIEW의 차이 (1) | 2009.09.30 |
[MS SQL] ldf 파일 사이즈 줄이기 또는 삭제하기 (0) | 2009.09.28 |