(─━┘_└━─)/ 머리만1톤(Head1Ton)

문제의 쿼리는 다음과 같다.

SET STATISTICS TIME ON
SET STATISTICS IO ON

-- 기존 쿼리   
SELECT DISTINCT     
     A.Sw_Seq Sw_Seq, A.Seq Seq, B.File_Desc File_Desc, D.USER_NAME USER_NAME,
     ISNULL(C.HNAME,'') HNAME, A.START_TIME START_TIME,    
     A.END_TIME END_TIME, A.Running  RUNNING,     
     dbo.UFN_COUNT_ALL(A.Sw_Seq, A.Seq, 'A') All_Count, 
     dbo.UFN_COUNT_ALL(A.Sw_Seq, A.Seq, 'M') Msg_Count,
     dbo.UFN_COUNT_ALL(A.Sw_Seq, A.Seq, 'O') OK_Count, 
     dbo.UFN_COUNT_ALL(A.Sw_Seq, A.Seq, 'F') Fail_Count,
     dbo.UFN_COUNT_ALL(A.Sw_Seq, A.Seq,'D') Done_Count
FROM SCHED A, USER_GROUP D, POLICY B    
LEFT JOIN  PC_USER C    
    ON B.REG_EMPNO = C.EMPNO    
WHERE    
    A.Sw_Seq = B.Sw_Seq AND    
    A.Seq = D.SEQ   AND    
    A.Running IN ('Y','N')    

-- 개선된 쿼리
SELECT 
        SubTable.Sw_Seq Sw_Seq, SubTable.Seq Seq, SubTable.File_Desc File_Desc,
        SubTable.USER_NAME USER_NAME, SubTable.HNAME, 
        SubTable.START_TIME START_TIME,
        SubTable.END_TIME END_TIME, SubTable.Running  RUNNING,
        dbo.UFN_COUNT_ALL(SubTable.Sw_Seq, SubTable.Seq, 'A') All_Count, 
        dbo.UFN_COUNT_ALL(SubTable.Sw_Seq, SubTable.Seq, 'M') Msg_Count, 
        dbo.UFN_COUNT_ALL(SubTable.Sw_Seq, SubTable.Seq, 'O') OK_Count, 
        dbo.UFN_COUNT_ALL(SubTable.Sw_Seq, SubTable.Seq, 'F') Fail_Count, 
        dbo.UFN_COUNT_ALL(SubTable.Sw_Seq, SubTable.Seq, 'D') Done_Count  
FROM 
(
    SELECT DISTINCT 
            A.Sw_Seq Sw_Seq, A.Seq Seq, B.File_Desc File_Desc,
            D.NAME NAME, ISNULL(C.HNAME,'') HNAME, A.START_TIME START_TIME, 
            A.END_TIME END_TIME, A.Running RUNNING
    FROM SCHED A, USER_GROUP D, POLICY B 
    LEFT JOIN  PC_USER C 
        ON B.REG_EMPNO = C.EMPNO 
    WHERE 
        A.Sw_Seq = B.Sw_Seq AND 
        A.Seq = D.SEQ   AND 
        A.Running IN ('Y','N')
)   SubTable

 개선된 결과는 다음과 같다.

에... 201 초가 0.7초로 단축되었으면.. 몇 % 개선이지?? ^^a


결론은 이거다.

william_read80trace-macro3.zip

kuaaan_ReadTraceTemplate.zip

ReadTrace 로 분석할 부하를 수집할 때는 스크립트를 사용하는 것이 여러 모로 권장할 만 하지만, ReadTrace 라는 놈이 원래 .trc 파일을 분석하는 놈이니 당연히 Profiler로 수집한 결과도 분석할 수 있다.

하지만 그냥 무작정 Profiler로 수집한 결과를 ReadTrace에 돌리면 에러가 나고 레포트가 잘 나오지 않는데, 그 이유는 ReadTrace란 놈이 분석하는데 필요한 필수 이벤트란 것들이 정해져 있기 때문이다. 이 이벤트들 중 일부라도 누락되게 되면 에러가 나고 분석이 되지 않는다.

