14. 텍스트 및 XML 실행 계획의 기초 - 텍스트 실행 계획

텍스트 및 XML 실행 계획의 기초

 

이번 포스트부터는 텍스트 실행 계획과 XML 실행 계획을 해석하는 방법에 대해 살펴 볼 것이다. 이미 실행 계획의 기초에서 간단히 살펴보았지만 텍스트 및 XML 실행 계획에는 그래픽 실행 계획과는 달리 아이콘이 없다. 다만 그것에 상응하는 연산자들만 포함하고 있다.

 

이전 버전의 SQL Server에서는 오직 텍스트 기반의 실행 계획만 사용 가능했다. 많이 사람들이 특히, 복잡한 실행 계획을 해석할 때 가독성이 떨어진다는 불평을 쏟아내었으며, 이에 마이크로소프트는 그래픽 실행 계획을 제공하기 시작했다. 확실히 그래픽 실행 계획은 텍스트 실행 계획보다 보기 편했다. 개발자는 물론 DBA들은 그래픽 실행 계획을 환영하였으며, 누구나 쉽게(?) 접근할 수 있는 실행 계획 해석 도구의 등장으로 텍스트 실행 계획은 이제 퇴물로써 역사의 뒤안으로 사라질 예정이다.

 

SQL Server 2005부터는 텍스트 기반의 실행 계획의 대안으로 XML 실행 계획을 제공하고 있다.

 

하지만 XML 실행 계획 또한 읽기 어려운 건 마찬가지이다. 특히, 실행 계획에서 XML 코드를 분리해 내기란 여간 쉬운 일이 아니다. 그렇다면 마이크로소프트가 이 불편하기 짝이 없는 XML 실행 계획을 텍스트 실행 계획의 대안으로 내세운 이유는 무엇일까?

 

여기에는 몇 가지 이유가 있다.

 

먼저, XML 실행 계획은 텍스트 기반의 실행 계획과는 달리 프로그램적으로 공용되고 있는 파일 형식이라는 점을 첫 번째 이유로 들 수 있다. 그리고 XML로는 텍스트과는 비교할 수 없을 정도로 풍부한 정보를 기록 할 수 있다는 장점도 있다. 또한 저장된 XML 실행 계획은 타인과 공유하기에도 용이하다. 예를 들어, 내가 XML 실행 계획을 다른 DBA에게 보내어 분석을 의뢰하면, 그 DBA는 SSMS에서 그래픽 실행 계획 포맷으로 열어 쉽게 분석할 수 있다.

 

텍스트 실행 계획

"이제 없어질 놈인데 텍스트 실행 계획을 공부할 필요가 있을까?"

 

아마 한 번쯤 이런 의문을 가졌을 것이다. 이 질문의 답은 여러분에게 있다. 만일 여러분의 환경에서 SQL Server 2005을 주로 사용하고 있다면 이 내용은 스킵하고 그래픽 실행 계획이나 XML 실행 계획에 포커스를 두고 공부해도 될 것이다. 하지만 이전 버전의 SQL Server를 여전히 사용하고 있다면 텍스트 실행 계획을 공부하기 싫어도 공부해야만 할 것이다.

이해를 돕기 위해 8. 그래픽 실행 계획의 기초 - Single Table Query 에서 사용한 쿼리를 작성해 보겠다.

SELECT ContactID,        LastName,        Phone FROM Person.Contact WHERE EmailAddress LIKE 'sab%'

 

이 쿼리의 그래픽 실행 계획을 상기시켜 보자.

 

[그림 1] 예제 쿼리의 그래픽 실행 계획

 

이제 텍스트 실행 계획을 생성해 보자. SHOWPLAN_ALL을 ON해야 하는 것, 그리고 실행 계획을 생성해야 한 후에는 반드시 OFF해야 한다는 것을 잊지 말자.

 

SET SHOWPLAN_ALL ON ; GO   SELECT ContactID,        LastName,        Phone FROM Person.Contact WHERE EmailAddress LIKE 'sab%' GO SET SHOWPLAN_ALL OFF ; GO

 

예상 실행 계획이 생성되었다.

 

 

[그림 2] 예제1 쿼리의 텍스트 실행 계획 1

 

