트레이스, 프로파일러

트레이스

트레이스(추적) : 실행되는 SQL문의 실행 통계를 세션별로 모아서 만든 파일

  ※ 트레이스의 아키텍처 : 이벤트가 발생하면 각 트레이스에서 설정한 필터를 거친 후 FILE 또는 SMO 형식으로 저장

트레이스의 주요 용어

이름	설명
이벤트	데이터베이스 엔진 인스턴스 내에서 동작이 발생했음을 의미
데이터 열	이벤트의 특성
이벤트 클래스	추적할 수 있는 이벤트의 유형. 이벤트에서 보고할 수 있는 모든 데이터 열을 포함
이벤트 범주	서로 연관성이 있는 이벤트 클래스들의 그룹
추적(명사)	DB 엔진에서 반환한 이벤트 및 데이터의 모음
추적(동사)	인스턴스의 이벤트를 수집 및 모니터링 하기 위한 동작
Tracedefinition	추적 중에 수집되는 이벤트의 유형을 식별하는 이벤트 클래스. 데이터 열 및 필터 모음
필터	추적에서 이벤트를 제한하는 조건
추적 테이블	프로파일러에서 추적을 테이블에 저장할 때 생성되는 테이블

주요 데이터 열 반환 설명

이름	설명
ApplicationName	SQL Server 인스턴스에 연결한 클라이언트 애플리케이션의 이름
Binary Data	추적에서 캡처된 이벤트 클래스에 따라 달라지는 이진 값
ClientProcessID	클라이언트 애플리케이션이 실행 중인 프로세스에 대해 호스트 컴퓨터가 할당한 ID
CPU	이벤트에 의해 사용된 CPU 시간 (밀리 초 단위)
Duration	이벤트의 기간 (마이크로 초 단위)
EndTime	이벤트가 종료된 시간
EventClass	캡처된 이벤트 클래스 유형
LoginName	사용자 로그인 이름 (EX GFZ\minjae93)
NTUserName	Windows 사용자 이름
ObjectType	이벤트와 관련된 개체 유형을 나타내는 값 (sysobject의 유형 열과 일치)
Reads	이벤트에 대해 서버에서 수행한 논리적 디스크 읽기 작업의 수
SPID	SQL Server가 클라이언트와 연결된 프로세스에 할당한 SPID
StartTime	이벤트가 시작된 시간 (사용 가능한 경우)
TextData	추적에서 캡처된 이벤트 클래스에 따라 달라지는 텍스트 값 (쿼리문)
Writes	이벤트에 대해 서버에서 수행한 물리적 디스크 쓰기 작업의 수
Host Name	클라이언트를 실행 중인 컴퓨터의 이름
Database	주어진 연결에 대한 현재 데이트베이스의 ID

트레이스 최적화 방안

1.  CMD를 활용
2.  자주 발생하는 이벤트를 포함하지 않는다.
3.  관련 데이터를 제공하는 이벤트만 수집
4.  중복 데이터를 수집하지 않는다.
5.  추적 정의에 필터를 사용

성능에 크게 영향을 미치는 이벤트를 추적해야 할 경우

1.  단기간 추적 실행
2.  추적 결과의 크기 제한
3.  반환되는 이벤트의 수를 제한

※ 프로파일러 사용시에 종료 시간, 파일 저장 시 파일 크기를 반드시 적용해주기.

SQL Server Profiler

프로파일러 : 추적을 작성 및 관리하고 추적 결과를 분석 및 재생하기 위한 인터페이스. 이벤트는 추적 파일에 저장되며 이 파일은 나중에 분석되거나 문제를 진단할 때 특정 단계를 다시 수행하기 위해 사용할 수 있다.

주 사용 용도

1.  문제가 발생한 원인을 찾기 위해 문제 쿼리 실행
2.  실행이 느린 쿼리를 찾고 진단
3.  문제가 발생한 일련의 T-SQL문 포착 → 저장된 트레이스를 사용하여 문제를 진단할 수 있는 테스트 서버에서 문제 복제 가능
4.  SQL Server의 성능을 모니터링하여 튜닝.
5.  문제 진단을 위해 성능 카운터의 상관 관계 지정

 

문제점 확인 예시

1. 인덱스 관련 문제점
   추적 결과에서 Reads와 Duration의 값이 많이 크면 인덱스와 관련된 문제일 가능성이 있음
2.  블로킹 (Lock들의 경합)
   Reads나 CPU는 작으면서 Duration 값이 많이 크다면 블로킹일 가능성이 있음.

 

CMD를 사용하여 프로파일러 실행 시 주요 명령어

 

Name	description
/?	profiler 인수의 구문 요약 정보를 표시합니다
/U login_id	SQL Server 인증을 위한 사용자 로그인 ID입니다. 로그인 ID는 대/소문자를 구분합니다.
/P password	SQL Server 인증을 위한 암호를 지정합니다. 사용자가 원하는 대로 지정할 수 있습니다.
/E	현재 사용자의 자격 증명으로 Windows 인증을 사용하는 연결을 지정합니다.
/S sql_server_name	SQL Server인스턴스를 지정합니다.
/A analysis_services_server_name	Analysis Services 인스턴스를 지정합니다.
/D database	연결에 사용할 데이터베이스 이름을 지정합니다.  (디폴트 값 : 사용자의 기본 db. ex : dbo)
/B "trace_table_name"	프로파일러를 시작할 때 로드할 추적 테이블을 지정합니다.
/T "template_name"	추적을 구성하기 위해 로드할 템플릿을 지정합니다. 파일 확장명(.tdf)을 포함X
/F "filename"	프로파일러를 시작할 때 로드할 추적 파일의 경로와 이름을 지정합니다.
/O "filename"	추적 결과를 기록할 파일의 경로와 이름을 지정합니다.
/M " MM-DD-YY hh:mm:ss "	추적을 중지할 날짜와 시간을 지정합니다.
/R	추적 파일 롤오버를 사용합니다.
/Z file_size	추적 파일 크기(MB)를 지정합니다. (디폴트 : 5MB)