11. 그래픽 실행 계획의 기초 - GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획

GROUP BY와 ORDER BY가 있는 쿼리에서의 실행 계획

 

 

 

정렬(Sort)

  

아래와 같이 ORDER BY절을 포함한 간단한 쿼리를 작성해 보자.

 

SELECT * FROM [Production].[ProductInventory] ORDER BY [Shelf]

 

실행 계획은 다음과 같다.

 

[그림 1] Sort

 

Clustered Index Scan 연산의 결과 집합이 Sort 연산에 공급되고 있다. Sort 연산은 문자 그대로 쿼리 옵티마이저가 데이터를 정렬할 때 나타난다. ORDER BY절에 오름차순(ASC)으로 정렬할지 내림차순(DESC)으로 정렬할지 명시하지 않으면 기본적으로 오름차순(ASC)으로 정렬된다.

 

[그림 2] Sort의 ToolTip

  

Sort 연산의 ToolTip을 보면 Sort 연산으로 1,069행이 통과함을 확인할 수 있다. Sort 연산자는 선행 연산인 Clustered Index Scan 연산으로 부터 1,069 행의 집합을 공급받아, 이를 정렬한 후, 정렬된 1,069 행을 반환하고 있다. 여기서 흥미롭게 지켜봐야 할 것 하나! 그것은 Sort 연산이 이 쿼리의 전체 비용 중 76%를 차지하고 있다는 점이다. 여러분의 쿼리에서 Sort의 비용이 전체 비용의 50% 이상을 차지하고 있다면 최적화의 여지가 있는지 주의깊게 살펴볼 필요가 있다.

 

다음을 생각해 보자.

• 정렬이 정말로 필요한가?
  만일 필요하지 않다면 정렬로 인한 오버헤드를 제거할 수 있을 것이다.
• 우리가 원하는 순서대로 이미 데이터가 정렬되어 있으면 어떨까?
  예를 들어, 우리가 원하는 정렬 순서대로 Clustered Index를 만들면 Clustered Index의 특성상 정렬 연산이 필요 없어질 것이다. 하지만 이 방법은 항상 사용 가능한 방법은 아니다. 이유는 간단하다. Clustered Index는 테이블 당 하나만 만들 수 있기 때문이다.
• 정렬할 데이터가 적으면 정렬 비용도 적게 들텐데...
  조건절에서 정렬할 데이터를 제한하면 정렬 비용도 당연히 줄어들 것이다. 예제의 쿼리에서 WHERE [ProductID] = 1의 조건을 통해 선행 집합을 3행으로 줄이면, 정렬할 데이터도 3행으로 줄어들게 되어 정렬 비용도 감소하게 될 것이다.
• 만일 실행 계획 내에 복수의 Sort 연산이 나타난다면...
  그 정렬들이 다 필요한 것인지 또는, 정렬을 줄일 수 있도록 쿼리 튜닝이 가능한지 살펴보자.

여기서 잠깐 두 번째 고려 사항을 증명해 보겠다.

예제의 쿼리를 아래와 같이 바꾸면

 

SELECT * FROM [Production].[ProductInventory] ORDER BY [ProductID]

 

다음과 같은 실행 계획을 얻을 수 있다.

앞서 작성한 쿼리에서 달라진 부분은 ORDER BY절의 정렬 컬럼이 [Shelf]에서 [ProductID]로 바뀐 것 뿐이다.

 

[그림 3] Clustered Index를 이용하여 Sort 연산 제거하기

 

눈치챘는가? 실행 계획에서 Sort 연산이 사라졌다!

왜냐하면 정렬 기준인 [ProductID]컬럼이 Clustered Index([ProductID] + [LocationID]) 내에 포함되어 있고, 데이터는 이미 [ProductID] + [LocationID] 순으로 정렬되어 있기 때문이다. 쿼리 옵티마이저는 데이터가 이미 정렬되어 있음을 알고 있을 만큼 영리한 것이다. 한 번 쓰다듬어 주자 ^^;

 