첫 번째 행은 부모 노드로써 StmtText 열에는 실행된 T-SQL문이 저장되어 있다.

오른쪽으로 스크롤을 하면 아래의 그림처럼 Type이라는 열을 찾을 수 있을 것이다. Type은 해당 노드의 타입을 의미한다.

 

[그림 3] 예제1 쿼리의 텍스트 실행 계획 2

 

첫 번째 행의 Type에는 실행된 T-SQL문의 타입이 저장되어 있다. 여기서는 SELECT이다. 나머지 행들의 타입은 PLAN_ROW이다. 각 PLAN_ROW 노드의 StmtText 열에는 연산자가 저장되어 있다. 각각 Nested Loop Inner Join, Index Seek, Clustered Index Seek이다.

 

텍스트 실행 계획도 그래픽 실행 계획과 마찬가지로  "오른쪽에서 왼쪽으로, 위에서 아래로" 순으로 읽는다. 마치 오라클의 그것과 흡사하다. NodeID와 Parent 행과 함께 파이프 라인(|) 을 이용하면 연산자의 흐름을 이해하는데 도움이 될 것이다.

현재 노드의 ID는 무엇이며 그 부모 노드는 무엇인지 확인해 가며 파이프 라인을 따라가기만 하면 된다.

 

같은 부모 노드를 가진 행은 "위에서 아래로"의 순서대로 연산자가 실행된다. 이 예제에서는 Index Seek를 한 다음 Clustered Index Seek를 하고 있다.

 

이 실행 계획은 가장 움푹하게 들여져 보이는 세 번째 행(NodeId 3)인 Index Seek 연산에서 부터 시작한다. StmtText 열이나 Argument 열을 펼쳐보면 [Person].[Contact] 테이블에 대해 Index Seek를 하고 있음을 확인할 수 있다.

 

OBJECT:([AdventureWorks].[Person].[Contact].[IX_Contact_EmailAddress]),
SEEK:([AdventureWorks].[Person].[Contact].[EmailAddress] >= N'sab' AND
[AdventureWorks].[Person].[Contact].[EmailAddress] < N'saC'),
WHERE:([AdventureWorks].[Person].[Contact].[EmailAddress] like N'sab%') ORDERED
FORWARD

 

DefinedValues 행에는 주어진 PLAN_ROW에 연산자에 전달된 값들이 콤마 구분된 리스트로 나열되어 있다.

 

[AdventureWorks].[Person].[Contact].[ContactID],
[AdventureWorks].[Person].[Contact].[EmailAddress]

 

이 값들은 쿼리를 수행하기 위해 쿼리 프로세서가 필요로 하는 내부 값들로써, DefinedValues열은 연산자를 수행하기 위해서 쿼리 옵티마이저에 의해 사용되는 값들을 저장한 임시 저장소라고 보면 된다.

 

마지막으로 EstimateRows를 보면 Index Seek 연산으로 인해 얻어진 행 수는 19.8행이다.

 

다음 연산인 4행(NodeID 5)로 이동해 보자.  [Person].[Contact] 테이블에 대해 Clustered Index Seek를 하고 있다. 텍스트 형식이기 때문에 금방 확인하기는 힘들지만, 이 연산은 Key Lookup 연산이다. 이 사실은 StmtText 열을 살펴보면 확인할 수 있다.

 

|--Clustered Index
Seek(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID]),
SEEK:([AdventureWorks].[Person].[Contact].[ContactID]=[AdventureWorks].[Person].[
Contact].[ContactID]) LOOKUP ORDERED FORWARD)

 

EstimateRows 열을 확인해 보자. 이 번에는 1 행이 얻어졌다. 하지만 마지막 열인 EstimateExecutions 열을 보면, 쿼리가 실행되는 동안 이 연산자가 19.8번 호출되었음을 알 수 있다. 이는 선행 연산인 Index Seek(NodeID 3)과  19.8번 Nested Loop Inner Join 연산을 했기 때문이다. 하지만, 이 쿼리의 그래픽 실행 계획을 캡쳐한 후 Key LookUp 연산의 등록정보를 보면 이 보다 덜 실행되었음을 알 수 있다. 예제의 경우, Rewind는 18.8, Rebind는 0이다. Rewind와 Rebind에 대해서는 12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds 를 참조하기 바란다.

 

