HOWTO: SQL Server에서 응용 프로그램 성능 문제 해결

기술 자료 번역 기술 자료 번역
기술 자료: 224587 - 이 문서가 적용되는 제품 보기.
이 문서는 이전에 다음 ID로 출판되었음: KR224587
모두 확대 | 모두 축소

이 페이지에서

요약

이 문서에서는 SQL Server 성능 문제를 해결하는 방법을 단계별로 설명합니다. 성능 관련 문제 해결은 응용 프로그램 성능 저하의 원인을 파악하여 해결하는 일련의 단계를 말합니다. 가능한 원인은 다음과 같습니다.
  • 블로킹
  • 시스템 리소스 경합
  • 응용 프로그램 설계 문제
  • 실행 시간이 긴 쿼리 또는 저장 프로시저 세트
이 문서에서는 성능 문제의 원인을 파악하는 방법을 설명합니다. 또한 추가적인 문제 해결을 위해 특정 성능 문제에 대해 자세한 사항을 다룬 Microsoft 기술 자료의 다른 문서도 참조합니다.

SQL 프로필러


SQL 프로필러는 SQL Server 7.0 이상에서 응용 프로그램 성능 문제를 해결할 때 사용할 수 있는 강력한 도구입니다. SQL 프로필러를 사용하면 일반 로드 시 서버에서 발생하는 모든 이벤트를 쉽게 캡처할 수 있으며 그에 대한 정보도 얻을 수 있습니다. SQL 프로필러를 Microsoft Windows NT 성능 모니터 및 일부 간단한 쿼리와 함께 사용하여 블로킹 발생 유무를 확인하면 광범위한 성능 문제를 해결하는 데 필요한 정보를 얻을 수 있습니다.

모니터링할 대상