만일 어쩔 수 없이 대량의 데이터를 정렬해야 하는 경우가 발생한다면, SQL Server Profiler를 사용하여 Sort Warning 이 벤트가 표시되는지 반드시 확인할 필요가 있다. SQL Server는 정렬의 성능을 높이기 위해 디스크보다는, 그보다 훨씬 빠른 메모리를 사용하려 한다. 그러나 정렬하고자 하는 데이터가 메모리가 감당할 수 없을 정도로 터무니 없이 크면, 어쩔 수 없이 디스크 즉, tempdb 데이터베이스를 이용해서 정렬한다. 아무튼 디스크 정렬이 발생하면 SQL Server는 Sort Warning 이벤트를 쓰는데, 여러분의 서버 내에서 정렬 작업이 자주 수행되고 있고 Sort Warning 이벤트가 많이 발생되고 있는 것이 확인되면, 메모리를 증설하거나 tempdb의 읽기 쓰기 속도를 높이는 방안을 모색해야 한다.

 

 

 

Hash Match(Aggregate)

우리는 앞서 9. 그래픽 실행 계획의 기초 - Table Joins에서 Hash Match 연산을 살펴본 바 있다. Hash Match연산은 비단 조인에서 뿐만 아니라 단일 쿼리에서도 발생한다. 자 그럼, COUNT 연산자를 사용해서 하나의 쿼리를 집계하는 쿼리를 가정해 보도록 하자.

 

SELECT [City], COUNT([City]) AS CityCount FROM [Person].[Address] GROUP BY [City]

 

[그림 4] Hash Match (Aggregate)

 

쿼리 옵티마이저는 먼저 Index Scan 연산을 시작한다. 왜냐하면 우리가 원하는 집계 데이터를 얻기 위해서는 테이블 내의 모든 행을 필요로 하기 때문이다. 행을 제한하는 그 어떤 조건문도 없다. 그런 다음 선행 연산에서 공급받은 행들에 대해 요청받은 COUNT 집계 연산을 수행한다. 쿼리 옵티마이저가 도시별로 등록된 주소 수를 집계하기 위해서는 반드시 Hash Match 연산을 수행해야만 한다. 여기서 Hash Match 연산자 아래에 괄호로 (Aggregate) 라고 표기되어 있음을 주목하기 바란다. 이런 식으로 조인에서의 Hash Match 연산과 구별하고 있다. Hash Match Join에서와 마찬가지로, SQL Server는 GROUP BY 컬럼에 해당되는 행 수를 집계하기 위해 메모리상에 Hash Table을 생성한다. 여기서는 [City] 컬럼이다. 집계가 완료되면 그 결과를 우리에게 반환한다.

 

정렬과 마찬가지로 집계는 상당히 비용이 비싼 연산이다. 이 경우 성능 개선을 위해 WHERE절에서 집계할 집합을 충분히 줄여서 집계 처리 비용을 줄이는 방법 밖에 없다.

 

 

 

Filter

위에서 작성한 쿼리에 다음과 같이 간단한 HAVING절을 추가해 보도록 하자.

 

SELECT [City], COUNT([City]) AS CityCount FROM [Person].[Address] GROUP BY [City] HAVING COUNT([City]) > 1

 

[그림 5] Filter 연산

 

HAVING절을 추가함에 따라 실행 계획 내에 Filter 연산자도 함께 추가되었다. Filter 연산자는 우리가 공급한 조건 즉 [City]컬럼을 집계한 행 수가 1이상인 것만 걸러낸다. HAVING절의 내용은 COUNT 집계 연산이 완료되기 전까지 적용되지 않는다. 즉, HAVING절은 집계에 아무런 영향을 주지 못하며 집계가 끝나고 나서야 그 내용이 적용된다. 이는 Hash Match 연산자와 Filter 연산자의 ToolTip을 보면 금방 확인할 수 있다. Hash Match 연산 결과 575 행이, Filter 연산 결과 348행이 반환되고 있다.

 

[그림 6] Filter연산과 Hash Match 연산의 ToolTip

 

결과적으로 HAVING절을 추가함으로써 반환되는 결과 집합은 줄일 수 있으나 반대로, 원하는 결과 집합을 생성하기 위한 추가적인 비용(Filter 연산)이 발생한다. 간단하게 이야기해서 성능에 좋지 않다는 것이다.

 

 

다음 이야기 : 12. 그래픽 실행 계획의 기초 - Rebinds와 Rewinds