Step by Step: ReadTrace를 이용하여 SQL Server에서 수행되는 악성 쿼리 확인하기

기술 자료: 977826 - 이 문서가 적용되는 제품 보기.
모두 확대 | 모두 축소

개요

SQL Server의 응답 속도가 저하되면 자연스럽게 성능 튜닝에 관심을 갖게 됩니다. 다양한 튜닝 분야 중에서 쿼리의 성능 튜닝을 위해서는 어떤 쿼리가 성능 저하를 유발하는지 확인하는 작업이 우선시 되어야 합니다. 이 문서에서는 성능 튜닝에 필요한 자료 수집과 수집된 자료를 이용하여 리소스를 많이 사용하는 쿼리를 확인하는 방법에 대해서 설명합니다.

방법

단계 1: SQL Server에서 추적 파일 수집하기

SQL Server로 전달되어 수행되는 모든 쿼리는 추적(Trace)될 수 있으며, 별도의 파일로 저장되어 성능 튜닝이나 문제 재현등에 사용됩니다. 이러한 추적 파일의 수집은 크게 2가지 형태로 할 수 있습니다.

  • SQL Profiler (프로파일러)
  • Server side

추적이 수행되는 동안 SQL Server의 성능 저하를 최소화하기 위해서는 Server side를 이용한 방법이 권장됩니다. Server side에서의 추적 파일 수집은 3가지 형태로 수행될 수 있습니다.
  1. PSSDIAG: Microsoft에서 기술지원을 목적으로 고객사에 자료 수집을 요청할 때 사용됨
  2. SQLDIAG: SQL Server 2005에 이후에 통합된 SQL Server 전용 자료 수집 도구
  3. 저장프로시저 sp_trace*: 추적 파일 수집에 사용되는 3개의 저장 프로시저

1, 2번 또한 내부에서는 3번의 저장 프로시저를 이용합니다. 이 문서에서는 3. 저장 프로시저를 이용한 방법에 대해서만 설명하겠습니다. 추적 파일을 수집하려면 다음을 따라 해 보세요.

SQL Server 프로파일러를 이용하여 추적 템플릿 생성하기
  1. [시작], [모든 프로그램], [Microsoft SQL Server 2008], [성능 도구], [SQL Server 프로파일러]를 차례대로 클릭하여 Microsoft SQL Profiler를 실행합니다.
  2. 자료 수집을 원하는 SQL Server에 접속
    [파일], [새 추적]을 차례대로 클릭한 다음 서버에 연결 화면에서 연결 정보를 입력한 후 [연결]을 클릭합니다.
    그림 축소그림 확대
    00 01


    • 서버 유형: 데이터베이스 엔진
    • 서버 이름: SQL Server의 인스턴스명
    • 인증: Windows 인증 또는 SQL Server 인증
  3. 추적 속성 화면에서 템플릿 사용 값이 Standard(기본값)인 것을 확인합니다.
    그림 축소그림 확대
    00 02


  4. 그런 다음 [이벤트 선택]을 클릭하여 추적하도록 설정된 이벤트를 확인합니다. 특별히 수집이 필요하다고 생각되는 이벤트나 열이 있으면, 오른쪽 하단의 “모든 이벤트 표시”, “모든 열 표시” 확인란을 선택한 후 원하는 설정을 추가할 수 있습니다.
    그림 축소그림 확대
    00 03


  5. [실행]을 클릭한 후 바로 빨간색 중지 단추을 클릭하여 실행을 중지합니다.
    그림 축소그림 확대
    00 04


  6. 설정된 추적 정보를 스크립트 형태로 저장합니다.
    [파일], [내보내기], [추적 정의 스크립트]에서 자료 수집을 원하는 SQL Server 버전을 선택합니다. 여기서는 SQL Server 2008에서 수행할 것이므로 [SQL Server 2005 - 2008]를 선택합니다.
    그림 축소그림 확대
    00 05


    폴더 위치와 파일 이름을 지정한 후 파일을 저장합니다. 예제에서는 TraceTemplate.sql이라는 이름으로 파일을 저장하였습니다.
  7. 저장된 파일을 Microsoft SQL Server Management Studio에서 확인합니다.
    다음은 해당 스크립트의 첫줄부터 sp_trace_create를 호출하는 부분까지를 발췌한 것입니다.
    /****************************************************/
    /* Created by: SQL Server 2008 Profiler             */
    /* Date: 2009/11/13  03:49:06 AM         */
    /****************************************************/
    
    
    -- Create a Queue
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    set @maxfilesize = 5 
    
    -- Please replace the text InsertFileNameHere, with an appropriate
    -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
    -- will be appended to the filename automatically. If you are writing from
    -- remote server to local drive, please use UNC path and make sure server has
    -- write access to your network share
    
    exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
    

