1. 개요
서버 및 데이터베이스의 동작을 모니터링하고 튜닝하는 도구로서 교착상태, 치명적인 오류, 저장 프로시저와 Transact-SQL문, 로그인 동작, 일괄 처리, 트랜잭션 시작과 같은 엔진 프로세스 이벤트등을 추적하여 데이터를 테이블이나 파일로 저장할 수 있다.
2. 템플릿
어떤 이벤트의 어떤 항목들을 추적할것인지 미리 정의해 둔것으로서 기본 템플릿을 그대로 사용하면 과부하가 걸릴수 있다.
3. 저장
추적한 데이터는 테이블, 파일로 저장할 수 있다.
추적한 데이터가 많을때는 기본 설정(5MB)가 부족함으로 필요한 만큼 늘려줘야 한다.
데이터를 파일로 저장시 파일 롤오버 옵션을 사용하면 저장되는 파일의 확장자가 tr1, tr2, trc3... 형식으로 하드가 꽉 찰때까지 저장되기 때문에 주의해야한다.
추적 중지 시간 설정을 권장한다.
데이터를 테이블로 저장시 서버에 이중으로 부하가 발생함으로 파일로 저장하는것을 권장한다. 파일로 저장된 데이터를 fn_trace_gettable로 테이블에 저장할수 있다.
EX)
SELECT IDENTITY(int, 1, 1) AS RowNumber, *
INTO #temp
FROM ::fn_trace_gettable('trc 파일', DEFAULT);
파일, 테이블에 저장하지 않고 추적을 할경우 메모리상에 추적 데이터가 저장되며 메모리가 꽉차면 추적을 중지한다.
4. 이벤트
Standard(기본값)을 선택시 Audit Login, Audit Logout, ExistingConnection, RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting가 선택된다.
Audit Login, Audit Logout : SQL Server에 로그인/로그아웃시 발생, 거의 추적안함
ExistingConnection : 블로킹등 특수한 경우외에 사용안함
RPC:Completed : 클라언트에서 SQL Server로 호출한 프로시저를 종료할 때 발생
SQL:BatchCompleted : 일괄처리 종료시 발생
SP:StmtCompleted : 저장 프로시저안의 명령문들을 문장단위로 추적, 특별한 경우를 제외하고 사용안함
5. 오류 및 경고
Attention, ErrorLog, EventLog, Exception, Execution Warnings, Hash Warring, Missing Column Statistics, Missing Join Predicate, OLEDB Errors, Sort Warnings 이벤트 클래스가 있다.
Attention : 클라이언트 인터럽트 요청, 클라이언트 연결 끊어짐의 주의를 요하는 이벤트
Execution Warnings : 명령문이나 저장 프로시저 실행 중에 발생하는 경고 이벤트
Exceptoin : 가장 흔히 발생하는 각종 오류로 인해 발생하는 이벤트
6. 잠금
Lock:acquired, Lock:Cancel, Lock:Deadlock, Lock:Deadlock Chain, Lock:Escalation, Lock:Released, Lock:Timeout 이벤트 클래스가 있다.
Lock:Deadlock Chain : 교착상태가 어떤 개체와 관련되어 있는지 확인하는 이벤트
Lock:Deadlock : 잠금을 얻을려고 시도하는 동안 교착상태가 발생할하여 잠금을 얻을려고 하는 시도가 취소될때 발생하는 이벤트
Lock:Timeout : SET LOCK_TIMEOUT문으로 설정해놓은 잠금 시간 만료가 되었을때 발생하는 이벤트
위의 세가지 이외의 이벤트 클래스들은 특별한 경우가 아닌 한 사용하지 않는것이 좋다.
※ 실제 서버에서 프로필러로 교착을 추적하기 보다는 추적 플래그 1204를 이용하여 추적하는것을 권장한다.
7. 트랜잭션
DTCTransaction, SQLTransaction, TransactionLog 이벤트 클래스가 있다.
SQLTransaction : 트랜잭션의 시작, 커밋, 롤백, 저장등과 관련된 이벤트
8. 데이터 열
자주 사용하는 데이터 열
EventClass : 캡쳐된 이벤트 클래스를 보여주며, 이 데이터열은 제거할 수 없다.
CPU : 이벤트에 의해 소요된 CPU 사용 시간(밀리 초 단위)
Duration : 이벤트에 의해 소요된 경과 시간(밀리 초 단위)
Reads : 이벤트에 의해 서버에서 수행한 논리적 디스크 읽기수(페이지 단위)
Writes : 이벤트에 의해 서버에서 수행한 문리적 디스크 쓰기수(페이지 단위)
TextData : 캡처된 이벤트 클래스에서 보내진 텍스트 값으로, 실행되는 T-SQL문, 저장 프로시저 또는 일괄 처리의 텍스트를 보여준다.
Binary Data : 캡처된 이벤트 클래스에서 보내진 바이너리 값으로, 해당 이벤트 클래스에서 데이터 열로 요구할때만 추가하면 된다.
ClientProcessID : 호스트 컴퓨터가 클라이언트 응용 프로그램이 실행되고 있는 프로세스에 대해 할당한 ID
SPID : SQL Server가 클라이언트와 관련된 프로세스에 대해 할당한 고유한 ID
50이하는 시스템, 51이상은 사용자가 사용
Application Name : SQL Server 인스턴스와 연결을 설정한 클라이언트 응용 프로그램의 이름
Host Name : 클라이언트를 실행중인 컴퓨터의 이름
Database ID : 주어진 연결에 대한 현재 데이터베이스의 ID
추적 분석을 할 때 Duration, CPU, Reads, Writes, SPID 또는 ClientProcessID 그룹화하면 좋다.
9. 필터
추적할 데이터 열에 조건을 부여하여 추적 대상을 필터링할 수 있도록 해준다.
10. 사용자 정의 템플릿 만들기
1) 프로파일러에서 파일 -> 새로 만들기 -> 추적 템플릿
2) 추적 템플릿 속성 대화상자의 이벤트 페이지에서 다음과 같은 이벤트 클래스를 선택
* 저장 프로시저 - RPC:Completed
* TSQL - SQL:BatchCompleted
3) 데이터 열 페이지에서 다음과 같은 데이터 열들을 선택한다.
* Event Class
* Duration
* CPU
* Reads
* Writes
* TextData,
* ApplicationName
* DatabaseID
* ClientProcessID
* HostName
* SPID
* StartTime
4) 필터 페이지에서 다음과 같이 추적 이벤트 조건을 설정한다.
* ApplicationName -> 유사하지 않음 -> SQL Profiler
5) 일반 페이지에서 다른 이름으로 저장 버튼을 누른다.
※ 2000 기준이면 2005는 약간 틀리다.(많이 귀찮다..)
11. 추적 결과 분석
추적 결과 데이터를 테이블에 입력해서 추적 결과를 분석할 수 있다.
1) 인덱스 관련 문제 찾기
Reads, Duration 값이 많이 클경우 인덱스와 관련된 문제일 가능성이 크다.
Reads > 10000 AND Duration > 10000 ORDER BY Reads DESC
2) 블로킹 찾기
Reads나 CPU값은 작으면서 Duration 값이 많이 크다면 블로킹일 가능성이 크다.
Duration > 10000 AND reads < 1000 ORDER BY Duration DESC
12. 추적 재생
추적한 결과를 재실행함으로써 오류나 버그의 재현, 디버깅 등을 가능하게 해줍니다.
추적 재생을 하기 위한 최초 추적을 하려면 프로필러 템필릇으로는 반드시 SQLProfilerTSQL_Replay를 사용하여야 한다.
서버 및 데이터베이스의 동작을 모니터링하고 튜닝하는 도구로서 교착상태, 치명적인 오류, 저장 프로시저와 Transact-SQL문, 로그인 동작, 일괄 처리, 트랜잭션 시작과 같은 엔진 프로세스 이벤트등을 추적하여 데이터를 테이블이나 파일로 저장할 수 있다.
2. 템플릿
어떤 이벤트의 어떤 항목들을 추적할것인지 미리 정의해 둔것으로서 기본 템플릿을 그대로 사용하면 과부하가 걸릴수 있다.
3. 저장
추적한 데이터는 테이블, 파일로 저장할 수 있다.
추적한 데이터가 많을때는 기본 설정(5MB)가 부족함으로 필요한 만큼 늘려줘야 한다.
데이터를 파일로 저장시 파일 롤오버 옵션을 사용하면 저장되는 파일의 확장자가 tr1, tr2, trc3... 형식으로 하드가 꽉 찰때까지 저장되기 때문에 주의해야한다.
추적 중지 시간 설정을 권장한다.
데이터를 테이블로 저장시 서버에 이중으로 부하가 발생함으로 파일로 저장하는것을 권장한다. 파일로 저장된 데이터를 fn_trace_gettable로 테이블에 저장할수 있다.
EX)
SELECT IDENTITY(int, 1, 1) AS RowNumber, *
INTO #temp
FROM ::fn_trace_gettable('trc 파일', DEFAULT);
파일, 테이블에 저장하지 않고 추적을 할경우 메모리상에 추적 데이터가 저장되며 메모리가 꽉차면 추적을 중지한다.
4. 이벤트
Standard(기본값)을 선택시 Audit Login, Audit Logout, ExistingConnection, RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting가 선택된다.
Audit Login, Audit Logout : SQL Server에 로그인/로그아웃시 발생, 거의 추적안함
ExistingConnection : 블로킹등 특수한 경우외에 사용안함
RPC:Completed : 클라언트에서 SQL Server로 호출한 프로시저를 종료할 때 발생
SQL:BatchCompleted : 일괄처리 종료시 발생
SP:StmtCompleted : 저장 프로시저안의 명령문들을 문장단위로 추적, 특별한 경우를 제외하고 사용안함
5. 오류 및 경고
Attention, ErrorLog, EventLog, Exception, Execution Warnings, Hash Warring, Missing Column Statistics, Missing Join Predicate, OLEDB Errors, Sort Warnings 이벤트 클래스가 있다.
Attention : 클라이언트 인터럽트 요청, 클라이언트 연결 끊어짐의 주의를 요하는 이벤트
Execution Warnings : 명령문이나 저장 프로시저 실행 중에 발생하는 경고 이벤트
Exceptoin : 가장 흔히 발생하는 각종 오류로 인해 발생하는 이벤트
6. 잠금
Lock:acquired, Lock:Cancel, Lock:Deadlock, Lock:Deadlock Chain, Lock:Escalation, Lock:Released, Lock:Timeout 이벤트 클래스가 있다.
Lock:Deadlock Chain : 교착상태가 어떤 개체와 관련되어 있는지 확인하는 이벤트
Lock:Deadlock : 잠금을 얻을려고 시도하는 동안 교착상태가 발생할하여 잠금을 얻을려고 하는 시도가 취소될때 발생하는 이벤트
Lock:Timeout : SET LOCK_TIMEOUT문으로 설정해놓은 잠금 시간 만료가 되었을때 발생하는 이벤트
위의 세가지 이외의 이벤트 클래스들은 특별한 경우가 아닌 한 사용하지 않는것이 좋다.
※ 실제 서버에서 프로필러로 교착을 추적하기 보다는 추적 플래그 1204를 이용하여 추적하는것을 권장한다.
7. 트랜잭션
DTCTransaction, SQLTransaction, TransactionLog 이벤트 클래스가 있다.
SQLTransaction : 트랜잭션의 시작, 커밋, 롤백, 저장등과 관련된 이벤트
8. 데이터 열
자주 사용하는 데이터 열
EventClass : 캡쳐된 이벤트 클래스를 보여주며, 이 데이터열은 제거할 수 없다.
CPU : 이벤트에 의해 소요된 CPU 사용 시간(밀리 초 단위)
Duration : 이벤트에 의해 소요된 경과 시간(밀리 초 단위)
Reads : 이벤트에 의해 서버에서 수행한 논리적 디스크 읽기수(페이지 단위)
Writes : 이벤트에 의해 서버에서 수행한 문리적 디스크 쓰기수(페이지 단위)
TextData : 캡처된 이벤트 클래스에서 보내진 텍스트 값으로, 실행되는 T-SQL문, 저장 프로시저 또는 일괄 처리의 텍스트를 보여준다.
Binary Data : 캡처된 이벤트 클래스에서 보내진 바이너리 값으로, 해당 이벤트 클래스에서 데이터 열로 요구할때만 추가하면 된다.
ClientProcessID : 호스트 컴퓨터가 클라이언트 응용 프로그램이 실행되고 있는 프로세스에 대해 할당한 ID
SPID : SQL Server가 클라이언트와 관련된 프로세스에 대해 할당한 고유한 ID
50이하는 시스템, 51이상은 사용자가 사용
Application Name : SQL Server 인스턴스와 연결을 설정한 클라이언트 응용 프로그램의 이름
Host Name : 클라이언트를 실행중인 컴퓨터의 이름
Database ID : 주어진 연결에 대한 현재 데이터베이스의 ID
추적 분석을 할 때 Duration, CPU, Reads, Writes, SPID 또는 ClientProcessID 그룹화하면 좋다.
9. 필터
추적할 데이터 열에 조건을 부여하여 추적 대상을 필터링할 수 있도록 해준다.
10. 사용자 정의 템플릿 만들기
1) 프로파일러에서 파일 -> 새로 만들기 -> 추적 템플릿
2) 추적 템플릿 속성 대화상자의 이벤트 페이지에서 다음과 같은 이벤트 클래스를 선택
* 저장 프로시저 - RPC:Completed
* TSQL - SQL:BatchCompleted
3) 데이터 열 페이지에서 다음과 같은 데이터 열들을 선택한다.
* Event Class
* Duration
* CPU
* Reads
* Writes
* TextData,
* ApplicationName
* DatabaseID
* ClientProcessID
* HostName
* SPID
* StartTime
4) 필터 페이지에서 다음과 같이 추적 이벤트 조건을 설정한다.
* ApplicationName -> 유사하지 않음 -> SQL Profiler
5) 일반 페이지에서 다른 이름으로 저장 버튼을 누른다.
※ 2000 기준이면 2005는 약간 틀리다.(많이 귀찮다..)
11. 추적 결과 분석
추적 결과 데이터를 테이블에 입력해서 추적 결과를 분석할 수 있다.
1) 인덱스 관련 문제 찾기
Reads, Duration 값이 많이 클경우 인덱스와 관련된 문제일 가능성이 크다.
Reads > 10000 AND Duration > 10000 ORDER BY Reads DESC
2) 블로킹 찾기
Reads나 CPU값은 작으면서 Duration 값이 많이 크다면 블로킹일 가능성이 크다.
Duration > 10000 AND reads < 1000 ORDER BY Duration DESC
12. 추적 재생
추적한 결과를 재실행함으로써 오류나 버그의 재현, 디버깅 등을 가능하게 해줍니다.
추적 재생을 하기 위한 최초 추적을 하려면 프로필러 템필릇으로는 반드시 SQLProfilerTSQL_Replay를 사용하여야 한다.
이올린에 북마크하기
