|
[따라하기] 1. 다음 방법 중 하나를 이용하여 프로필러를 실행합니다.
[시작] → [프로그램] → [Microsoft SQL Server] → [프로필러] 또는 엔터프라이즈 관리자의 상단 메뉴에서 [도구] → [SQL 프로필러]를 선택합니다. 2. [파일] → [새로 만들기] → [추적]을 선택합니다.
3. 원하는 SQL 서버에 연결하면, [추적 속성]창이 나타납니다.
4. 추적이름을 입력합니다. 5. 추적할 SQL Server를 선택합니다. 6. 템플릿을 사용할 경우에 템플릿 이름을 선택합니다. 7. 파일에 저장하려면, [파일에 저장]을 선택하고, 저장할 위치와 파일명을 입력합니다. 8. 최대 파일 크기 설정을 합니다. 9. [이벤트] 탭을 선택한 후, 원하는 이벤트를 추가하거나, 제거합니다.
10. [데이터 열] 탭을 선택한 후, 수집할 데이터 열을 선택합니다.
11. 필터를 이용하고 싶다면, 필터 탭을 선택하여, 원하는 필터를 정의합니다. 예를 들어, LoginName이 Test인 것만 수집하고 싶다면 다음과 같이 설정합니다.
12. [실행]을 클릭하면, 수집이 시작됩니다.
13. 수집 중지를 위해서, 중지 버튼(붉은 네모)을 클릭합니다. | |
 | |