SQL Server 프로파일러를 이용하여 추적 템플릿 생성하기
  1. 위 7) 스크립트에 표시된 내용을 삭제한 후, 다음 내용으로 교체합니다.
    /********************************************************
    내용: sp를이용한 trace 파일관리
    Parameters :
    	@Options : 2 - Roll over. MaxFileSize에 도달하면 새파일 생성후 계속해서 자료수집.
    	@MaxFileSize : 한개의 추적 파일에 대한 최대 크기 지정. MB 단위.
    	@StopTime : 추적 파일 수집을 자동으로 중지하려는 시각 설정(NULL:수동으로중지)
    	@OutputFolder : .trc 파일이 생성될 폴더
    사용예: 
    	--sp수행(return value 가 1이면 정상적으로 시작됨.)
    	exec uspTrace
    	--현재 수행 중인 모든 trace 확인
    	SELECT * FROM :: fn_trace_getinfo(default) 
    	--특정 trace id 중지
    	exec sp_trace_setstatus @traceid = 2, @status = 0
    	--특정 trace id 삭제
    	exec sp_trace_setstatus @traceid = 2, @status = 2
    *********************************************************/
    
    CREATE PROC uspTrace
    (
    	@maxfilesize bigint = 300,
    	@Options int = 2,
    	@stopTime datetime = null,
    	@OutputFolder varchar(100) = 'C:\temp\'
    )
    as
    
    -- Create a Queue
    declare @rc int
    declare @TraceID int
    declare @FileName sysname
    -- Filename은 MyTrace+수집시작시각.trc 형태로 수집됨. (예: MyTrace200911101545.trc)
    set @FileName = @OutputFolder+'MyTrace'+ REPLACE(REPLACE(REPLACE(CONVERT(varchar(16),getdate(),120),':',''),'-',''),' ','') 
    
    exec @rc = sp_trace_create @TraceID output, @options=@Options, @tracefile=@FileName, @maxfilesize=@MaxFileSize, @stoptime=@StopTime
    

    이 작업은 이벤트 설정은 그대로 유지한 체 파일 위치 등의 일부 정보만 원하는 내용으로 수정하기 위해서 사용합니다.

    표 축소표 확대
    그림 축소그림 확대
    팁

    1. Rollover Size는 300MB 이하가 권장됩니다. 이 값은 추적 내용이 파일로 저장되는 속도, 그리고 관리되는 파일 하나의 크기를 고려하여 권장하는 값입니다.
    2. 파일이 저장되는 위치는 원격 저장소도 가능하지만, 성능과 추적 파일 손실을 고려하여 로컬 저장소를 이용하시기 바랍니다.
    3. @FileName에 명시된 디렉토리는 미리 생성되어져 있어야 합니다.
  2. SQL Server Management Sudio(이하 SSMS)에서 Master 데이터베이스에 접속 후 uspTrace를 생성합니다.
  3. SSMS에서 생성된 uspTrace를 수행하면 추적 파일이 쌓이기 시작합니다. 원하는 경우, parameters를 추가하여 설정을 변경할 수 있습니다. 다음과 같이 그냥 수행하게 되면 C:\Temp\ 폴더에 MyTrace200911101545.trc와 같은 형식으로 파일이 생성됩니다.
    exec uspTrace

    수행 결과에서 TraceID가 몇 번으로 생성되었는지 확인합니다. SQL Server 2005부터는 기본 추적이 자동으로 시작되기 때문에 항상 TraceID 1은 항상 사용되고 있습니다.

  4. 설정된 추적 파일이 잘 생성되고 있는지 설정된 폴더에서 확인합니다.

    --예) 2009-11-10 15:45에 추적을 시작한 파일
    D:\temp\MyTrace200911101545.trc

    --예) 기본 파일의 크기가 300MB에 달하여 첫번째 roll over가 발생한 경우
    D:\temp\MyTrace200911101545.trc
    D:\temp\MyTrace200911101545_1.trc

  5. 현재 Server side로 수행중인 추적 정보를 보려면 SSMS에서 다음 명령을 이용합니다.

    --수행 중인 모든 추적 정보 확인
    SELECT * FROM :: fn_trace_getinfo(default) 

    --Traceid가 2인 추적 정보 확인
    SELECT * FROM :: fn_trace_getinfo(2)

    -- Traceid가 2인 추적 정보를 확인한 예
    그림 축소그림 확대
    00 06



    각각의 property(추적 속성)가 의미하는 값은 다음과 같습니다.
    • 1 = 추적 옵션
    • 2 = 파일 이름
    • 3 = 최대 크기
    • 4 = 중지 시간
    • 5 = 현재 추적 상태. 0 = 중지됨. 1 = 실행 중
  6. 원하는 만큼 자료가 수집되었다고 생각되면, Server side 추적을 중지합니다.
    --Traceid가 2번인 Server side추적을 중지(상태 0)시키는 예
    exec sp_trace_setstatus @traceid = 2, @status = 0
    
    
  7. 중지된 추적 정보를 Server side에서 삭제합니다(수집된 추적파일은 삭제되지 않음).
    --중지된 traceid 1번을 삭제(상태 2)하는 예제
    exec sp_trace_setstatus @traceid = 2, @status = 2
