SQL Server 2000 세미나 Profiler를 이용한 문제해결

Presentation on theme: "SQL Server 2000 세미나 Profiler를 이용한 문제해결"— Presentation transcript:

1 SQL Server 2000 세미나 Profiler를 이용한 문제해결
강사: 정원혁

2 차 례 문제 해결 기법 프로필러가 뭘까? 추적 만들기 추적 분석하기 추적 플래그 사용하기 추적 재생하기 문제 해결 기법
데이터의 확보 병모가지 정확히 찾기 Devide & conquer 숫자 맞추기 게임 프로필러가 뭘까? 서버 및 데이터베이스 동작(예를 들어, 교착 상태 수, 치명적인 오류, 저장 프로시저 및 Transact-SQL 문 추적, 로그인 동작)을 모니터링할 수 있습니다. SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해 나중에 분석할 때 사용할 수 있으며 SQL Server에 캡처한 이벤트를 단계별로 재생해 발생한 이벤트를 정확하게 확인할 수도 있습니다. SQL 프로필러는 일괄 처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적합니다. 추적 만들기 템플릿 파일/ 테이블 저장 이벤트 필터 걸기 추적 분석하기 파일/ 테이블 활용 엑셀 연동 추적 플래그 사용하기 추적 재생하기 사례

3 차 례 문제 해결 기법 프로필러가 뭘까? 추적 만들기 추적 분석하기 추적 플래그 사용하기 추적 재생하기 문제 해결 기법
데이터의 확보 병모가지 정확히 찾기 Devide & conquer 숫자 맞추기 게임 프로필러가 뭘까? 서버 및 데이터베이스 동작(예를 들어, 교착 상태 수, 치명적인 오류, 저장 프로시저 및 Transact-SQL 문 추적, 로그인 동작)을 모니터링할 수 있습니다. SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해 나중에 분석할 때 사용할 수 있으며 SQL Server에 캡처한 이벤트를 단계별로 재생해 발생한 이벤트를 정확하게 확인할 수도 있습니다. SQL 프로필러는 일괄 처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적합니다. 추적 만들기 템플릿 파일/ 테이블 저장 이벤트 필터 걸기 추적 분석하기 파일/ 테이블 활용 엑셀 연동 추적 플래그 사용하기 추적 재생하기 사례

4 문제 해결 기법 데이터의 확보 병모가지 정확히 찾기 Devide & conquer -숫자 맞추기 게임
자기만의 trouble shooting guide 만들기

5 차 례 문제 해결 기법 프로필러가 뭘까? 추적 만들기 추적 분석하기 추적 플래그 사용하기 추적 재생하기 문제 해결 기법
데이터의 확보 병모가지 정확히 찾기 Devide & conquer 숫자 맞추기 게임 프로필러가 뭘까? 서버 및 데이터베이스 동작(예를 들어, 교착 상태 수, 치명적인 오류, 저장 프로시저 및 Transact-SQL 문 추적, 로그인 동작)을 모니터링할 수 있습니다. SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해 나중에 분석할 때 사용할 수 있으며 SQL Server에 캡처한 이벤트를 단계별로 재생해 발생한 이벤트를 정확하게 확인할 수도 있습니다. SQL 프로필러는 일괄 처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적합니다. 추적 만들기 템플릿 파일/ 테이블 저장 이벤트 필터 걸기 추적 분석하기 파일/ 테이블 활용 엑셀 연동 추적 플래그 사용하기 추적 재생하기 사례

6 프로필러 서버 및 데이터베이스 동작 모니터 - 교착 상태 수, 치명적인 오류, 저장 프로시저 및 Transact-SQL 문 추적, 로그인 동작 SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해서 분석가능 캡처한 이벤트를 단계별로 재생 - 문제 해결 - 부하 걸기 시뮬레이션 SQL 프로필러는 일괄 처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적

7 차 례 문제 해결 기법 프로필러가 뭘까? 추적 만들기 추적 분석하기 추적 플래그 사용하기 추적 재생하기 추적 만들기 템플릿
파일/ 테이블 저장 이벤트 필터 걸기 추적 분석하기 파일/ 테이블 활용 엑셀 연동 추적 플래그 사용하기 추적 재생하기 사례

8 템플릿 어떤 것(이벤트, 데이터, 필터)을 추적 할 지 미리 정의해 둔 것 나만의 템플릿을 만들어 둘 수도 있다.
기본 템플릿 그대로 사용하는 것은 과부하를 걸 수 있다.

9 파일/ 테이블 저장 추적을 실행하는 서버가 추적 데이터를 처리하도록 지정합니다. 이 옵션을 선택하면 스트레스 상태에서 어떤 이벤트도 건너뛰지 않습니다. 그러나 추적될 이벤트 수에 따라 서버 성능이 영향을 받을 수도 있습니다. 이 확인란을 선택 해제하면 클라이언트 응용 프로그램이 처리를 수행하게 되고 몇몇 이벤트는 스트레스 상태에서 추적되지 않을 수 있습니다.