1. SQL 프로필러를 설치하여 추적(Trace)을 캡처합니다. 다음과 같이 하십시오.
  1. SQL 프로필러를 엽니다.
  2. Tools 메뉴에서 Options를 누릅니다.
  3. All Event Classes 옵션과 All Data Columns 옵션이 선택되었는지 확인합니다.
  4. OK를 누릅니다.
  5. 새 추적을 만듭니다.
  6. File 메뉴에서 New를 가리킨 다음 Trace를 누릅니다.
  7. General 탭에서 데이터를 캡처할 추적 이름과 파일을 지정합니다.
  8. Events 탭에서 다음과 같은 이벤트 유형을 추적에 추가합니다.

    표 축소표 확대
    머리글추가할 이벤트설명
    CursorsCursorPrepare이 이벤트는 ODBC, OLEDB 또는 DB-Library를 사용하여 SQL 문에 있는 커서를 준비했음을 나타냅니다.
    Error and WarningMissing Column Statistics이 이벤트는 최적화 프로그램(Optimizer)에서 유용하게 쓰이는 열(Column) 통계를 사용할 수 없음을 나타냅니다. 이 때 Text 열은 사용할 수 없는 열 통계의 목록을 보여 줍니다. 이 이벤트는 Misc: Auto-UpdateStats 이벤트와 결합하여 Auto Create Statistics 옵션을 트리거했음을 나타냅니다.
    Misc.Attention이 이벤트는 클라이언트에서 주의 신호를 보냈음을 나타냅니다.
    Misc.Auto-UpdateStats이 이벤트는 Auto Update Statistics 옵션을 트리거했음을 나타냅니다.
    Misc.Exec Prepared SQL이 이벤트는 ODBC, OLE DB 또는 DB-Library가 준비된 Transact-SQL 문을 실행했음을 나타냅니다.
    Misc.Execution Plan이 이벤트는 실행된 Transact-SQL 문의 계획(Plan) 트리를 보여 줍니다.
    Misc.Prepare SQL이 이벤트는 ODBC, OLE DB 또는 DB-Library 응용 프로그램이 Transact-SQL 문을 사용할 준비가 되었음을 나타냅니다.
    Misc.Unprepare SQL이 이벤트는 ODBC, OLE DB 또는 DB-Library 응용 프로그램이 Transact-SQL 문을 사용할 준비가 되지 않았음을 나타냅니다.
    SessionsConnect이 이벤트는 새 연결이 만들어졌음을 나타냅니다.
    SessionsDisconnect이 이벤트는 클라이언트 연결이 끊어졌음을 나타냅니다.
    SessionsExisting Connection이 이벤트는 SQL 프로필러 추적이 시작될 때 연결이 이미 존재했음을 나타냅니다.
    Stored ProceduresSP: Completed이 이벤트는 저장 프로시저가 실행을 완료했음을 나타냅니다.
    Stored ProceduresSP: Recompile이 이벤트는 저장 프로시저(Stored Procedure)가 실행 중에 다시 컴파일되었음을 나타냅니다.
    저장 프로시저(Stored Procedure)
    SP: Starting이 이벤트는 저장 프로시저가 실행을 시작했음을 나타냅니다.
    Stored ProceduresSP: StmtCompleted이 이벤트는 저장 프로시저 내에 있는 명령문이 실행을 완료했음을 나타냅니다.
    TSQL:SQL:BatchCompleted이 이벤트는 Transact-SQL의 일괄 처리를 완료했음을 나타냅니다. 이 때 Text 열은 실행된 명령문을 보여 줍니다.
    TSQL:SQL:StmtCompleted이 이벤트는 Transact-SQL 문을 완료했음을 나타냅니다. 이 때 Text 열은 실행된 명령문을 보여 줍니다.
    TSQL:RPC:Completed이 이벤트는 RPC(원격 프로시저 호출)가 완료되었음을 나타냅니다.
  9. 응용 프로그램에서 시간 초과 오류를 일으키거나 응답을 하지 않고 또는 문제가 있는 명령문을 완료하지 못하는 등의 여러 이벤트가 발생하는 경우 다음과 같은 이벤트도 포함시키십시오.

    표 축소표 확대
    TSQL:SQL:BatchStarting이 이벤트는 Transact-SQL 일괄 처리의 시작을 나타냅니다. 이 때 Text 열은 실행되는 명령문을 보여 줍니다.
    TSQL:SQL:StmtStarting이 이벤트는 Transact-SQL 문의 시작을 나타냅니다. 이 때 Text 열은 실행되는 명령문을 보여 줍니다.
    TSQL:RPC:Starting이 이벤트는 RPC(원격 프로시저 호출)의 시작을 나타냅니다.
    Stored ProceduresSP: StmtStarting이 이벤트는 저장 프로시저 내에 있는 명령문이 실행을 시작하고 있음을 나타냅니다.


    이러한 이벤트를 이용하면 시간 초과 오류가 발생했을 경우 실행 중이던 명령문을 확인할 수 있습니다.
  10. Data Columns 탭에 다음 열이 있는지 확인합니다.

    SQL Server 2000의 경우

    Start Time

    End Time

    LoginSid

    SPID

    Event Class

    TextData

    IntegerData

    BinaryData

    Duration

    CPU

    Reads

    Writes

    Application Name

    NT User Name

    DBUserName


    SQL Server 7.0의 경우

    Start Time

    End Time

    Connection ID

    SPID

    Event Class

    Text

    Integer Data

    Binary Data

    Duration:

    CPU

    Reads

    Writes

    Application Name

    NT User Name

    SQL User Name

SQL 프로필러 사용에 대한 자세한 내용은 SQL Server 7.0 및 SQL Server 2000 온라인 설명서를 참조하십시오.


2. 성능 모니터를 사용하여 Windows NT와 SQL Server 카운터를 캡처합니다. 다음과 같이 하십시오.
  1. Windows NT 시스템 성능 모니터를 시작합니다.
  2. 보기 메뉴에서 로그를 누릅니다.
  3. 옵션 메뉴에서 로그를 누릅니다.
  4. 파일 이름과 위치를 지정하여 성능 카운터를 로깅합니다. 그리고 나서 업데이트 간격을 적당하게 조절합니다.
  5. 편집 메뉴에서 로그에 추가를 누릅니다.
  6. 모든 개체를 추가합니다. Windows NT 개체와 SQL Server 개체를 모두 추가합니다.
  7. 로깅을 시작하려면 옵션 메뉴에서 로그를 누른 다음 로그 시작 단추를 누릅니다.

