3. 실행 계획 기초 - 그래픽 실행 계획 사용하기

본 포스트의 내용은 MS SQL Server2005 이상에 국한된 내용입니다. 퍼가실 때는 덧글을 달아주시는 센스를...   그래픽 실행 계획 사용하기   먼저 예상 실행 계획과 실질 실행 계획을 표시하는 방법을 살펴보도록 하겠다. MS SQL Server Management Studio를 열고 쿼리 창에서 아래의 스크립트를 작성하자.   SELECT * FROM [dbo].[DatabaseLog];       예상 실행 계획(Estimated Execution Plan) 표시하기   그래픽 예상 실행 계획을 표시하기 위해 실제로 쿼리를 실행할 필요는 없다. 아래의 방법 중 하나를 통해 예상 실행 계획을 표시할 수 있다. 툴바의 "예상 실행 계획 표시(Display Estimated Execution Plan)"를 클릭한다. 쿼리 창에서 마우스 오른쪽 버튼을 클릭 후 나타난 메뉴에서 "예상 실행 계획 표시(Display Estimated Execution Plan)"를 선택한다. "쿼리" 메뉴에서 "예상 실행 계획 표시(Display Estimated Execution Plan)"를 선택한다. CTRL + L 단축키를 사용한다. 아래는 위에서 작성한 T-SQL의 예상 실행 계획을 표시한 예이다.     [그림 1] 예상 실행 계획       실질 실행 계획(Actual Execution Plan) 표시하기   실질 실행 계획은 예상 실행 계획과 달리 쿼리 옵티마이저가 계산한 결과를 보여주는 것이 아니라, 쿼리가 실행되었을 때 어떤 일이 발생했는지는 보여준다. 이 두 가지는 때때로 다르게 보여지기도 하는데, 이는 storage engine이 만들어진 실행 계획을 변경했기 때문이다.   아래는 실질 실행 계획을 표시하는 방법이다. 툴바에서 "실질 실행 계획 포함(Include Actual Execution Plan)"를 클릭한다. 쿼리 창에서 마우스 오른쪽 버튼을 클릭 후 나타난 메뉴에서 "실질 실행 계획 포함(Include Actual Execution Plan)"를 선택한다. "쿼리" 메뉴에서 "실질 실행 계획 포함(Include Actual Execution Plan)"를 선택한다. CTRL + M 단축키를 사용한다. 이 옵션들을 ON 설정하면 강제로 OFF를 하기 전까지 계속된다.   아래는 위에서 작성한 T-SQL의 실질 실행 계획을 표시한 예이다.   [그림 2] 실질 실행 계획       그래픽 실행 계획 해석하기   SQL Server의 실행계획에는 약 78개의 실행 계획 연산자(Showplan operator)가 있다. 그 중에서 가장 기본적인 2개의 연산자를 그림 1, 2을 통해 먼저 살펴보기로 하자. 왼쪽에 SELECT 아이콘(혹은 노드)이 있다. 이는 relational engine으로 부터 반환된 최종 결과를 나타낸다. 그리고 오른쪽에는 Table Scan 이라는 아이콘이 있다. Table Scan에 대해서는 다시 설명하겠지만, 간단히 설명하자면 storage engine이 전체 테이블을 순차적으로 쭉 읽어내는 것을 의미한다. 예제의 경우 WHERE 조건도 없고 Covering Index가 존재하지 않기 때문에 Table Scan이 나타난 것이다.   ☞ Covering Index : 쿼리를 충족시키는데 필요한 모든 컬럼들이 포함되어 있는 Nonclustered Index를 의미한다. Covering Index의 예 - 쿼리 포함되어 있는 col2, col1, col3 가 모두 인덱스에 포함되어 있다. select col1, col3 from Table1 where col2 = 'value'; create Index Indexname1 on Table1 (col2, col1, col3);   그래픽 실행 계획을 읽는 순서는  "오른쪽에서 왼쪽으로, 위에서 아래로" 이다. 이 순서로 읽어가다 보면 두 아이콘 사이에 화살표가 있음을 확인 할 수 있을 것이다. 화살표는 아이콘으로 표시된 실행 계획 연산자의 실행 순서 혹은 데이터의 흐름을 의미한다. 따라서, 예제의 실행계획은 "Table Scan을 통해 얻은 최종 결과를 표시한다" 라고 해석할 수 있다. 화살표의 두께는 데이터량과 상관관계가 있다. 화살표가 두께가 두꺼우면 두꺼울수록 통과하는 데이터가 많다는 것을 의미한다. 마우스 포인터를 화살표 위로 가져가면 화살표를 통과하는 행 수가 표시된다. 예를 들어 쿼리를 실행해서 얻어지는 최종 결과는 2행이지만, 실행 계획에서 두꺼운 화살표가 표시되어 많은 행을 처리함이 보여진다면 조사해 볼 여지가 있는 것이다.   각각의 아이콘 아래에는 퍼센트가 표시되어 있다. 이 숫자는 각 실행 계획 연산자의 상대 비용을 나타낸다. 예제의 경우, SELECT는 0%, Table Scan은 100% 로써 Table Scan에 모든 비용이 소요되었다. 하지만 이는 비율을 의미하지 실제 숫자를 의미하지 않음을 명심해야 한다. SELECT가 0%라고 해서 SELECT 연산을 수행하는데 든 비용이 0인 것은 아니다.   아이콘 위 쪽에는 "SELECT * FROM [dbo].[DatabaseLog];" 라고 쿼리 스트링이 표시되어 있다. 그리고 그 위로는 "cost (relative to batch): 100%" 라고 표시되어 있다. 만일, 하나의 배치 안에 복수 개의 쿼리가 실행된다면, "cost (relative to batch): xxx%" 부분에는 "cost (relative to batch)" 라고 표시되어 있듯이 배치 내의 쿼리 간의 상대 비용이 표시된다.     ToolTips(도구 설명)   각 아이콘이나 화살표 위로 마우스 포인터를 옮기면 ToolTip라 불리는 팝업 원도우가 나타난다.   먼저, 예제의 SELECT 실행 계획 연산자 위로 마우스 포인터를 옮겨보도록 하자. 아마 아래와 같은 ToolTip 윈도우가 표시될 것이다.   [그림 3] SELECT 실행 계획 연산자 Cached Plan size - 해당 쿼리가 실행 계획을 생성하기 위해 Plan Cache에서 얼마만큼의 메모리를 사용하였는지를 보여준다. 어떤 실행계획이 보다 메모리를 많이 사용하는지 확인할 수 있기 때문에, 캐시 성능을 조사할 때 유용한 수치로 활용할 수 있다. Estimated Operator Cost (예상 비용) - 쿼리 옵티마이저가 해당 연산을 실행하기 위한 비용이다. 이는 총 쿼리 비용의 백분율로 표시되며, 이는 이미 [그림 1]에서 확인하였다. Estimated Subtree Cost (예상 하위 트리 비용) - 쿼리 옵티마이저가 해당 연산과 이전의 모든 연산을 실행하기 위한 비용의 누계이다. 시간 비용의 누계로 표시된다. Estimated number of rows (예상 행 수) - 연산자에서 생성한 행 수이다. 아래는 Table Scan 연산자에 마우스를 가져다 놓았을 때 나타나는 ToolTip이다.   [그림 4] Table Scan 실행 계획 연산자   SELECT 실행 계획 연산자과 사뭇 다른 모습을 보여준다. 좀 더 많은 정보가 보여지는데 위에서 부터 하나 하나 살펴보기로 하자.   먼저, 맨 위에 나와 있는 Physical Operation (물리적 연산)과 Logical Operation (논리적 연산)을 설명하자면, Logical Operation (논리적 연산) 은 쿼리를 실행했을 때 어떤 일이 발생할지 쿼리 옵티마이저가 연산한 결과를 논리적으로 표현한 것이고, Physical Operation (물리적 연산)은 실제로 어떤 연산을 수행하는지 보여준다. 일반적으로 Logical Operation (논리적 연산)과 Logical Operation (논리적 연산)는 같지만 항상 그런 것은 아니다.   그 아래로 Estimated I/O Cost (예상 I/O 비용), Estimated CPU Cost (예상 CPU 비용), Estimated Operator Cost (예상 비용), Estimated Subtree Cost (예상 하위 트리 비용) 가 보인다. 여기서 Subtree(하위 트리)란 해당 실행 연산자의 화살표를 역추적했을 때 나타나는 모든 실행 노드(혹은 연산자)의 가지를 의미한다. 예를 들어 노드 B의 Subtree는 노드 D + 노드 E + 노드 H 이다. [그림 5] 하위 트리(Subtree)   Estimated I/O Cost (예상 I/O 비용)와 Estimated CPU Cost (예상 CPU 비용)는 연산자가 아니다. 이는 쿼리 옵티마이저가 비용 계산을 수행하는 동안 할당된 비용 수치이다. 이 숫자가 크면 클수록 보다 많은 처리를 한다는 것을 의미한다. 따라서, 우리는 이 수치를 통해 대부분의 비용이 I/O에서 발생하는지(예제가 그러하다), 혹은 CPU에 부하가 발생하는지 예측할 수 있다. 다시 한 번 말하지만 이 숫자는 절대적인 수치가 아니라 해당 노드에서 실제 비용이 어디에서 발생하는지 측정하는데 도움을 주는 조언자라고 생각하는 것이 옳다.   예제에서는 Table Scan 연산 밖에 없기 때문에 Estimated Operator Cost (예상 비용)와 Estimated Subtree Cost (예상 하위 트리 비용)가 같다. 하지만 보다 많은 연산자가 나타나는 복잡한 실행 계획 트리에서는 Estimated Subtree Cost (예상 하위 트리 비용)이 각 하위 트리에서 발생한 비용의 누계로 나타난다. 실행 계획의 총 비용은 마지막 노드(연산자)에서 확인할 수 있다. 여기서는 SELECT 연산자이다.   Estimated number of rows (예상 행 수)는 각 노드별로 표시된다. 왜냐하면 각 노드별로 처리하는 데이터가 다르기 때문이다. 보다 복잡한 쿼리에서는 각 노드 통과하는 동안 다양한 연산자가 처리를 수행함에 따라 이 수치가 바뀌는 것을 쉽게 확인할 수 있다. 우리는 이 수치를 통해 쿼리가 실행되는 동안 각각의 노드에서 행이 어떻게 추가되고 걸러지는지 알 수 있다.   또 하나의 중요한 정보로 Ordered (순서)가 있는데 불리언(Boolean) 값으로 표현된다. 이는 성능 튜닝시 중요한 이슈인데, 해당 노드에서 데이터를 정렬하느냐 안 하느냐를 표시한다. 예를 들어 SELECT 구문에 ORDER BY 구를 사용했을 경우 ORDER BY에 나열된 특별한 순서로 정렬한 필요가 있다. 이 때 Ordered (순서) 상태 값을 통해 어떤 노드에서 정렬이 발생하는지 확인할 수 있다.   마지막으로 Node ID (노드 ID)가 있다. 이는 단순히 각 노드의 순서를 의미한다. 사실 이 값은 실행 계획을 읽는 순서대로 오른쪽에서 왼쪽으로 값이 매겨지는 것이 타당하겠지만, (재미있다고 해야 하나?) 애석하게도 왼쪽에서 오른쪽으로 값이 매겨진다.   이상 설명한 모든 내용은 여러분이 쿼리를 실행할 때 어떤 일이 발생하는지 이해하는데 큰 도움을 줄 것이다. 하위 트리에서 비용이 어떻게 산출되었는지, 각 노드별로 행 수가 어떻게 바뀌는 지 등을 다양한 연산자를 통해 관찰할 수 있을 것이며, 또한 얼마만큼 CPU를 사용하는지, 테이블에 인덱스가 더 필요한지 혹은 필요없는지 등 여러가지 튜닝의 요소를 판단하는 근거로 사용할 수 있다.     Operator Properties(그래픽 실행 계획 노드 속성)   ToolTip보다 더 상세한 정보를 확인하는 방법이 있다. 그래픽 실행 계획내의 특정 아이콘을 마우스 오른쪽 버튼 클릭을 하면 나타나는 "Properties(속성)" 메뉴를 선택하면 해당 연산자와 관련된 상세 정보가 나타난다.   아래는 Table Scan의 등록정보를 표시한 내용이다.   [그림 6] Table Scan의 그래픽 실행 계획 노드 속성   몇 가지 새로운 정보를 제외하고는 ToolTip에 나타난 내용과 비슷하다. 그럼 새로운 정보에 대해 위에서 부터 하나씩 살펴보도록 하자. 먼저 Defined Values 라는 것이 보인다. Defined Values는 해 당 연산자가 추가한 프로세스 정보를 표시한다. 이는 기본 쿼리의 일부분이 될 수도 있고 - 예제의 경우 SELECT된 컬럼이다 -, 참조 무결성을 확인하기 위한 플래그로 사용될 수 있으며, 또는 집계 함수를 위한 자리 표시자( 개체 틀)가 될 수도 있다.   Defined Values 속성 밑으로 연산자에 대한 설명과 예상 비용에 관련된 아래와 같은 내용을 추가적으로 볼 수 있다. Estimated Rebinds & Estimated Rewind - 이 값은 실행계획 내에서 Init() 연산자가 얼마나 호출되었는지 나타내는 수치이다. Forced Index - 이 값이 True라면 특정 인덱스 사용을 강제하도록 쿼리 힌트(Query hint)를 사용했음을 의미한다. SQL Server는 쿼리의 실행을 어느 정도 제어할 수 있도록 쿼리 힌트(Query hint)로써 그 기능을 제공하는데, 이는 나중에 다시 살펴보도록 하겠다. NoExpandHint - 어떤 의미에서는 Forced Index와 같은 컨셉이지만 indexed view에는 적용되지 않는다.     다음 이야기 : 4. 실행 계획 기초 - 텍스트 실행 계획 사용하기