10 파일/ 테이블 저장 파일 기본 5MB 단위로 롤 오버 (최대 1GB) trace1.trc , trace2.trc, ….
서버에서 처리 테이블 저장 주의 / 대신 나중에 fn_trace_gettable() 를 이용하여 테이블로 변경 시작 / 종료 시간 저장 안 하면… 추적을 실행하는 서버가 추적 데이터를 처리하도록 지정합니다. 이 옵션을 선택하면 스트레스 상태에서 어떤 이벤트도 건너뛰지 않습니다. 그러나 추적될 이벤트 수에 따라 서버 성능이 영향을 받을 수도 있습니다. 이 확인란을 선택 해제하면 클라이언트 응용 프로그램이 처리를 수행하게 되고 몇몇 이벤트는 스트레스 상태에서 추적되지 않을 수 있습니다.

11 이벤트 제외: 보안 감사 / 세션 RPC completed TSQL Batch completed
ExistingConnection RPC completed TSQL Batch completed 경고 Attention Exception Execution Warnings Execution Warnings 이벤트 클래스를 모니터링하면 쿼리가 리소스를 기다려야 하는지 여부와 기다리는 시간을 확인할 수 있습니다. 이것은 시스템에 성능에 영향을 줄 수 있는 충돌 문제가 존재해 조사가 필요한지 여부를 확인하는데 중요합니다. Locks 이벤트 클래스를 사용하면 영향을 받는 개체를 확인할 수 있습니다.

12 이벤트 잠금 Lock:Deadlock Lock:Deadlock Chain Lock:Timeout 성능
Execution Plan Show Plan All 프로시저 SP:CacheHit SP: ExecContextHit SP:Recompile Execution Warnings 이벤트 클래스를 모니터링하면 쿼리가 리소스를 기다려야 하는지 여부와 기다리는 시간을 확인할 수 있습니다. 이것은 시스템에 성능에 영향을 줄 수 있는 충돌 문제가 존재해 조사가 필요한지 여부를 확인하는데 중요합니다. Locks 이벤트 클래스를 사용하면 영향을 받는 개체를 확인할 수 있습니다.

13 이벤트 트랜잭션 SQLTransaction TSQL Exec Prepared SQL Prepare SQL
해당 이벤트를 가져와야 특정 정보가 나타난다. Execution Warnings 이벤트 클래스를 모니터링하면 쿼리가 리소스를 기다려야 하는지 여부와 기다리는 시간을 확인할 수 있습니다. 이것은 시스템에 성능에 영향을 줄 수 있는 충돌 문제가 존재해 조사가 필요한지 여부를 확인하는데 중요합니다. Locks 이벤트 클래스를 사용하면 영향을 받는 개체를 확인할 수 있습니다.