자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
150934 NT 문제 해결을 위해 성능 모니터 로그를 만드는 방법

3. 블로킹을 검사합니다.

블로킹이 일어나고 있는지 확인하려면 sp_who 시스템 저장 프로시저를 실행합니다.
exec sp_who
이 출력에 blk 열이 있습니다. 블로킹이 일어나고 있다는 것을 나타내는 0이 아닌 값을 가지는 항목을 검사합니다. 성능의 저하가 일어나는 경우 이 프로시저를 일정 시간 동안 주기적으로 실행하도록 합니다.

참고sp_who 시스템 저장 프로시저를 실행하면 단순히 블로킹의 유무만을 검사합니다. 일반적으로 블로킹 문제를 완전히 해결하는 데 충분한 정보가 되지 못합니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
251004 INF: SQL Server 7.0 블로킹 모니터링 방법

일반 로드 시 응용 프로그램 실행

이상적으로는 SQL 프로필러, 성능 모니터 및 블로킹 출력은 동일한 시간 내에 캡처하는 것이 좋습니다. 이 시간은 응용 프로그램의 성능이 양호한 상태에서 나쁜 상태로 가는 시간을 말하는 것입니다. 이 정보를 합하면 성능 저하의 원인에 대하여 좀더 명확한 대안책을 얻는 데 도움이 될 것입니다.