표 축소표 확대
그림 축소그림 확대
주의사항

  1. SQL Server 프로파일러를 이용한 자료 수집은 UI를 이용하기 때문에 편하지만, SQL Server성능에 직접적으로 영향을 미칠 수 있습니다. 따라서, 테스트 머신이나 부하가 없는 운영서버에서 로컬로 접속하여 사용할 것을 권장합니다.
  2. 부하가 심한 운영머신에서 Server side 추적을 장시간 수행하는 것도 성능 저하를 초래할 수 있습니다. 따라서, 장시간 수행할 때에는 서버에 영향이 없는지 관심있게 지켜볼 필요가 있습니다.
  3. 성능 이슈가 발생하고 있는 시점에 수집된 추적 파일만이 의미가 있습니다. 문제가 없는 상황에서 수집된 자료는 문제 시점과 비교할 수 있는 baseline 자료로 쓰일 수 있지만, 그것 자체만으로는 문제의 쿼리를 확인할 수 없습니다.

단계 2: RML 툴 설치하기

Microsoft SQL Server 지원팀은 전형적인 고객 지원 건에 대해서 작업을 쉽게 하기 위해 내부적으로 작성된 툴을 사용합니다. 그 중 하나가 RML(Replay Markup Language)이라는 툴이며 이 문서에서 소개한 ReadTrace 또한 이 툴에 포함된 유용한 도구 중의 하나입니다.
  1. 다음 영문 문서에서 ReadTrace를 수행하려는 시스템(x86또는 x64)에 맞는 RML툴을 다운로드받아 설치합니다.

    Description of the Replay Markup Language (RML) Utilities for SQL Server
  2. 설치된 폴더에서 ReadTrace.exe가 있는지 확인합니다.
    --예) x64 시트템에 생성된 기본 위치
    C:\Program Files\Microsoft Corporation\RMLUtils


    X64 시스템에 설치된 모습
    그림 축소그림 확대
    00 07


표 축소표 확대
그림 축소그림 확대
주의사항

Microsoft에서 제공하는 SQL Server용 RML 툴은 있는 그대로를 제공하며, 이 툴 자체에 대한 기술지원이나 업데이트를 제공하지 않습니다.

단계 3: ReadTrace를 이용하여 튜닝이 필요한 쿼리 확인하기