14 데이터 열 EventClass CPU / Duration Reads/ Writes TextData
ClientProcessID / SPID Binary Data Application Name /Host Name Database ID Grouping Application Name 110SQL Server 인스턴스와의 연결을 설정한 클라이언트 응용 프로그램 이름입니다. 이 열은 프로그램의 표시 이름이 아니라 응용 프로그램에서 전달한 값으로 채워집니다. Binary Data2추적에서 캡처된 이벤트 클래스에 의존하는 이진 값입니다. ClientProcessID19클라이언트 응용 프로그램이 실행 중인 프로세스에 대해 호스트 컴퓨터가 할당한 ID입니다. 클라이언트가 클라이언트 프로세스 ID를 제공하면 이 데이터 열이 채워집니다. Column Permissions44열 사용 권한이 설정되었는지 나타냅니다. 명령문 텍스트를 구문 분석하여 어떤 명령이 어떤 열에 적용되었는지 알 수 있습니다. CPU18이벤트에 의해 소요된 밀리초 단위의 CPU 경과 시간입니다. Database ID13주어진 연결에 대해 USE database 문이 생성되지 않은 경우 USE database 문이나 사용자의 기본 데이터베이스에서 지정한 데이터베이스 ID입니다. Server Name 데이터 열이 추적에서 캡처되고 서버를 사용할 수 있으면 SQL 프로필러에는 데이터베이스 이름이 나타납니다. DB_ID 함수를 사용하여 데이터베이스에 대한 값을 결정합니다. DatabaseName35사용자 명령문이 실행되는 데이터베이스 이름입니다. DBUserName140클라이언트의 SQL Server 사용자 이름입니다.Duration13이벤트에 의해 소요된 밀리초 단위의 경과 시간입니다. End Time 15이벤트가 종료된 시간입니다. 이 열은 SQL:BatchStarting이나 SP:Starting 등의 시작하는 이벤트를 참조하는 이벤트 클래스에 채워지지 않습니다. Error31주어진 이벤트의 오류 수입니다. 종종 sysmessages에 저장된 오류 수를 나타냅니다.EventClass127캡처된 이벤트 클래스 유형입니다. EventSubClass121각 이벤트 클래스에 대한 자세한 정보를 제공하는 이벤트 하위 클래스 유형입니다. 예를 들어 실행 경고 이벤트 클래스에 대한 이벤트 하위 클래스 값은 실행 경고 유형을 나타냅니다. 1 = 쿼리 대기. 쿼리는 실행하기 전에 리소스(예를 들어, 메모리)를 기다려야 합니다. 2 = 쿼리 시간 제한. 실행을 위해 리소스를 기다리는 동안 제한 시간을 초과한 쿼리입니다. 이 데이터 열은 모든 이벤트 클래스에 대해 채워지지는 않습니다. FileName36수정되는 파일의 논리적 이름입니다. Handle33ODBC, OLE DB, DB-Library가 서버와의 공동 실행을 위해 사용하는 정수입니다. Host Name18클라이언트를 실행 중인 컴퓨터 이름입니다. 클라이언트가 호스트 이름을 제공하면 이 데이터 열이 채워집니다. 호스트 이름을 확인하려면 HOST_NAME 함수를 사용하십시오.Index ID24이벤트에 의해 영향 받는 개체의 인덱스 ID입니다. 개체의 인덱스 ID를 확인하려면 sysindexes 시스템 테이블의 indid 열을 사용하십시오. Integer Data25추적에서 캡처된 이벤트 클래스에 의존하는 이진 값입니다. LoginName11사용자 로그인 이름(DOMAIN\Username 형식의 Microsoft Windows® 로그인 신임장 또는 SQL Server 보안 로그인)입니다.LoginSid141로그인한 사용자의 보안 ID(SID)입니다. 이 정보는 master 데이터베이스의 syslogins 테이블에 있습니다. 각 SID는 서버의 각 로그인마다 고유합니다. Mode32여러 가지 이벤트에서 이벤트의 수신 또는 요청 상태를 설명할 때 사용하는 정수입니다. 반환한 데이터를 나타내는 정수입니다. NT Domain Name17사용자가 속한 Microsoft Windows NT® 4.0 또는 Windows 2000도메인입니다. NT User Name16Windows NT 4.0 또는 Windows 2000 사용자 이름입니다.Object ID22시스템이 할당한 개체의 ID입니다.ObjectName34참조되는 개체 이름입니다.ObjectType28이벤트와 관련된 개체 유형을 나타내는 값입니다. 이 값은 sysobjects의 type 열에 해당합니다.Owner Name37개체 소유자의 데이터베이스 사용자 이름입니다.Permissions19확인한 사용 권한의 유형을 나타내는 정수 값입니다. 다음과 같은 값이 올 수 있습니다. 1 = SELECT ALL 2 = UPDATE ALL 4 = REFERENCES ALL 8 = INSERT 16 = DELETE 32 = EXECUTE (프로시저만) 4096 = SELECT ANY (최소한 1 열) 8192 = UPDATE ANY = REFERENCES ANY Reads16이벤트 대신 서버에서 수행한 논리적 디스크 읽기 수입니다.RoleName38활성화된 응용 프로그램 역할 이름입니다.Server Name126추적 중인 SQL Server 인스턴스의 이름입니다.Severity20예외의 심각도입니다. SPID112SQL Server가 클라이언트와 관련된 프로세스에 대해 할당한 프로세스 ID입니다.Start Time114사용 가능한 경우 이벤트가 시작된 시간입니다.State30오류 상태 코드입니다.Success23이벤트가 성공적이었는지를 나타냅니다. 다음과 같은 값이 올 수 있습니다. 1 = 성공 0 = 실패 예를 들어, 1은 사용 권한 확인에 성공했음을 의미하며 0는 확인에 실패했음을 의미합니다.TargetLoginName42데이터베이스 사용자를 대상으로 하는 작업(예: 사용자에게 사용 권한 허가)의 경우 해당 사용자의 이름입니다.TargetLoginSid43로그인을 대상으로 하는 작업(예: 새 로그인 추가)의 경우 대상 로그인의 SID입니다.TargetUserName39데이터베이스 사용자를 대상으로 하는 작업(예: 사용자에게 사용 권한 허가)의 경우 해당 사용자의 이름입니다.TextData1추적에서 캡처된 이벤트 클래스에 의존하는 텍스트 값입니다. 그렇지만 매개 변수화된 쿼리를 추적하면 변수는 TextData 열에 데이터 값으로 표시되지 않습니다.Transaction ID4시스템이 할당한 트랜잭션 ID입니다.Writes17이벤트 대신 서버에서 수행한 물리적 디스크 쓰기 수입니다