결과 해석

  1. 블로킹 검사

    sp_who 출력에 있는 blk 열이 0이 아닌 경우 사용자의 시스템에서 블로킹이 일어나고 있음을 의미합니다.. 프로세스가 서로를 블로킹하는 경우 블로킹을 받는 프로세스의 실행 시간이 더 오래 걸리게 됩니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
    224453 INF: SQL Server 7.0 블로킹 문제 파악 및 해결 방법
  2. SQL 프로필러 출력 확인

    SQL 프로필러 데이터를 효과적으로 분석하는 것은 성능 문제 해결에서 아주 중요합니다. 알아야 할 것 중 가장 중요한 것은 캡처한 모든 것을 볼 필요 없이 선택적으로 볼 수 있다는 것입니다. SQL 프로필러는 캡처한 데이터를 효과적으로 분석할 수 있도록 도와줍니다. SQL 프로필러를 사용하면 File 메뉴에서 Properties를 누르면 나타나는 Properties 탭에서 표시할 데이터의 유형을 제한할 수 있는데 데이터 열 또는 이벤트를 제거하거나 데이터 열로 그룹화(정렬)하거나 필터를 적용하는 방법을 사용합니다. 전체 추적을 검색하거나 Edit 메뉴에서 Find를 눌러 특정 값에 대해 특정 열만 검색할 수도 있습니다. 또한 File 메뉴에서 Save As를 가리키고 Trace Table을 눌러 SQL 프로필러 데이터를 SQL Server 테이블에 저장한 다음 이에 대해 SQL 쿼리를 실행할 수도 있습니다.

    이전에 저장한 추적 파일에만 필터링을 수행해야 합니다. 활성화된 추적에서 이러한 단계를 수행하면 추적을 시작한 후에 캡처한 데이터는 손실될 우려가 있습니다. 먼저 활성화된 추적을 파일 또는 테이블에 저장(File 메뉴에서 Save As 누름)한 다음 계속하기 전에 다시 엽니다(File 메뉴에서 Open 누름). 저장한 추적 파일에서 작업을 하게 되면 필터링은 필터링된 데이터를 영구적으로 삭제하는 것이 아니고 데이터를 단지 표시하지 않는 것입니다. 특히 중점을 두는 검색에 필요한 이벤트와 데이터 열을 추가하거나 삭제할 수 있습니다.

    성능과 관련한 SQL 프로필러 추적 파일을 검사하는 첫 단계는 서로 다른 유형의 이벤트가 서버의 어느 위치에서 일어나고 있는 지 확인하는 것입니다.

    이벤트 클래스별로 추적 그룹화:

    a. 파일 메뉴에서 Properties를 누릅니다.

    b. Data Columns 탭에서 UP 단추를 사용하여 Event ClassGroups 머리글 아래로 이동한 다음 Groups 머리글 아래에 있는 다른 열을 모두 DOWN 단추를 사용하여 제거합니다.

    c. OK를 누릅니다.

    이벤트 클래스 열별로 그룹화하면 SQL Server에서 어떤 유형의 이벤트가 일어나고 있고 그 발생 빈도는 어떻게 되는지 확인할 수 있습니다. 이 열에서 다음과 같은 이벤트를 검색합니다.

    SP:RECOMPILE

    이 이벤트는 저장 프로시저가 실행 중 다시 컴파일되었음을 나타냅니다. Recompile 이벤트 수가 많은 경우 SQL Server가 쿼리를 실행할 때보다는 쿼리 컴파일에 리소스를 더 많이 사용하고 있음을 나타냅니다.

    저장 프로시저 재컴파일 문제 해결에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
    243586 INF: 저장 프로시저(Stored Procedure) 재컴파일 문제 해결 방법


    Attention

    Attention 신호는 클라이언트에서 쿼리를 취소했음을 나타냅니다. 여기에는 일반적으로 두 가지 원인이 있습니다.

    사용자가 명시적으로 쿼리를 취소했거나 응용 프로그램을 종료했습니다.

    또는

    쿼리 실행 시간이 초과되었습니다.

    즉 Attention 신호가 표시될 경우에는 쿼리의 실행 속도가 느리다는 것을 나타냅니다.

    자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
    243589 INF: SQL Server 7.0에서 느리게 실행되는 쿼리 문제 해결 방법
    어떤 쿼리에서 Attention 신호를 받는지 확인하려면 추적을 특정 데이터 열로 그룹화하지 않게 수정하고 Filters 탭에서 SPID 값을 x로 설정하여 쿼리를 받는 SPID(시스템 프로세스 ID)를 필터링하십시오. 즉시 Attention 신호를 표시하는 SQL:StmtStarting, SQL:BatchStarting 또는 SP:StmtStarting 이벤트는 시간 초과나 취소를 받는 쿼리입니다. Edit 메뉴에서 Find를 눌러 Event Class 열을 검색하면 Attention 이벤트를 쉽게 찾을 수 있습니다.

    PREPARE SQL과 EXEC PREPARED SQL

    Prepare SQL 이벤트는 ODBC, OLE DB 또는 DB-Library 응용 프로그램이 다음 번 사용을 위하여 Transact-SQL 문을 준비했음을 나타냅니다. Exec Prepared SQL 이벤트는 응용 프로그램에서 기존의 준비된 SQL 문을 사용하여 명령을 실행했음을 나타냅니다.

    위의 두 가지 이벤트가 일어나는 횟수를 비교해 보십시오. 응용 프로그램은 SQL 문을 한 번만 준비하고 여러 번 실행하는 것이 가장 좋습니다. 이는 최적화 프로그램이 명령문을 실행할 때마다 새로운 계획(Plan)을 컴파일하는 비용을 줄여 주기 때문입니다. 그러므로 Exec Prepared SQL 이벤트의 수가 Prepare SQL 이벤트의 수보다 훨씬 많아야 합니다. Prepare SQL 이벤트의 수가 Exec Prepared SQL 이벤트의 수와 거의 비슷한 경우 해당 응용 프로그램이 준비/실행 모델을 제대로 사용하고 있지 못함을 나타냅니다. 그러므로 한 번만 실행되는 명령문을 준비하지 않는 것이 좋습니다. SQL 문 준비에 대한 자세한 내용은 SQL Server 7.0 온라인 설명서에 있는 "Preparing SQL Statements" 항목을 참조하십시오.

    Exec Prepared SQL 이벤트의 수가 Prepare SQL 이벤트의 수보다 3~5배 정도 크지 않은 경우 응용 프로그램에서 준비/실행 모델을 효과적으로 사용하고 있는 것이 아닙니다. 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.
    243588 INF: Ad-Hoc 쿼리의 성능 문제 해결

    SQL Server 2000에서는 준비/실행당 초과적인 반복 실행은 제거될 것이므로 3-5 정도의 비율은 그다지 심각한 문제가 되지 않습니다. 그럼에도 불구하고 준비된 계획을 한 번 이상 시도하고 다시 사용하는 것이 좋습니다.

    Missing Column Statistics

    이 이벤트는 최적화 프로그램에서 더 좋은 쿼리 계획(Query Plan)을 만드는 데 사용하는 통계 정보를 사용할 수 없음을 나타냅니다. 이는 적어도 한 테이블에 있는 유용한 인덱스가 쿼리에 포함되지 않았음을 나타냅니다. 유용한 인덱스 외에도 열에 대한 통계 정보까지 SQL Server에 없기 때문에 쿼리 계획에 대한 결정을 하지 못합니다. 이 출력은 만들어진 쿼리 계획이 최적이 아닐 수도 있다는 것입니다. 이 이벤트가 표시될 경우 생성된 쿼리와 실행 계획을 살펴 보고 Microsoft 기술 자료에 있는 다음 문서를 참고하여 이 쿼리의 성능 향상을 위한 단계를 구현하도록 하십시오.
    243589 INF: SQL Server 7.0에서 느리게 실행되는 쿼리 문제 해결 방법

    Missing Column Statistics 이벤트를 분석할 경우 실행 시간이 긴 쿼리와 관련된 것에 먼저 포커스를 두도록 합니다. SQL Server의 AutoStats 기능을 이용하여 어떤 이벤트는 자동적으로 만들어 지고 해결될 수도 있어 사용자 작업이 필요하지 않을 수도 있습니다. 그러므로 아래에서 보는 것처럼 오랜 시간이 걸리는 쿼리에 먼저 포커스를 두는 것이 가장 좋은 전략입니다. 그리고 이와 관련된 Missing Column Statistics 이벤트가 있는지 확인하십시오.

    이러한 이벤트 클래스의 인스턴스가 나타나지 않는 경우에는 다음 단계에서 시간을 어디에 쓸 지 결정하십시오.

    기간(Duratrion)별로 추적 출력 그룹화:

    a. File 메뉴에서 Properties를 누릅니다.

    Data Columns 탭에서 UP 단추를 사용하여 DurationGroups 머리글 아래로 이동한 다음 Groups 머리글 아래에 있는 다른 열을 DOWN 단추를 사용하여 모두 제거합니다.

    c. Events 탭에서 TSQLStored Procedures를 제외하고 모든 그룹을 제거합니다.

    d. OK를 누릅니다.

    기간별로 그룹화하는 경우 어떤 SQL 구문, 일괄 처리 또는 프로시저가 가장 느린 속도로 실행 중인지 쉽게 확인할 수 있습니다. 문제가 발생된 시간을 확인하는 것 뿐만 아니라 성능이 좋을 때의 Baseline을 확인하는 것도 아주 중요합니다. 사용자는 시작 시간으로 필터링을 하여 추적을 성능이 좋았을 경우의 섹션과 성능이 나빴을 경우의 섹션으로 따로 나눌 수 있습니다. 성능이 좋을 때 가장 긴 기간을 갖는 쿼리를 찾도록 합니다. 이것이 바로 문제 발생의 가장 근본적인 원인입니다. 시스템 전체의 성능이 떨어지는 경우 문제가 없는 쿼리라도 시스템 리소스 상에서 대기하기 때문에 시간이 오래 걸릴 수 있습니다.

    기간이 긴 몇 개의 쿼리가 있는 경우 Microsoft 기술 자료에 있는 다음 문서를 참고하십시오.
    243589 INF: SQL Server 7.0에서 느리게 실행되는 쿼리 문제 해결 방법
    각 쿼리의 기간은 짧은데 많이 사용되어 성능 모니터 출력에서 SQL Compilations/sec 카운터가 높은 경우에는 Microsoft 기술 자료에 있는 다음 문서를 참조하십시오.
    243588 INF: Ad-Hoc 쿼리의 성능 문제 해결
    나머지 데이터 열( 확인:

    추적 데이터에 있는 다른 데이터 열을 분석하면 성능 문제에 대한 추가 정보를 볼 수 있습니다. 다음 사항을 고려하십시오.

    CPU Usage가 높은 경우 CPU로 그룹화하면 어떤 쿼리가 CPU 시간을 가장 많이 사용하는 지 알 수 있습니다. Text 열에서 "hash"나 "merge"를 검색하여 어떤 쿼리 실행 계획에서 이러한 조인 유형을 사용하고 있는지 확인합니다. 이러한 조인은 IO를 많이 사용하는 중첩 루프 조인과는 달리 CPU와 메모리를 많이 사용합니다.

    디스크 IO가 병목 상태인 경우 Reads 및 Writes로 그룹화하십시오. Application Name, NT User NameSQL User Name 필드를 확인하면 실행 시간이 긴 쿼리의 소스를 알 수 있습니다.

    Exception 이벤트의 Integer Data 열은 클라이언트로 반환되는 오류를 나타냅니다. SQL Server 7.0 온라인 설명서에서 해당 숫자를 검색하면 오류 메시지 구문을 찾을 수 있습니다.

    Connection ID 필드는 특정 클라이언트의 세션을 확인하는 데 도움이 됩니다. 하지만 SPID는 이를 보장하지 않는데, 이것은 사용자의 연결이 끊어져 새로운 사용자가 연결하면 동일한 SPID를 받을 수 있기 때문입니다.

    위에 설명된 필드에서 얻을 수 있는 이점은 각자의 시나리오에 따라 다릅니다. 하지만 성능 문제와 관련된 필드에서 해결책을 찾지 못했다면 위의 필드들도 검사를 해보아야 합니다.
  3. 성능 모니터 출력 확인

    성능 모니터는 전체적인 시스템의 병목 현상을 표시합니다. SQL Server나 응용 프로그램이 예상대로 수행될 수도 있지만 컴퓨터의 성능이 낮아져서 메모리나 다른 리소스가 부족할 수도 있습니다. 아니면 어떤 카운터에서 응용 프로그램과 SQL Server 실행 시 나타난 문제를 표시할 수도 있습니다. 최소한 다음과 같은 카운터를 검사하십시오.

  • 개체: Process

    카운터: Processor

    인스턴스: SQL Server

  • 개체: Processor

    카운터: % Processor Time

    인스턴스: Check each processor instance

  • 개체: Physical Disk

    카운터: Avg. Disk Queue Length

    인스턴스: Check each physical disk instance

  • 개체: SQL Server:SQL Statistics

    카운터: SQL Compilations/sec
성능이 좋은 상태에서 나쁜 상태로 변했을 때의 시간에 대하여 경향을 찾도록 합니다. 먼저 무엇이 증가 되었습니까? 컴퓨터 CPU와 DISK IO 중 어떤 것이 바운드되었습니까? 이 정보를 이 문서 앞 부분의 프로필러 출력과 함께 검토하여 문제가 있는 영역을 좁혀갈 수 있습니다. CPU 관련 문제가 많다는 것은 저장 프로시저를 다시 컴파일하는 횟수나 임의(Ad-hoc) 쿼리 컴파일, 해시 및 병합(Merge) 조인을 집중적으로 사용하는 횟수가 많음을 의미합니다. 본 문서 앞부분에 참조되어 있는 문서를 참조하여 올바른 단계를 결정하도록 합니다. 디스크 대기열(Queue)이 길다는 것은 시스템 메모리가 더 많이 필요하거나 향상된 디스크 하위 시스템이 필요하다는 것을 나타냅니다.





Microsoft 제품 관련 기술 전문가들과 온라인으로 정보를 교환하시려면 Microsoft 뉴스 그룹에 참여하시기 바랍니다.

속성

기술 자료: 224587 - 마지막 검토: 2007년 10월 26일 금요일 - 수정: 4.1
본 문서의 정보는 다음의 제품에 적용됩니다.
  • Microsoft SQL Server 7.0 Standard Edition
키워드:?
kbhowto kbhowtomaster kbinfo kbproductlink KB224587

피드백 보내기

 

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