다음 연산은 NodeID 2이다. 앞서 설명하였다시피 선행 연산 결과를 Nested Loop Inner Join 연산을 하고 있다. 여기서 Defined Values가 Null인 것은 이 연산으로 어떤 새로운 값을 생성하지 않았기 때문이다. 그리고 OutputList를 보면 이 쿼리에서 요구한 결과 집합인 [ContactID], [LastName], [Phone] 컬럼이 보인다.

 

이 밖에, EstimateRows, EstimateIO, TatalSubTreeCost 와 같은 행에 대한 설명은 이미 그래픽 실행 계획의 기초에서 다루웠던 내용이므로 생략하도록 하겠다.

 

 

지금까지 살펴본 예제는 아주 단순한 쿼리로써 순서대로 쭉 읽어나가기만 하면 되었다. 이제 조금 복잡한 쿼리를 작성하여 다음 이야기를 진행하도록 하겠다.

 

SET SHOWPLAN_ALL ON ; GO SELECT c.[LastName], a.[City], cu.[AccountNumber], st.[Name] AS TerritoryName FROM [Person].[Contact] c JOIN [Sales].[Individual] i ON c.[ContactID] = i.[ContactID] JOIN [Sales].[CustomerAddress] ca ON i.[CustomerID] = ca.[CustomerID] JOIN Person.Address a ON [ca].[AddressID] = [a].[AddressID] JOIN [Sales].Customer cu ON cu.[CustomerID] = i.[CustomerID] JOIN [Sales].[SalesTerritory] st ON [cu].[TerritoryID] = [st].[TerritoryID] WHERE st.[Name] = 'Northeast' AND a.[StateProvinceID] = 55 ; GO SET SHOWPLAN_ALL OFF ; GO

 

이 쿼리를 실행하면 예상 실행계획이 결과 창에 표시된다.

[그림 4]는 실행 계획 결과의 StmtText 컬럼의 내용을 보여주고 있다.

 

[그림 4] 예제2 쿼리의 텍스트 실행 계획 1

 

역시 파이프 라인을 따라서 가장 움푹하게 들어가 있는 부분부터 읽어나가도록 하자.

이 쿼리에서는 가장 먼저, [Person].Address] 테이블의 [IX_Address_StateProvinceId] 인덱스를 Index Seek를 하고 있다. 

 

[그림 5] 예제2 쿼리의 텍스트 실행 계획 2

 

이 연산을 통해 WHERE절에 조건으로 기술한 내용(a.[StateProvinceID] = 55)을 걸려낸다. 걸러진 결과 집합은 이어지는 모든 연산들이 사용하게 되므로 전체 작업량에 큰 영향을 미치게 된다. 따라서 이 연산에서 검색 범위를 얼마나 축소를 시키는냐가 성능 관점에서 중요한 포인트가 될 것이다. 이 연산으로 부터 얻어진 결과 값은 [AddressId] 컬럼이다. [AddressId]는 SELECT 리스트에는 포함되어 있지 않지만 다음 연산에서 필요로 한다. 

 

--Index Seek(
OBJECT:([AdventureWorks].[Person].[Address].[IX_Address_StateProvinceID] AS [a]),
SEEK:([a].[StateProvinceID]=(55)) ORDERED FORWARD)

 

이어지는 연산은 Clustered Index Seek 연산이다.

공급받은 [AddressId]를 사용하여  [Person].[Address] 테이블의 [PK_Address_AddressID] 인덱스를 검색한다.

StmtText 컬럼을 보면 확인할 수 있듯이 이 연산은 Key Lookup 연산이다.

 

--Clustered Index Seek
(OBJECT:([AdventureWorks].[Person].[Address].[PK_Address_AddressID] AS [a]),
SEEK:([a].[AddressID]=[AdventureWorks].[Person].[Address].[AddressID] as
[a].[AddressID]) LOOKUP ORDERED FORWARD)

 

다음 연산자를 해석하기 위해 파이프 라인의 한 스텝 바깥(8행)으로 가보자. 