15 필터 걸기 Min, max 값 지정 가능 (duration, cpu, reads등에 대해)

16 차 례 문제 해결 기법 프로필러가 뭘까? 추적 만들기 추적 분석하기 추적 플래그 사용하기 추적 재생하기 문제 해결 기법
데이터의 확보 병모가지 정확히 찾기 Devide & conquer 숫자 맞추기 게임 프로필러가 뭘까? 서버 및 데이터베이스 동작(예를 들어, 교착 상태 수, 치명적인 오류, 저장 프로시저 및 Transact-SQL 문 추적, 로그인 동작)을 모니터링할 수 있습니다. SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해 나중에 분석할 때 사용할 수 있으며 SQL Server에 캡처한 이벤트를 단계별로 재생해 발생한 이벤트를 정확하게 확인할 수도 있습니다. SQL 프로필러는 일괄 처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적합니다. 추적 만들기 템플릿 파일/ 테이블 저장 이벤트 필터 걸기 추적 분석하기 파일/ 테이블 활용 엑셀 연동 추적 플래그 사용하기 추적 재생하기 사례

17 추적 분석 By duration/ CPU/ reads / writes By user Find

18 추적 분석 테이블로 저장 select * into tr1a from tr1 alter table tr1a
add sql varchar(3000) update tr1a set sql = convert(varchar(3000), textdata) drop column textdata select sql, duration, cpu, reads, writes from tr1a order by sql select convert(varchar(80), sql), avg(duration) dr, avg(cpu) c, avg(reads) r, avg(writes ) w group by convert(varchar(80), sql)

19 차 례 문제 해결 기법 프로필러가 뭘까? 추적 만들기 추적 분석하기 추적 플래그 사용하기 추적 재생하기 문제 해결 기법
데이터의 확보 병모가지 정확히 찾기 Devide & conquer 숫자 맞추기 게임 프로필러가 뭘까? 서버 및 데이터베이스 동작(예를 들어, 교착 상태 수, 치명적인 오류, 저장 프로시저 및 Transact-SQL 문 추적, 로그인 동작)을 모니터링할 수 있습니다. SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해 나중에 분석할 때 사용할 수 있으며 SQL Server에 캡처한 이벤트를 단계별로 재생해 발생한 이벤트를 정확하게 확인할 수도 있습니다. SQL 프로필러는 일괄 처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적합니다. 추적 만들기 템플릿 파일/ 테이블 저장 이벤트 필터 걸기 추적 분석하기 파일/ 테이블 활용 엑셀 연동 추적 플래그 사용하기 추적 재생하기 사례

20 dbcc traceon() 추적 플래그 설정된 모든 연결에 적용 부작용 주의 DBCC TRACEOFF TRACESTATUS
1204 / 1205 3604 / 3605 8602 / 모든 인덱스 힌트의 사용을 무시 8755 / 모든 잠금 힌트를 무시 8722 / 모든 종류의 힌트(OPTION절에서 사용되는 힌트들) 무시 모든 인덱스 힌트의 사용을 무시 8755 플래그는 모든 잠금 힌트를 무효화 시키고, 8722 플래그는 다른 모든 종류의 힌트(OPTION절에서 사용되는 힌트들)를 무효화 시킨다.

21 차 례 문제 해결 기법 프로필러가 뭘까? 추적 만들기 추적 분석하기 추적 플래그 사용하기 추적 재생하기 문제 해결 기법
데이터의 확보 병모가지 정확히 찾기 Devide & conquer 숫자 맞추기 게임 프로필러가 뭘까? 서버 및 데이터베이스 동작(예를 들어, 교착 상태 수, 치명적인 오류, 저장 프로시저 및 Transact-SQL 문 추적, 로그인 동작)을 모니터링할 수 있습니다. SQL 프로필러 데이터를 SQL Server 테이블이나 파일에 캡처해 나중에 분석할 때 사용할 수 있으며 SQL Server에 캡처한 이벤트를 단계별로 재생해 발생한 이벤트를 정확하게 확인할 수도 있습니다. SQL 프로필러는 일괄 처리나 트랜잭션 시작과 같은 엔진 프로세스 이벤트를 추적합니다. 추적 만들기 템플릿 파일/ 테이블 저장 이벤트 필터 걸기 추적 분석하기 파일/ 테이블 활용 엑셀 연동 추적 플래그 사용하기 추적 재생하기 사례

22 추적 재생 Stpe by step 한꺼번에 (부하 걸기) 실제로 수행
db context / login, user context/ password/ permission 등이 맞아야 제대로 재생

23 추적 재생 SQLProfilerTSQL_Replay 템플릿 사용 재생 결과를 표시 : 안 하면 재생 속도 더 빠르다
추적 파일 / 추적 테이블 모두 재생 가능