| 추적 수행하기 - SP 사용
[파일] → [추적 스크립팅]을 이용하면, 원하는 확장 프로시저를 생성할 수 있습니다. | |
|
[따라하기] A. 오래 실행되는 SQL 문 찾기 오래 실행되는 쿼리는 잘못 튜닝된 시스템, 잘못 작성된 응용 프로그램, 또는 단순히 많은 작을 수행하는 작업등을 의미할 수 있습니다. 어떠한 경우건, 이러한 오래 실행되는 SQL문을 찾아서 튜닝하는 것은 그 작업의 성능은 물론 전반적인 시스템 성능까지도 향상시킬 수 있습니다.
- 권장되는 추적 이벤트 : TSQL, SQL:BatchCompleted - 정렬 기준 컬럼 : Duration B. 과도한 자원 사용자 찾기 과도한 자원을 사용하는 응용 프로그램이나 사용자를 찾는 추적은 DBA에게 유용한 도구가 될 수 있습니다. 이러한 추적 유형은 CPU와 I/O 자원 모두를 많이 사용하는 SQL 문을 살펴야 합니다. 프로세스나 사용자를 식별하여, 응용 프로그램을 튜닝할 수 있습니다.
- 권장되는 추적 이벤트 : TSQL, - SQL:BatchCompleted - 정렬 기준 컬럼 : CPU, Reads, Writes C. 교착 상태 알아내기 사용자의 작업에 따라 교착상태는 시스템에서 문제가 될 수도 있고 또 그렇지 않을 수도 있습니다. 많은 경우에 있어 교착 상태는 심각한 문제일 수 있는데, 이 경우 원인을 알아내는 것은 성능을 향상시키는데 핵심이 됩니다. 그러나 이러한 이벤트를 프로파일 하는 것은 자원을 많이 사용하게 되므로 주의해야 합니다.
- 권장되는 추적 이벤트 TSQL, SQL:BatchStarting : 동작하는 SQL 일괄 처리(batch) Locks, Lock:Deadlock : 교착 상태 자체의 이벤트 Locks, Lock:Deadlock Chain : 교착 상태에 이르는 이벤트 순서 [참고] A. 불필요한 이벤트의 추가는 삼가합니다. 너무 많은 이벤트의 추가는 서버의 성능에 영향을 줄 수 있습니다. B. 일반적인 경우라면, 모든 컬럼들을 포함시킵니다. 어떤 이벤트들은 보조적인 항목을 반환하는 어떤 항목들에 의존합니다. 적어도 다음 컬럼들은 포함하는 것이 좋습니다. - BinaryData C. 테이블에 추적을 직접 저장하는 것은 좋지 않습니다. 추적 파일을 생성한 후, fn_trace_gettable 함수를 사용하면 추적 데이터를 테이블에 저장할 수 있습니다. | |
| |
| 추적에 관련된 저장 프로시저의 예제 스크립트 | |
|
다음은 추적 스크립팅을 저장 프로시저화한 예제 스크립트입니다. 시스템의 환경에 적합하도록 수 정 보완하여 활용하기 바랍니다. | |
■ 추적을 시작하는 저장 프로시저의 예제 스크립트 : sp_trace_start USE master
GO
CREATE PROCEDURE sp_trace_start @TraceFileName sysname=NULL,
@TraceName sysname='trace',
@Options int=2, -- TRACE_FILE_ROLLOVER
@MaxFileSize bigint=5,
@StopTime datetime=NULL,
@Events varchar(300)=
'10,12',
-- 10 - RPC:Completed
-- 12 - SQL:BatchCompleted
@Cols varchar(300)=
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2
9,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,',
-- 모든 컬럼
@IncludeFilter sysname=NULL,
@ExcludeFilter sysname=NULL
AS
SET NOCOUNT on
-- 변수 선언
DECLARE @TraceId int
DECLARE @On bit
DECLARE @rc int
SET @On=1
-- 이벤트와 컬럼을 확인한다.
IF @Events IS NULL or @Cols IS NULL BEGIN
PRINT 'No Events or Coloumns.'
RETURN -1
END
-- 파일경로와 파일명을 설정한다.
IF @TraceFileName IS NULL
SELECT @TraceFileName = 'c:\Trace\trace_' + CONVERT(CHAR(8),getdate(),112)
-- 추적 큐를 만든다
EXEC @rc =sp_trace_create @TraceId OUT, @Options, @TraceFileName,
@MaxFileSize, @StopTime
IF @rc<>0 BEGIN
PRINT 'Trace not started.'
RETURN @rc
END
PRINT 'Trace started.'
PRINT 'The trace file name is '+@TraceFileName+'.'
-- 추적할 이벤트 클래스들과 컬럼들을 지정한다
DECLARE @i int, @j int, @Event int, @Col int, @Colstring varchar(300)
IF RIGHT(@Events,1)<>',' SET @Events=@Events+','
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @Colstring=@Cols
IF RIGHT(@Colstring,1)<>',' SET @Colstring=@Colstring+','
SET @j=CHARINDEX(',',@Colstring)
WHILE @j<>0 BEGIN
SET @Col=CAST(LEFT(@Colstring,@j-1) AS int)
EXEC sp_trace_setevent @TraceId, @Event, @Col, @On
SET @Colstring=SUBSTRING(@Colstring,@j+1 ,300)
SET @j=CHARINDEX(',',@Colstring)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END
-- 필터를 설정한다
EXEC sp_trace_setfilter @TraceId, 10, 0, 7, N'SQL Profiler'
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, N'EXEC% sp_%trace%'
IF @IncludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 6, @IncludeFilter
IF @ExcludeFilter IS NOT NULL
EXEC sp_trace_setfilter @TraceId, 1, 0, 7, @ExcludeFilter
-- 추적을 활성화한다
EXEC sp_trace_setstatus @TraceId, 1
-- 추적을 기록한다. (테이블 사용)
IF OBJECT_ID('tempdb..TraceQueueList') IS NULL BEGIN
CREATE TABLE tempdb..TraceQueueList (TraceID int, TraceName
varchar(20), TraceFile sysname)
END
IF EXISTS(SELECT * FROM tempdb..TraceQueueList WHERE TraceName =
@TraceName) BEGIN
UPDATE tempdb..TraceQueueList
SET TraceID = @TraceId, TraceFile = @TraceFileName
WHERE TraceName = @TraceName
END
ELSE BEGIN
INSERT tempdb..TraceQueueList
VALUES(@TraceId, @TraceName, @TraceFileName)
END
RETURN 0
GO
/* 실행 하기 */
EXEC sp_trace_Start
[참고] | |
| |
■ 추적을 중지하는 저장 프로시저의 예제 스크립트 : sp_trace_stop USE master
GO
CREATE PROCEDURE sp_trace_stop @TraceName sysname='trace'
AS
SET NOCOUNT on
-- 변수를 선언한다
DECLARE @TraceId int
DECLARE @TraceFileName sysname
-- 추적 목록을 확인하여, 추적을 중지한다
IF OBJECT_ID('tempdb..TraceQueueList') IS NOT NULL BEGIN
SELECT @TraceId = TraceID, @TraceFileName=TraceFile
FROM tempdb..TraceQueueList
WHERE TraceName = @TraceName
IF @@ROWCOUNT<>0 BEGIN
EXEC sp_trace_setstatus @TraceId, 0
EXEC sp_trace_setstatus @TraceId, 2
DELETE tempdb..TraceQueueList
WHERE TraceName = @TraceName
PRINT ‘Trace is stopped. '
+ 'The trace output file name is '+@TraceFileName
END
ELSE
PRINT 'No active traces.'
END
ELSE
PRINT 'No active traces.'
RETURN 0
GO
/* 실행하기 */
EXEC sp_trace_stop
[참고] | |
| 추적 재생하기 | |
|
1. 프로필러를 시작하고, [파일] → [열기] → [추적 파일]을 선택합니다.
2. 원하는 파일을 선택합니다. 자세한 내용은 SQL Server 온라인 설명서의 [추적 재생]을 참조하십시오. | |
| 유용한 유틸리티 | |
| ■ PSSDIAG
PSSDIAG는 성능 분석에 필요한 여러가지 로그와 데이터를 수집할 수 있는 유틸리티입니다. 이 유틸리티를 사용하면 프로필러에 비해 부하를 덜 주면서 추적 데이터를 수집할 수 있으므로, 대용량 시스템의 경우에는 프로필러 대신 이 툴을 사용할 것을 권고합니다. 수집한 추적 정보를 분석하는데 매우 유용한 유틸리티입니다. 추적 데이터를 분석하여 로컬 데이터베이스에 분석한 정보를 저장해 주고, htm 파일 형태로 리소스를 많이 사용한 쿼리 또는 저장 프로시저, duration이 긴 쿼리 또는 저장 프로시저 등에 대한 분석 결과를 제공합니다. 자세한 내용은 다음을 참조하십시오. | |
Database/MSSQL
MSSQL DBA GUIDE - 프로필러
반응형
반응형