다음 절차를 통해서 특정 추적 파일에 대한 분석 내용을 확인하실 수 있습니다. 추적 파일명은 myTraces.trc이라고 가정하겠습니다.
  1. [시작], [실행]을 차례대로 클릭하여 cmd를 입력한 다음 [확인]을 클릭합니다.
    실행된 명령창에서 ReadTrace가 설치된 폴더로 이동
    cd C:\Program Files\Microsoft Corporation\RMLUtils
  2. ReadTrace명령을 이용하여 분석용 DB(PerfAnalysis)를 만들고, 수집된 추적 데이터를 입력합니다.

    다양한 옵션을 사용할 수 있으며 retrace /? 로 확인하거나 RML 도움말을 확인합니다. 기본적으로 사용할 수 있는 명령은 다음 예를 참조하세요.

    사용 예1)
    ReadTrace ?I”C:\temp\myTraces.trc”
    추척 파일의 경로만 입력해 주고 수행하면, 기본 SQL Server 인스턴스를 찾아서 PerfAnalysis DB를 생성한 후 분석 결과를 그곳에 저장합니다.


    사용 예 2)
    ReadTrace ?I”C:\temp\myTraces.trc” ?o”C:\temp\result\”
    사용 예1의 동작 + 명령창에 나타난 메시지를 지정한 output 폴더에 파일로 생성해 줍니다.


    사용 예 3)
    ReadTrace ?I”C:\temp\myTraces.trc” ?o”C:\temp\result\” ?S.\sql2008
    사용 예2의 동작에서 SQL Server만 기본 인스턴스가 아닌 명명된 인스턴스 sql2008을 이용합니다.


    사용 예 4)
    ReadTrace ?I”C:\temp\myTraces.trc” ?o”C:\temp\result\” ?S.\sql2008 ?dPerfDB1
    사용 예 3의 동작에서 분석용 DB를 지정한 이름(PerfDB1)으로 생성하여 사용합니다. 분석용 DB는 항상 재생성되므로 기존의 분석 데이터를 보존하고 싶다면 이와 같이 DB를 다르게 지정해야 합니다.
  3. Reporter 확인하기
    ReadTrace에서 분석 작업이 완료되면 자동으로 Reporter가 수행되어 Performance Overview 페이지를 보여줍니다.
    그림 축소그림 확대
    00 08


    Main 탭에서는 Performance Overview에 대해서만 보여줍니다. 위 예제에서는 이해를 돕기위해 수행되는 쿼리 수를 제한하고, 리소스를 많이 사용하는 쿼리를 하나만 수행하였습니다. 그래프를 보면 특정 시점에 리소스(Duration, CPU, Reads)가 많이 사용된 것을 확인할 수 있습니다.
  4. Performance Overview 아래의 [Unique Batches]를 선택하여 각각의 쿼리를 확인합니다.
    그림 축소그림 확대
    00 09


    Top Unique Batches 탭에서는 각 리소스별로 사용량이 많은 쿼리들의 순위를 보여줍니다. 즉, CPU를 과도하게 사용하거나, Duration(수행 시간)이 너무 오래걸렸거나 또는 페이지 IO가 많이 발생하여 Reads/Writes가 높은 쿼리들의 순위를 확인할 수 있습니다. 이런 쿼리들을 우선 튜닝 대상으로 선정하면 됩니다.

추가 정보

  1. RML에 포함된 Reporter를 이용해서 이미 생성해 놓은 다른 분석 DB에 연결하여 언제든지 결과를 볼 수 있습니다. 다음과 같이 Reporter를 수행한 다음 [시작], [모든 프로그램], [ RML Utilities for SQL Server], [Rerporter]를 차례대로 클릭하여 Properties 탭에서 해당 DB로 연결 설정을 해주면 됩니다.
  2. 위에서 사용한 readtrace분석 과정은 GUI형태의 SQL Nexus를 통해서도 사용할 수 있습니다. 이 문서에서는 readTrace에 대해서만 설명합니다. SQL Nexus는 다음을 참조하세요.

    SQL Nexus Tool
  3. ReadTrace는 완료된 쿼리에 대해서만 분석이 가능합니다. 특정 악성쿼리가 완료되지 않고 계속 리소스를 사용하는 상황이라면 이 툴을 이용한 분석은 도움이 되지 않습니다.

참조

의견 보내기

표 축소표 확대
그림 축소그림 확대
의견 보내기

Microsoft 고객지원 사이트에서는 고객님의 소리를 귀담아 듣습니다. 아래 의견 보내기로 소중한 의견 보내주시기 바랍니다.

속성

기술 자료: 977826 - 마지막 검토: 2014년 5월 23일 금요일 - 수정: 1.3
본 문서의 정보는 다음의 제품에 적용됩니다.
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
키워드:?
kbhowto kbstepbystep KB977826

피드백 보내기

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com