SQL Server Profiler(SSP), SQL 프로파일러 사용 방법

 안녕하세요? 일주일만에 티스토링을 올립니다. 이번에는 Microsoft SQL Server Profiler(SSP)에 대해서 알아 보겠습니다. Microsoft SQL Server Profiler(SSP)는 SQL Server 설치 파일(DVD)의 관리도구에 포함이 되어 있습니다. 관리도구만 설치 가능하도록 따로 구성된 파일(DVD)도 있습니다.

 

 저는 이번에 출시된 SQL Server 2014의 SSP를 설치하였습니다. 드림스파크(DreamSpark)에서 관리도구만 다운로드를 하여 설치를 하였습니다. 마이크로소프트 다운로드 센터에서도 무료도 다운 받으실 수 있습니다.

 

 

 

마이크로소프트 다운로드 센터 : Microsoft SQL Server 2014 Express 

드림스파크(DreamSpark) : SQL Server 2014

 

 

 관리도구는 SQL Server Management Studio(SSMS)를 설치하는 방법과 동일합니다. SSMS 설치 방법과 SQL Server 2014를 무료로 다운로드 할 수 있는 방법은 아래 링크에서 확인할 수 있습니다.

 

SQL Server 2014 다운로드 및 설치

SQL Server Management Studio(SSMS) 설치하기

 

 

 SSP를 설치를 하셨으면 실행을 합니다. 아래 그림과 같은 바로가기가 있으실 겁니다.

 

 

 

 SSP를 실행 하는 방법은 위 그림처럼 바로가기를 실행을 할 수 있지만 다른 방법도 있습니다. 대부분은 SQL Server Management Studio(SSMS) 사용 중에 SSP를 실행해야 되는 경우가 많습니다. SSMS에서 SSP를 실행 하기 위해서는 새 쿼리 창에 커서가 위치해 있는 상태에서 상단 메뉴에 쿼리(Q) -> SQL Server Profiler에서 쿼리 추적(T)을 실행 하시면 됩니다. 실행을 하면 바로 쿼리 추적이 실행이 됩니다. 단축키는 Ctrl + Alt + P 입니다.

 

 

 

 저는 SSP 단축 아이콘으로 실행을 해보겠습니다. 실행을 하시면 아래 그림과 같이 서버에 연결 창이 뜹니다. 서버 이름(S), 인즌(A), 로그인(L), 암호(P)를 입력하고 연결(C) 버튼을 클릭하여 연결을 합니다.

 

 

 

 저는 사용방법을 알려 드리기 위해서 모든 설정을 해보는 걸로 진행을 하겠습니다. 기능들을 익히시고 필요할때 적용을 하시면 됩니다.

 

 추적 속성 창이 뜹니다. 일반 탭에서는 추적에 대한 템플릿을 지정할 수 있습니다. 추적할 조건이나 추적 내용에 대해서 다양하게 지정을 할 수 있습니다. 때문에 본인이 템플릿을 만들어 두고 사용하시면 됩니다. 저는 템플릿 사용(U)를 기본으로 Standard (기본값)으로 으로 했습니다. 파일에 저장(S)을 선택하여 추적내용을 파일로 저장하겠습니다.

 

 

 

 테이블에 저장(B)을 선택하시면 서버에 연결 창이 뜨는데 추적내용을 데이터베이스(DataBase) 테이블로 저장할 수 있습니다. 연결(C) 버튼을 클릭하여 연결 합니다. 서버에 연결을 하면 대상 테이블 창이 뜹니다. 데이터베이스(D), 스키마(S)를 선택하고 테이블(T)은 기존 테이블을 사용하거나 신규로 생성할 테이블 명을 입력하고 확인 버튼을 클릭합니다.

 

 

 

 

 다시 추적 속성 화면에서 이벤트 선택 탭으로 이동합니다. 템플릿을 Standard (기본값)으로 선택했기 때문에 추척할 이벤트가 많이 선택되어 있습니다. 많은 이벤트를 선택하면 시스템에 부하을 일으킬 수 있어서 필요한 이벤트만 선택하시기 바랍니다. 저는 Stored Procedures, TSQL만 선택을 했습니다. 체크박스를 선택하거나 해제하여 이벤트 열을 추가하거나 제거를 할 수 있습니다.

 

 

 

 연결된 SQL Server에서 처리되는 내용이 많으면 추척내용이 엄청나게 올라옵니다. 그래서 필터 기능을 적용하여 본인이 원하는 항목만 필터링을 할 수 있습니다. 오른쪽 아래에 열 필터(F) 버튼을 클릭하여 지정하시면 됩니다.

 

 필터 편집 창이 뜹니다. 왼쪽에는 컬럼 항목이 뜨는데 해당 컬럼에서 생성되는 데디터에서 어떤 값과 유사하거나 유사하지 않은 데이터를 필터링 할 수 있습니다. 저는 왼쪽에 TextData를 선택하고 유사 항목에는 %VERSION%, 유사하지 않음 항목에는 %GETDATE%를 입력하였습니다(% 표시는 LIKE 조건이 적용 됩니다). 확인 버튼을 클릭합니다.

 

 

 

 아래 그림과 같은 경고 창이 뜹니다. 그 이유는 이벤트 선택에서 제가 TextData 항목을 선택하지 않았기 때문입니다. 예(Y) 버튼을 클릭하여 이벤트 열을 추가합니다.

 

 

 

 

 

 추적 속성 창에서 실행 버튼을 눌러 추적을 시작합니다. 추적이 잘 진행이 되는지 확인을 해보겠습니다. SSMS를 실행하여 추적 진행 중인 서버로 접속합니다. 새 쿼리 창에서 우선 SELECT @@VERSION 을 실행합니다. 그다음으로 SELECT GETDATE()를 실행합니다. 그리고 SSP(SQL 프로파일러)를 확인해 보겠습니다.

 

 

 

 

 SQL 프로파일러(SSP)에는 SELECT @@VERSION만 추적이 나타나는 것을 확인 할 수 있습니다. 필터링이 잘되는 것을 확인 할 수 있습니다.

 

 앞에서 추적 속성 창에서 추적내용을 파일과 데이터베이스 테이블에 저장을 했습니다. 저장한 파일을 찾아서 실행을 하시면 SQL 프로파일러(SSP)가 하나 더 실행이 되고 추적한 결과가 화면에 나타납니다.

 

 

 

 이번에는 SSMS로 추적한 내용을 저장한 데이터베이스로 접속하여 조회를 해보시면 아래와 같이 추적내용이 저장된 것을 확인 할 수 있습니다.

 

 

 

 추척한 내용을 데이터베이스 테이블로 저장을 하시면 쿼리문을 사용하여 찾기가 한결 편리합니다.

 

 SQL 프로파일러(SSP)를 24시간 모니터링을 하는 것은 권장하지 않습니다. 필요할 경우에 추적하시기를 바랍니다. SSP를 이용하여 아래과 같은 경우에 사용을 하시면 도움이 되실 겁니다.

 

- 오래 실행되는 SQL 문 찾기

권장 추적 이벤트 : TSQL, SQL:BatchCompleted

정렬 기준 이벤트 열 : Duration

 

- 과도한 자원 사용자 찾기

권장 추적 이벤트 : TSQL, SQL:BatchCompleted

정렬 기준 이벤트 열 : CPU, Reads, Writes

 

- 교착 상태 알아내기 : 처리에 따라서 교착이 될수도 있고 아닐 수도 있음

권장 추적 이벤트

1. TSQL, SQL:BatchStarting 동작하는 SQL 일괄 처리(batch)

2. Locks, Lock:Deadlock 교착 상태 자체의 이벤트

3. Locks, Lock:Deadlock Chain 교착 상태에 이르는 이벤트 순서