여기서는 선행된 두 개의 연산자의 결과를 Nested Loop Join을 하고 있다.

 

[그림 6] 예제2 쿼리의 텍스트 실행 계획 3

 

이제, 파이프 라인을 쭉 타고 밑으로 내려가 보자. 11행에 도달할 것이다.

[Sales].[CustomerAddress] 테이블의 [AK_CustomerAddress_rowguid] 인덱스 전 영역에 대해 Index Scan을 하고 있다.

 

--Index Scan(OBJECT:([AdventureWorks].[Sales].[CustomerAddress].[AK_CustomerAddress_rowguid]AS [ca]))

 

이 연산에 의해 약 19,000 행의 결과 집합이 생성되고, 생성된 결과는 다음 연산인 Hash Match Join으로 전달된다.

 

[그림 7] 예제2 쿼리의 텍스트 실행 계획 4

 

마찬가지로 Hash Match 연산자의 파이프 라인을 따라가 보자. 12행인 Compute Scalar 연산에 도달할 것이다. 

 

여기서는 선행 연산 즉, [Sales].[Customer] 테이블의 PK_Customer_CustomerID 인덱스를 Clustered Index Seek 연산한 결과에 대해 Compute Scalar 연산을 수행하고 있다.

 

그런데, 좀 이상하지 않는가?

실행된 쿼리에는 Scalar 연산이 포함되어 있지 않는데 Compute Scalar 연산을 하고 있다.

도대체 왜???

 

그 답을 얻기 위해 다시 한 번 실행 계획을 살펴보자.

StmtText열을 보면 SELECT 리스트에 있는 [AccountNumber] 열을 조회하기 위해, isnull.. 이라는 스칼라 함수를 사용하고 있다.

 

-- Compute Scalar

(DEFINE:([cu].[AccountNumber]=isnull('AW'+[AdventureWorks].[dbo].[ufnLeadingZeros]

([AdventureWorks].[Sales].[Customer].[CustomerID] as [cu].[CustomerID]),'')))

 

[Sales].[Customer] 테이블의 DDL문을 추출해 보면 보다 명백해 진다. 

[AccountNumber] 열이 (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')) 로 정의되어 있기 때문에 [AccountNumber] 값을 얻기 위해 Compute Scalar 연산을 수행한 것이다.

 

Scalar 연산의 결과와 Hash Match 연산의 결과는 다음 이어지는 Nested Loop 연산에 의해 결합된다.

 

[그림 8] 예제2 쿼리의 텍스트 실행 계획 5

  

그 다음 연산을 확인하기 위해 다시 한 스텝 밖으로 이동하자.

[AccountNumber] 열을 다시 Compute Scalar 연산을 하고 있다.

앞서 살펴본 Compute Scalar 연산은 함수식을 이용해서 [Sales].[Customer].[CustomerID] 컬럼을 계산한 것이고, 이 스텝에서의 Compute Scalar 연산은 [AccountNumber] 을 정의한 것이다.

 

[그림 9] 예제2 쿼리의 텍스트 실행 계획 6

  

파이프 라인을 따라 같은 레벨을 찾아가 보면, PK_Individual_CustomerId 인덱스에 대해 Clustered Index Seek 연산을 하고 있는 것이 보인다. 이 두 결과는 아래의 그림의 4행에서 Nested Loop Join을 통해 결합된다.

 

[그림 10] 예제2 쿼리의 텍스트 실행 계획 7

 

이 결과는 다시, 15행의 [Person].[Contact] 테이블에 대해 Clustered Index Seek 연산을 한 결과와 Nested Loop Join으로 3행에서 결합되고,

 

 

[그림 11] 예제2 쿼리의 텍스트 실행 계획 8

 

결합된 결과는 2행에서, [Sales].[SalesTerritory] 테이블을 Clustered Index Seek 연산을 한 결과와 마지막으로 Nested Loop Join을 한 후, 최종 결과 집합을 1행에서 보여주게 된다.

 

[그림 12] 예제2 쿼리의 텍스트 실행 계획 9

 

 

다음 이야기 : 15. 텍스트 및 XML 실행 계획의 기초 - XML 예상 실행 계획