이 이벤트들은 도움말 파일에 나와있지만 이 이벤트의 종류들의 한두가지가 아니고,  모든 이벤트를 수집하자니 부하가 너무 높고.... 해서 Template 을 만들어서 사용하는 것이 편하다.

SQL2000에서 사용했던 Read80Trace는 아래와 같은 템플릿파일을 사용했다. (출처는 인터넷 어디서 검색한 결과인데, 오래되어서 어디서 구했는지 기억이 나지 않는다. 도대체 william이 누구야... ^^)

william_read80trace-macro3.zip

kuaaan_ReadTraceTemplate.zip

뭐 그다지 사용법이랄 것도 없지만, 적어보면 다음과 같다.
(아래의 작업들은 DB서버 Local에서 수행한 경우이다.)

1. Profiler를 실행시키고, Template 파일을 Import한다.

2. 실제로 부하를 수집하여 분석할 .trc 파일을 만든다.

ClearTrace.2008.31.zip

http://www.cleardata.biz/cleartrace/download.aspx

TraceCaptureDef.sql

1. 무엇이 문제인지를 확인하고 현재 상태를 진단하여 튜닝의 기준선(Baseline)을 정의
2. 병목이 발생하는 지점이 어디인지를 확인
3. 확인된 병목을 해소할 방법을 모색
4. 해소방법을 적용한 후 다시 성능을 측정하여 (1단계 기준선 대비) 실제로 성능이 향상되었는지를 확인

※ RML Utility 도움말에서는 SQLDiag.exe로 시스템 정보를 수집하는 내용이 있지만, 이것은 튜닝대상 서버의 환경을 분석하여 베이스 정보를 수집하기 위한 과정이고, Report 생성에 필요한 과정은 아니라고 도움말에 나와있다. ^^;


좀 더 자세히 살펴보면 다음과 같다.

Step 1. RML 패키지 설치
Workload 분석 툴인 ReadTrace와 Workload Replay 툴인 O'Stress로 이루어진 RML Utility (Replay Markup Language Utility)를 다운받아 설치한다. 가급적이면 분석 대상인 DB서버에 바로 설치하는 것이 여러 모로 편하다. 다음의 URL에서 다운로드할 수 있다.

 http://support.microsoft.com/kb/944837 

TraceCaptureDef.sql

해당 DB서버에서 SQL Server Management Studio로 열고 trc 파일 저장위치('InsertFileNameHere') 부분을 'D:\Test'와 같이 수정하여 실행해준다. trc 확장자는 자동으로 append되므로 붙일 필요가 없으며, 만약의 경우를 대비하여 OS가 설치된 파티션이나 mdf/ldf 파일이 존재하는 파티션과 다른 파티션에 저장하는 것이 좋다.

실행하면 아래와 같은 결과가 SELECT되는데 이 때 내가 실행시킨 수집(Trace) 세션의 ID (아래 그림에서는 "2")를 기억해놓도록 한다.

Step 3. 수집한 Workload 를 분석

이제 수집된 Workload를 분석하기 위해 ReadTrace 프로그램을 사용한다.
ReadTrace 프로그램을 DB서버 Local에 설치했다면 이 작업도 당연히 DB서버 로컬에서 수행해야 한다.
ReadTrace.exe 는 커맨드 창에서 실행시켜야 하는 프로그램인데, 설치된 경로를 PATH 환경변수에 지정하기 위해 RML Utility에서 지원하는 RML Command창을 사용한다.

위와 같이 주면 기본적으로 분석 결과가 PerfAnalysis 라는 DB에 저장된다. 이 경우 여러번 분석하게 되면 이전 결과를 덮어쓰게 되므로 다음과 같이 DB 이름도 지정해줄 수 있다. (당연한 얘기지만... DB가 없으면 생성된다.)

Step 4. 분석 결과 확인

ReadTrace를 설치할 때 자동으로 함께 설치되는 RML Reporter 프로그램을 사용하여 분석된 결과를 Visual한 Report로 확인하고 악성 SQL을 찾아낸다. 최초 1번은 자동으로 실행되며 이후 레포트를 다시 보려면 다음과 같이 수동으로 Reporter 프로그램을 실행시키면 된다.