[MSSQL] 프로파일러(Profiler) 실행 & 쿼리 추적 사용법

MSSQL에는 실행되는 쿼리를 추적하는 공식 도구인 SQL Server Profiler라는 툴을 제공합니다.

SQL 튜닝을 위해서 문제가 되는 고부하 쿼리를 찾아 낼 때 유용한 툴인데요.

 

오늘은 이 툴인 SQL프로파일러를 실행해서 어떤 이벤트를 걸어서

내가 사용한 쿼리들이 프로파일러에서 어떻게 보이는지 확인하는 방법에 대해서 알아보겠습니다.

 

SQL 프로파일러 실행

SSMS를 실행한 다음 [도구]-[SQL Server Profiler]를 클릭합니다.
실행하자 로그인 창이 나오는데 관리자 계정인 sa 계정으로 로그인합니다.

 

 

수집할 이벤트 만들기

추적 속성을 설정하는 화면입니다.

[일반] 항목에서 "추적 이름"을 입력하고 "템플릿 사용"을 Standard(기본값)으로 설정합니다.

[이벤트 선택] 항목에서 아래의 2가지 이벤트를 선택하고 나머지는 해제합니다.
TSQL : SQL Batchcompleted
Stored Procedures : RPC Completed

만약 필터가 필요하면 하단에 "열 필터"를 선택합니다.
Duration으로 3초이상(1/1000) 돌아간 쿼리를 찾을 수 있습니다.

주의사항은 SQL Server Profiler에서는 밀리초 단위로 표시되어서 3초를 3000으로 입력해야 하고 서버에서 볼 때는 마이크로초 단위이기 때문에 3초는 3,000,000으로 생각해야 합니다.

또한 SSMS에서 새 쿼리 창 열면 옆에 있는 쿼리 창  ID 번호로 필터를 걸어 집중 분석을 할 수 있다.

 

프로파일러 쿼리 추적 예시

위의 항목 말고 추가적인 분석이 필요하면 각각 이벤트의 개념을 익혀서 체크하고 분석하면 됩니다.
아래는 많이 사용하는 추가적인 항목 이벤트와 개념을 적어 두었습니다.

 

다음과 같이 설정 후 프로 SQL 프로파일러를 실행한 다음 예시 쿼리들을 실행해 본다면 아래와 같은 결과가
프로파일러에 잡히는 것을 볼 수 있습니다.

모든 이벤트 표시 체크를 하고 5가지 이벤트를 체크하고 실행합니다.

TSQL
SQL StmtCompleted - Transact-SQL 문이 완료된 경우 발생합니다. 개별 쿼리 숫자 4개
SQL Batchcompleted - Transact-SQL 문이 완료된 경우 발생합니다. 쿼리 묶음(go로 구분된) 2개

Stored Procedures
RPC Completed : 원격 프로시저 호출이 완료된 경우 발생합니다.(닷넷에서 호출)
SP Completed : 저장 프로시저가 완료된 시기를 나타냅니다.
SP StmtCompleted : 저장 프로시저 내의 Transaction-SQL 문이 완료되었음을 나타냅니다.

 

아래의 예시 쿼리를 새 쿼리창에 입력하고 실행하면 실행된 쿼리들을 SQL Profiler에서 볼 수 있습니다.

예시 1)

--쿼리문
select 1+1
select 1+2
go
select 1+3
select 1+4

 

 

결과
SQL StmtCompleted 4개
SQL Batchcompleted 2개

상세분석
SQL: StmtCompleted        select 1+1
SQL: StmtCompleted        select 1+2
SQL: BatchCompleted        select 1+1 select 1+2
SQL: StmtCompleted        select 1+3
SQL: StmtCompleted        select 1+4
SQL: BatchCompleted        select 1+3 select 1+4

예시 2)

--쿼리문
create proc usp_test
@p int
as
begin
declare @num int =20
select 10 + @p + @num
select 30 + @p * @num
end
GO

exec usp_test @p= 100
GO

결과
SP:StmtCompleted 4개
SP:Completed 1개
SQL:StmtCompleted 1개
SQL:StmtCompleted 1개

상세분석
SP:StmtCompleted        declare @num int =20
SP:StmtCompleted        select 10 + @p + @num
SP:StmtCompleted        select 30 + @p * @num
SP:Completed                exec usp_test @p= 100
SQL:StmtCompleted        exec usp_test @p= 100
SQL:BatchCompleted        exec usp_test @p= 100

 

이상으로 SQL Server Profiler를 이용하여 내가 내가 실행한 쿼리를 추적하는 방법에 대해서 알아보았습니다.