6. 실행 계획 기초 - SQL Server Profiler로 실행 계획 캡처하기

SQL Server Profiler로 실행 계획 캡처하기

 

지금까지 우리는 실행 계획을 캡처하는 몇 가지 방법들을 살펴보았다. 이 방법들은 모두 실행 계획 캡처 활성화 > 쿼리 실행 > 실행 계획 캡처 비활성화 의 순으로 실행 계획을 캡처할 수 있도록 하고 있다.

 

하지만, 이미 서비스되고 있는 서버나 테스트 서버에서 문제해결(troubleshooting)를 하는 경우라면 이야기는 달라진다. 서버에는 수십 개에서 수 백 개의 세션이 물려있을 것이고, 다양한 파라미터와 다양한 실행 계획을 가진 수많은 쿼리가 처리되고 있기 때문에, 각각의 쿼리들에 대해 실행 계획 캡처 활성화 > 쿼리 실행 > 실행 계획 비활성화 한다는 것은 무모하다고 하지 않을 수 없다. 이 경우에는 자동으로 실행 계획을 캡처해야 할 필요가 있는데, SQL Server 2005 이상에서는 SQL Server Profiler를 이용해서 실행되고 있는 쿼리의 XML 실행 계획을 자동으로 캡처할 수 있는 기능을 제공하고 있다. 이렇게 수집된 실행 계획을 바탕으로 고비용의 쿼리에는 어떤 것들이 있는지, 혹은 단순히 Table scan을 하는 쿼리는 어떤 것이 있는지 등 여러가지 조사를 할 수 있다.

 

SQL Server Profiler는 SQL Server내에서 발생되고 있는 T-SQL이나 저장 프로시저와 같은 여러가지 이벤트에 대해 데이터를 수집할 수 있는 강력한 도구이다. Profiler는 GUI 인터페이스를 통해 수동으로 이벤트를 추적(이하 trace 혹은 추적)하거나, T-SQL이나 GUI 인터페이스를 통해 추적 내용을 정의하고 특정 시간이나 기간 내에 자동으로 수집하도록 할 수도 있다.

뿐만 아니라 이렇게 수집된 추적은 화면 창에 보여주거나 파일이나 테이블에 떨어뜨릴 수 있다.  Profiler에 대한 내용은 본 포스트의 주제에서 벗어나는 내용이므로, BOL의 SQL Server 프로파일러 참조 를 참조하기 바란다.

 

 

 

실행 계획 이벤트

 

추적에 포함할 수 있는 실행 계획 이벤트에는 다음과 같은 것들이 있다.

• Showplan Text : 실행되는 쿼리 각각에 대해 이벤트를 추적하여 SHOWPLAN_TEXT T-SQL문과 같은 방법으로 예상 실행 계획을 생성한다. Showplan XML에서 얻을 수 있는 정보의 일부분만 보여지므로 상대적으로 얻을 수 있는 정보가 빈약하다. 텍스트 실행 계획의 단점에 대해서는 이미 살펴본 바 있으니 참고하기 바라며, 이 이벤트는 다음 버전에서 제거될 예정이다.
• Showplan Text (unencoded) : 바이너리가 아닌 스트링으로 정보를 보여주는 것 이외에는 Showplan Text와 같다. 이 이벤트 역시 다음 버전에서 제거될 예정이다.
• Showplan All : 실행되는 쿼리 각각에 대해 이벤트를 추적하여 SHOWPLAN_ALL T-SQL문과 같은 방법으로 예상 실행 계획을 생성한다. Showplan Text와 같은 단점을 가지고 있다. 이 이벤트 역시 앞으로 제거될 예정이다.
• Showplan All for Query Compile : Showplan All 이벤트와 같은 데이터를 생성한다. 그러나 쿼리가 컴파일될 때만 이벤트가 발생한다. 이 이벤트도 앞으로 제거될 예정이다.
• Showplan Statistics Profile : STATISTICS PROFILE T-SQL문과 같은 방법으로 실질 실행 계획을 생성한다. 이것 역시 텍스트 결과의 단점을 가지고 있으며, STATISTICS XML문이나 Showplan XML Statistics Profile 이벤트에서 얻을 수 있는 데이터의 일부만 포함하고 있다. 이 이벤트도 앞으로 제거될 예정이다.
• Showplan XML : 실행되는 쿼리 각각에 대해 이벤트를 추적하여 SHOWPLAN_XML과 같은 방법으로 예상 실행 계획을 생성한다.
• Showplan XML For Query Compile : 컴파일되는 쿼리만 대상으로 한다는 것 이외에는 Showplan XML과 같다.
• Performance Statistics : Showplan XML For Query Compile과 유사하나, 이 이벤트는 실행 계획 뿐만 아니라 말 그대로 쿼리의 성능 메트릭을 함께 캡처한다는 점이 다르다. 이 이벤트는 실행 계획이 처음으로 캐시되거나, 처음으로 컴파일 혹은 재컴파일되는 경우, 또는 캐시에서 제거될 경우 발생한다.
• Showplan XML Statistics Profile : 이 이벤트는 각각의 쿼리에 대해 실질 실행 계획을 생성한다.

당연한 이야기이겠지만 Showplan XML 또는 Showplan XML Statistics Profile을 사용하여 모든 실행 계획을 캡처하게 되면, 서버에 상당한 부하를 주게 되므로 서비스 중인 서버에서는 특히 주의해야 한다.

 

 

Showplan XML Trace

 

SQL Server Profiler의 Showplan XML 이벤트는 쿼리를 실행할 때 쿼리 옵티마이저에 의해 사용된 XML 실행 계획을 캡처한다. SQL Server Profiler로 XML 실행 계획을 캡처하기 위해서는 다음과 같은 절차를 따른다.

1. SQL Server Profiler에 접근하는 방법이 여러가지 있겠지만 여기서는, 시작(Start) 메뉴 > 모든 프로그램(All Programs) > MS SQL Server 2005(혹은 2008) > 성능도구(Performance Tools) > SQL Server 프로파일러(SQL Server Profiler) 를 클릭하여 SQL Server Profiler 를 기동한다.
2. 파일(File) 메뉴 > 새 추적(New Trace) 를 선택한 후, 추적하고자 하는 데이터베이스에 연결한다.
3. 프로파일러 속성(Profiler Properties) 대화 상자에서 이벤트 선택(Event Selection) 탭으로 이동하여, 모든 이벤트 보기(Show all events) 체크 박스에 체크한다.
4. Showplan XML 이벤트는 성능(Perpormance) 섹션에 있다. 성능(Perpormance) 섹션에서 플러스(+) 사인을 클릭하면 해당 섹션이 확장되고 우리가 추적하고자 하는 Showplan XML 이벤트가 보일 것이다. 이 섹션에는 이 밖에도 RPC:Completed, SQL:BatchStarting, SQL:BatchCompleted 와 같은 이벤트도 있는데 Showplan XML 이벤트와 함께 추적하면 더욱 유용하다. 예를 들어, 해당 이벤트 바로 전후에 어떤 일들이 일어났는지 확인할 수 있다면 많은 도움이 될 것이다.
   
   

   

   
   

   [그림 1] 추적 속성 대화 상자 - 이벤트 선택 탭
   
   

5. Showplan XML을 선택하거나, 혹은 다른 XML 이벤트를 선택하면 이벤트 추출 설정(Events Extraction Settings) 이라는 세번째 탭에 나타날 것이다. 이 탭에서는 추적한 XML 실행 계획 이벤트를 별도의 파일로 저장할 수 있다. 또한 모든 XML 실행 계획을 단일 파일에 저장하거나, 배치별로 개별 파일로 저장할 수도 있다.
   
   

   

   
   

   [그림 2] 추적 속성 대화 상자 - 이벤트 추출 설정 탭
   
   

6. 실행(Run) 버튼을 클릭하면 아래의 예제 그림처럼 추적이 시작된다.
   
   

   

   
   

   [그림 3] 추적 실행 예
   

Showplan XML 이벤트를 클릭하면 TextData 컬럼에 XML 실행 계획 코드가 나타나고, 아래의 두번째 창에 그래픽 실행 계획이 나타남을 확인할 수 있을 것이다. 이 때 마우스 오른쪽 버튼을 클릭해서 나타나는 메뉴인 이벤트 데이터 추출(Extract Event Data) 를 선택함으로써 Showplan XML 이벤트를 별도의 파일로 저장할 수 있다.

 

[그림 4] 이벤트 데이터 추출

 

대화상자가 나타나면 XML 코드를 저장하고자 하는 경로와 파일 명을 입력하고 저장한다. 전형적인 XML확장자로 저장하는 대신에 .sqlplan 확장자로 저장해도 된다. .sqlplan 확장자로 저장한 경우, 탐색기 내에서 저장된 파일을 더블 클릭하면 SSMS에 그래픽 실행 계획으로 보여진다.

 

 

요약

 

우리는 지금까지 실행 계획 기초 시리즈를 통해 먼저, 쿼리가 실행될 때 어떤 일이 발생하는지 SQL Server DBMS Engine의 메카니즘을 살펴보았고, 또 이런 과정이 예상 실행 계획과 실질 실행 계획에서 어떻게 표현되는지도 살펴보았다. 또한 텍스트 실행 계획, 그래픽 실행 계획, 그리고 XML 실행 계획의 각각의 특성과 장단점 및 실행 계획을 취득하는 방법도 함께 살펴보았다. 그래픽 실행 계획이든 XML 실행 계획이든 우리가 취득하고자 하는 실행 계획 데이터는 모두 취득할 수 있다. 하지만 어떤 방식을 사용하고 어떻게 활용할지는 여러분의 몫이다.

 

 

 

다음 이야기 - 7. 그래픽 실행 계획의 기초 - 그래픽 실행 계획의 언어