SQL Server에서 임의(Ad-Hoc) 쿼리의 성능 문제를 해결하는 방법

 

SQL Server에서 임의(Ad-Hoc) 쿼리의 성능 문제를 해결하는 방법

이 페이지에서

요약

성능 문제의 특징

쿼리 컴파일의 개선 사항

매개 변수가 있는 쿼리 개발

성능 모니터 데이터 보기

SQL 프로파일러 데이터 보기

이벤트 유형

참조

요약

이 문서에서는 Microsoft SQL Server에서 동시에 많은 임의(ad-hoc) 쿼리 실행으로 인해 속도가 느려지는 성능 문제를 해결하는 방법을 설명합니다. 문제의 정확한 원인을 확인하지 못한 경우에는 먼저 Microsoft 기술 자료의 다음 문서를 참조하십시오.

224587 (http://support.microsoft.com/kb/224587/) HOWTO: SQL Server에서 응용 프로그램 성능 문제 해결


이 문서에서는 KB 224587을 사용하여 문제의 범위를 좁히고 특정 카운터, 이벤트 및 데이터 열에 대한 정보가 기록된 Windows NT 성능 모니터 로그와 SQL 프로파일러 추적을 캡처했다고 간주합니다.

위로 가기

성능 문제의 특징

성능 문제에는 다음과 같은 특징이 있습니다.

•	일반적으로 매우 짧은 기간의 짧은 임의(ad-hoc) 쿼리를 많은 사용자가 동시에 실행하는 경우 전체적으로 시스템 성능이 느려집니다.
•	CPU의 대부분 또는 전체를 사용합니다
•	성능이 느린 기간 동안 관련된 차단이 발생하지 않습니다. sp_who 시스템 저장 프로시저의 출력에서 BLK 열을 확인하여 차단을 신속하게 확인할 수 있습니다. 많은 SPID(시스템 프로세스 ID)에서 BLK열이 0이 아니면 차단이 발생한 것입니다.
•	어떤 경우에는 서버 메모리에 과도한 부하가 발생하여 다음과 같은 오류가 나타날 수 있습니다. 오류: 701, 심각도: 17, 상태: 1 시스템 메모리가 부족하여 이 쿼리를 실행할 수 없습니다. 또는 메시지 8645, 수준 17, 상태 1, 프로시저, 줄 1 메모리 리소스가 쿼리를 실행하기를 기다리는 중 시간이 초과되었습니다. 쿼리를 다시 실행하십시오.

위로 가기

쿼리 컴파일의 개선 사항

SQL Server 7.0부터 시스템 아키텍처가 향상되었고 특히 쿼리 최적화 프로그램이 향상되었기 때문에 SQL Server의 이전 버전과 비교할 때 응용 프로그램의 시스템 리소스 사용이 달라졌습니다. 특히 SQL Server 7.0에서는 대개 디스크 IO 중심인 이전 버전의 SQL Server와 달리 CPU 및 메모리 사용이 증가할 수 있습니다. 이러한 변경은 다음 두 가지 요인에 따른 것입니다.

•	해시 및 병합 조인
•	쿼리 컴파일 시간

이전 버전의 SQL Server에서는 중첩 루프 반복에 완전히 의존하여 조인을 실행합니다. 중첩 루프 조인은 본질적으로 디스크 IO를 사용합니다. SQL Server 7.0부터 해시 및 병합 조인이 도입되었습니다. 해시 및 병합 조인의 경우 중첩 루프 조인의 경우보다 메모리 내 처리 작업을 더 많이 수행합니다. 이로 인해 해시 및 병합 조인 기술을 사용하는 경우 CPU 및 메모리를 더 많이 사용하게 됩니다. 해시 및 병합 조인에 대한 자세한 내용은 SQL Server 7.0 온라인 설명서의 "Understanding Hash Joins" 및 "Understanding Merge Joins" 항목을 참조하십시오.

이전 버전의 SQL Server에서보다 쿼리 최적화 프로그램에 사용할 수 있는 옵션과 정보가 더 많기 때문에(예: 새 해시 및 병합 조인 기술, 향상된 검색 알고리즘 및 열 통계) 쿼리 컴파일 시간이 영향을 받습니다. 이러한 추가 정보를 통해 쿼리 최적화 프로그램에서는 가장 효율적인 방법을 선택하여 쿼리 데이터를 검색할 수 있습니다. 그러나 이러한 새 기술과 정보를 분석하고 고려하려면 처리 시간이 필요합니다. CPU 사용 증가로 인해 이전 버전의 SQL Server에서보다 쿼리 컴파일 시간이 더 오래 걸릴 수 있습니다.

대부분의 쿼리에서 이러한 컴파일 시간의 증가는 실행 시간을 줄여서 상쇄할 수 있습니다. 전반적으로 이전 버전의 SQL Server에 비해 쿼리가 더 빨리 실행됩니다. 그러나 실행 시간이 매우 짧은 매우 작고 단순한 OLTP 형식 쿼리의 경우는 여기에서 제외됩니다. 이러한 경우에는 쿼리 계획을 생성하는 프로세스에 쿼리 실행 이상의 비용이 들 수 있습니다. 결과적으로 쿼리가 이전 버전의 SQL Server에서보다 약간 느리게 실행될 수 있습니다. 일반적으로 이 차이는 밀리초 단위이기 때문에 특정 쿼리가 개별적으로 실행되는 경우에는 차이를 알아챌 수 없습니다. 그러나 다수의 사용자가 많은 임의(ad-hoc) 쿼리를 동시에 실행하면 이전 버전의 SQL Server에 비해 전체적인 시스템 CPU 사용이 증가합니다.

위로 가기

매개 변수가 있는 쿼리 개발

SQL Server 7.0에서는 임의(ad-hoc) 쿼리 캐시 및 자동 매개 변수화와 같은 새로운 기술을 사용합니다. 그러나 SQL Server 7.0에서 자동으로 매개 변수화하는 쿼리는 제한되어 있습니다. 쿼리 계획을 매개 변수화하고 더 효과적으로 재사용할 수 있도록 하려면 다음과 같은 방법을 사용하십시오.

•	매개 변수 표식 OLE DB 및 ODBC API에서는 쿼리를 제출할 때 매개 변수를 물음표로 지정할 수 있습니다. 이는 모든 응용 프로그램에 매우 유용하지만 특히 저장 프로시저를 사용할 수 없는 쿼리 생성 모듈이 있는 중간 계층 응용 프로그램에 유용합니다. 매개 변수 표식이 있는 쿼리에 대해 생성된 쿼리 계획은 다른 매개 변수 값이 지정되더라도 같은 쿼리를 실행하는 모든 클라이언트에서 재사용할 수 있습니다. 자세한 내용은 SQL Server 7.0 온라인 설명서의 "Parameter Markers" 항목을 참조하십시오.
•	sp_executesql sp_executesql 저장 프로시저는 응용 프로그램에서 매개 변수 표식을 사용할 때 OLE DB 공급자 또는 ODBC 드라이버에서 호출됩니다. 그러나 이 저장 프로시저는 임의(ad-hoc) 쿼리를 명시적으로 매개 변수화하기 위해 응용 프로그램이나 다른 저장 프로시저에서 직접 호출될 수도 있습니다. 이는 EXECUTE 문을 사용하여 동적 SQL 문을 실행하는 응용 프로그램이나 배치 파일에 매우 유용합니다. sp_executesql과 달리 EXECUTE 문에서는 매개 변수화가 허용되지 않기 때문에 쿼리 계획 재사용의 기회가 제한됩니다. 자세한 내용은 SQL Server 7.0 온라인 설명서의 "sp_executesql (T-SQL)" 및 "Using sp_executesql" 항목을 참조하십시오.
•	저장 프로시저 저장 프로시저에는 쿼리를 매개 변수화하고 실행 계획을 재사용하는 기능을 비롯한 여러 장점이 있습니다. 자세한 내용은 SQL Server 7.0 온라인 설명서의 "Stored Procedures" 및 "Programming Stored Procedures" 항목을 참조하십시오.

위로 가기

성능 모니터 데이터 보기

병목 상태를 발생시키는 시스템 리소스를 확인하기 위해 성능 모니터 로그를 사용합니다. 성능 모니터 로그를 통해 시스템의 전체적인 상태를 보고 SQL 프로파일러 데이터를 볼 때 중점적으로 살펴볼 항목을 결정할 수 있습니다. 성능이 좋을 때부터 성능이 저하될 때까지의 성능 모니터 데이터를 검토하고 처음 영향을 받은 카운터를 결정한 후 다음 문제 중 해당 상황과 가장 관련이 큰 것을 결정합니다.

•	개체: Process 카운터: Processor 인스턴스: SQL Server
•	개체: Processor 카운터: %Processor Time 인스턴스: 각 프로세서 인스턴스 확인
•	개체: SQL Server:Buffer Manager 카운터: Free Buffers
•	개체: SQL Server:Buffer Manager 카운터: Stolen Page Count
•	개체: SQL Server:Memory Manager 카운터: Memory Grants Pending
•	개체: SQL Server:SQL Statistics 카운터: SQL Compilations/sec

CPU 사용률과 SQL Compilations/sec 및 Free Buffers 카운터 값이 크고 Memory Grants Pending 및 Stolen Page Count 카운터 값이 작으면 CPU가 병목 상태에 있습니다. 이 경우에는 쿼리 계획 생성에 비용을 들이지 않기 위해 쿼리 계획을 효과적으로 매개 변수화하고 재사용하는 방법을 집중적으로 살펴보고 이 문서의 "이벤트 클래스별 SQL 프로파일러 추적 그룹화" 절을 참조하십시오. Free Buffers 및 SQL Compilations/sec 카운터 값이 작고 Stolen Page Count 및 Memory Grants Pending 카운터 값이 크면 SQL Server에서 사용할 수 있는 메모리가 제한됩니다. 이 경우에는 해시 조인을 사용하고 해시 조인을 루프 조인으로 변경할 수 있는 쿼리를 찾는 데 집중하고 이 문서의 "기간별 SQL 프로파일러 추적 그룹화" 절을 참조하십시오. 이러한 카운터에 대한 자세한 내용을 보려면 SQL Server 7.0 온라인 설명서에서 해당 카운터 이름을 검색하십시오.

위로 가기

SQL 프로파일러 데이터 보기

성능 문제를 해결할 때 SQL 프로파일러 데이터를 보면 매우 유용합니다. 캡처한 데이터를 모두 검토할 필요는 없고 선택하여 보면 됩니다. SQL 프로파일러는 캡처된 데이터를 효과적으로 보는 데 도움이 됩니다. SQL 프로파일러를 사용하면 파일 메뉴에서 속성을 누르면 나타나는 속성 탭에서 데이터 열이나 이벤트를 제거하거나, 데이터 열을 기준으로 그룹화 또는 정렬하거나, 필터를 적용하여 표시되는 데이터를 제한할 수 있습니다. 편집 메뉴에서 찾기를 눌러 전체 추적이나 특정 열에서만 특정 값을 검색할 수 있습니다. 또한 파일 메뉴에서 다른 이름으로 저장을 가리키고 추적 테이블을 눌러 SQL 프로파일러 데이터를 SQL Server 테이블에 저장한 다음 이에 대해 SQL 쿼리를 실행할 수도 있습니다.

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

또한 가장 큰 이익을 얻을 수 있는 영역에 집중해야 합니다. 아래에 열거된 요소가 응용 프로그램의 성능을 높이는 데 도움이 되지만 반드시 동일한 정도로 영향을 주는 것은 아닙니다. 변경 사항을 구현하기 전에 다음 요소에 따라 변경 사항의 효과가 어떻게 달라지는지 확인해야 합니다.

•	쿼리 실행 빈도
•	쿼리 개선 정도

예를 들어, 쿼리 하나의 실행 시간을 1.5초에서 1.2초로 줄이는 것은 쿼리가 자주 실행되지 않는 경우에 별로 유용하지 않습니다. 그러나 수많은 사용자가 동시에 쿼리를 아주 자주 실행하면 성능 향상 효과가 매우 클 수 있습니다. 반대로 쿼리 하나의 실행 시간을 6분에서 3초로 향상시키면 쿼리를 거의 사용하지 않는 경우 전체 성능이 눈에 띄게 향상되지 않습니다. 변경 사항을 구현하기 전에 특정 쿼리 또는 프로시저의 효과를 예측하려면 응용 프로그램에 대한 사용자 지식과 SQL 프로파일러의 그룹화 및 필터링 기술을 사용하십시오. 가장 효과가 큰 변경 사항에 우선 집중한 다음 성능이 충분히 향상된 수준에 이를 때까지 다른 쿼리와 프로시저를 통해 계속 반복합니다.

SQL 프로파일러 추적을 파일이나 테이블에 저장한 후 SQL 프로파일러에서 추적을 다시 열고 내용을 검토합니다. SQL 프로파일러 추적을 그룹화하려면 다음과 같이 하십시오.

•

기간별 SQL 프로파일러 추적 그룹화: 1. 파일 메뉴에서 속성을 누릅니다. 2. 데이터 열 탭을 누른 다음 그룹에서 위로를 눌러 기간을 이동합니다. 아래로를 눌러 다른 모든 열을 제거합니다. 3. 이벤트 탭을 누른 다음 TSQL SQL:StmtCompleted 및 TSQL RPC:Completed를 제외하고 모든 이벤트를 제거합니다. 이렇게 하면 실행 중인 쿼리에만 집중할 수 있습니다. 4. 확인을 누릅니다. 기간별로 그룹화하면 가장 느리게 실행되는 SQL 문, 일괄 처리 및 프로시저를 쉽게 볼 수 있습니다. 문제가 발생한 시기의 추적을 검토하고 좋은 성능의 기준을 만듭니다. 시작 시간으로 필터링하여 추적을 성능이 좋은 경우의 섹션과 성능이 나쁜 경우의 섹션으로 나눌 수 있습니다. 성능이 좋을 때 기간이 가장 긴 쿼리를 찾습니다. 이러한 쿼리가 문제의 근본 원인일 가능성이 가장 큽니다. 전체 시스템 성능이 저하되면 양호한 쿼리도 시스템 리소스를 기다리고 있으므로 긴 기간을 나타낼 수 있습니다. 가장 자주 기간이 길어지는 쿼리의 실행 계획을 검토합니다. 해시 조인이 사용된 경우에는 LOOP JOIN 쿼리 힌트를 사용하여 쿼리에 중첩 루프 조인을 적용하는 것을 고려하십시오. 루프 조인을 사용하는 쿼리의 실행 시간이 해시 조인을 사용하는 실행 시간보다 작거나, 같거나, 약간 높은 경우 컴퓨터가 메모리 및 CPU를 많이 사용할 경우 루프 조인을 선택하는 것이 더 바람직합니다. 리소스 병목(CPU 및 메모리)에서 과도한 부하를 줄이면 전체 시스템 성능을 향상시킬 수 있습니다. LOOP JOIN 쿼리 힌트에 대한 자세한 내용은 SQL Server 7.0 온라인 설명서의 "SELECT (T-SQL)" 항목을 참조하십시오.

•

이벤트 클래스별 SQL 프로파일러 추적 그룹화: 1. 파일 메뉴에서 속성을 누릅니다. 2. 데이터 열 탭을 누른 다음 그룹 머리글에서 위로를 눌러 이벤트 클래스와 텍스트를 이동하되 이벤트 클래스를 맨 위에 놓습니다. 아래로를 눌러 그룹 머리글 아래의 다른 모든 열을 제거합니다. 3. 이벤트 탭을 누른 다음 모든 이벤트가 포함되었는지 확인합니다. 4. 확인을 누릅니다.

위로 가기

이벤트 유형

SQL Server를 실행하는 컴퓨터에서 발생하는 이벤트 유형과 이벤트 발생 빈도를 확인하려면 이벤트 클래스 열별로 그룹화합니다. 이 열에서 다음과 같은 이벤트를 검색합니다.

•	MISC: Prepare SQL and Exec Prepared SQL; CURSORS: Cursorprepare Prepare SQL 이벤트는 전진 전용, 읽기 전용, 행 집합 크기 = 1인 기본 커서 옵션이 있는 SQLPrepare/SQLExecute(ODBC용) 또는 ICommandText::Prepare/ICommandText::Execute(OLE DB용)를 사용하여 SQL 문을 기본 결과 집합(클라이언트측 커서)과 함께 사용할 수 있도록 준비했음을 나타냅니다. Cursorprepare 이벤트는 위의 커서 옵션 중 하나가 기본값이 아닌 값으로 설정된 SQLPrepare/SQLExecute(ODBC용) 또는 ICommandText::Prepare/ICommandText::Execute(OLE DB용)를 사용하여 SQL 문에서 서버측 커서를 준비했음을 나타냅니다. Exec Prepared SQL 이벤트는 위의 유형으로 된 기존의 준비된 SQL 문 중 하나가 실행되었음을 나타냅니다. 이러한 이벤트가 자주 발생하면 응용 프로그램에서 결과 집합을 열 때 준비/실행 모델을 사용하고 있는 것입니다. 이 경우 준비/실행 모델을 올바르게 사용하고 있는지 확인해야 합니다. 최적화 프로그램에서 SQL 문이 실행될 때마다 새로운 계획을 컴파일할 필요가 없도록 응용 프로그램에서 SQL 문을 한 번 준비하고 여러 번 실행하는 것이 이상적입니다. 준비된 문을 실행할 때마다 쿼리 컴파일 비용이 절약됩니다. 쿼리를 한 번만 실행할 계획이면 해당 SQL 문을 준비하지 않는 것이 좋습니다. SQL 문을 준비하고 실행하려면 네트워크를 세 번 왕복해야 합니다. SQL 문을 준비하기 위해 한 번, SQL 문을 실행하기 위해 한 번, SQL 문의 준비를 해제하기 위해 한 번 왕복해야 합니다. 서버측 커서를 준비하려면 최소한 5번 왕복해야 합니다. 커서를 준비하기 위해 한 번, 커서를 실행하거나 열기 위해 한 번, 커서를 반입하기 위해 한 번 이상, 커서를 닫기 위해 한 번, 커서 준비를 해제하기 위해 한 번 왕복해야 합니다. 쿼리를 실행하려면 한 번만 왕복하면 됩니다. 응용 프로그램에서 준비/실행 모델을 효과적으로 사용하는지 확인하려면 이러한 두 이벤트(준비 및 실행)가 발생하는 횟수를 비교합니다. Exec Prepared SQL 이벤트의 수는 Prepare SQL 및 CursorPrepare 이벤트의 합계보다 훨씬 많아야 합니다(적어도 3-5배 많아야 함). 이는 준비된 SQL 문이 해당 문을 만들기 위해 늘어난 오버헤드를 극복할 만큼 충분히 자주 사용된다는 것을 나타냅니다. Prepare SQL 및 CursorPrepare 이벤트의 수가 Exec Prepared SQL 이벤트의 수와 거의 비슷하면 응용 프로그램에서 준비/실행 모델을 효과적으로 사용하고 있지 않음을 나타냅니다. SQL 문을 한 번 준비하고 가능한 한 많이 재사용하도록 하십시오. SQL 문을 한 번 준비하고 재사용하도록 응용 프로그램을 변경할 수도 있습니다. 응용 프로그램은 준비/실행 모델을 효율적으로 사용하도록 명시적으로 작성되어야 합니다. 준비된 SQL 문에 대한 핸들의 수명은 HSTMT를 ODBC에서 열어둔 기간이나 ICommandText 개체를 OLE DB에서 열어둔 기간에 따라 제어됩니다. 일반적으로 HSTMT을 얻고 SQL 문을 준비하고 준비된 SQL 문을 실행한 후 HSTMT를 해제하여 준비된 계획에 대한 핸들을 잃게 됩니다. 이렇게 하면 준비/실행 모델에서 아무런 이점도 얻을 수 없습니다. 실제로 네트워크 왕복으로 추가된 오버헤드 때문에 성능이 저하될 수 있습니다. 응용 프로그램에는 준비된 SQL 문 핸들로 HSTMT나 개체를 캐시하고 재사용을 위해 HSTMT나 개체에 액세스하는 몇 가지 메서드가 있어야 합니다. 드라이버나 공급자가 이러한 작업을 자동으로 수행하지 않기 때문에 응용 프로그램이 이 정보를 구현하고, 유지 관리하고, 사용하는 작업을 담당합니다. 응용 프로그램에서 이러한 작업을 수행할 수 없으면 준비/실행 모델 대신 매개 변수 표식을 사용하는 것을 고려하십시오.
•	매개 변수 표식 사용 매개 변수 표식을 사용하면 응용 프로그램에서 다른 입력 및 출력 값을 사용하여 동일한 Transact-SQL 문을 여러 번 사용하는 것을 최적화할 수 있습니다. 쿼리는 처음 실행될 때 매개 변수화된 쿼리로 준비되고, SQL Server에서는 쿼리에 대해 매개 변수화된 계획을 만들고 캐시합니다. 같거나 다른 매개 변수를 사용하여 이후에 동일한 쿼리를 호출하는 경우 SQL Server에서 새로운 쿼리 계획을 생성할 필요가 없습니다. SQL Server에서는 현재 매개 변수를 대체하여 기존 쿼리 계획을 재사용할 수 있습니다. 응용 프로그램에서 SQLExecDirect(ODBC용) 또는 ICommandText::Execute(OLE DB용)를 호출할 때 매개 변수 표식을 사용하는 경우 드라이버나 공급자는 자동으로 SQL 문을 패키지로 만들어 sp_executesql 호출로 실행합니다. 문을 따로 준비하고 실행할 필요가 없습니다. SQL Server에서 sp_executesql에 대한 호출을 받으면 일치하는 계획이 있는지 프로시저 캐시를 자동으로 검사하고 해당 계획을 재사용하거나 새 계획을 생성합니다. 응용 프로그램에서 현재 매개 변수 표식을 사용하고 있는지 확인하려면 SQL 프로파일러 추적의 텍스트 열에서 "sp_executesql"을 검색하면 됩니다. 그러나 sp_executesql이 직접 호출될 수 있으므로 모든 인스턴스가 매개 변수 표식의 사용을 나타내지는 않습니다. 준비/실행 모델에 대한 자세한 내용은 SQL Server 7.0 온라인 설명서의 "Execution Plan Caching and Reuse" 항목을 참조하십시오. 매개 변수 표식에 대한 자세한 내용은 SQL Server 7.0 온라인 설명서의 "Parameter Markers" 항목을 참조하십시오.
•	SP:Completed EXECUTE 명령으로 실행되는 동적 SQL 문은 "Dynamic SQL" 텍스트가 있는 SP:Completed 이벤트로 나타납니다. SP:Completed 이벤트를 확장하고 "Dynamic SQL" 텍스트가 있는지 검색하십시오. 이러한 이벤트가 많으면 EXECUTE 문 대신 sp_executesql을 사용하여 응용 프로그램 성능을 향상시킬 수도 있습니다. sp_executesql 저장 프로시저를 사용하면 동일한 쿼리가 다른 매개 변수를 사용하여 다시 실행되는 경우 SQL Server에서 실행 계획을 재사용할 수 있습니다. EXECUTE 문을 사용하는 경우에는 계획이 매개 변수화되지 않으며 쿼리가 동일한 매개 변수를 사용하여 다시 실행되지 않는 한 재사용되지 않습니다. EXCUTE 문으로 동적 SQL 이벤트를 사용하는 쿼리나 프로시저를 확인하려면 각 이벤트의 연결 ID와 시작 시간을 살펴봅니다. 그런 다음 그룹 머리글에서 이벤트 클래스 및 텍스트를 제거하여 추적 그룹을 해제합니다. 추적 그룹을 해제하면 추적이 날짜순으로 정렬됩니다. 필터 탭에서 연결 ID별로 추적을 필터링한 다음 좀 더 읽기 쉽도록 SP:Starting 및 SP:Complete 이벤트를 제외한 모든 이벤트 클래스를 제거할 수 있습니다. 그런 다음 편집 메뉴에서 찾기를 눌러 이벤트의 시작 시간을 검색할 수 있습니다. 결과에 동적 SQL 이벤트가 시작된 시간이 나타납니다. 저장 프로시저에서 이벤트가 발생한 경우 이벤트가 해당 프로시저의 SP:Starting 및 SP:Completed 이벤트 사이에 나타납니다. 이벤트가 저장 프로시저에서 발생하지 않은 경우에는 임의(ad-hoc) 쿼리로 실행된 것이므로 다른 데이터 열(응용 프로그램 이름, NT 사용자 이름 등)을 사용하여 명령이 실행된 곳을 확인할 수 있습니다. 이벤트가 실행된 컨텍스트 및 명령의 텍스트를 확인하려면 SQL:BatchCompleted 및 SQL:RPCCompleted와 같은 이벤트 클래스를 추가할 수도 있습니다. EXECUTE 문이 사용되는 곳을 찾은 후에는 이 문을 사용하는 대신 sp_executesql을 호출하는 것을 고려하십시오. 예를 들어, EXECUTE 문이 동적 SQL에 사용되는 경우 다음과 같은 시나리오를 생각해 봅시다. 프로시저에서 입력 매개 변수로 테이블 이름, ID 및 idValue를 사용하고 ID 값을 기반으로 테이블에서 SELECT 문을 실행합니다. EXECUTE 문을 사용하는 경우 프로시저는 다음 코드와 유사합니다. drop proc dynamicUsingEXECUTE go create proc dynamicUsingEXECUTE @table sysname, @idName varchar(10), @idValue varchar(10) as declare @query nvarchar(4000) -- Build query string with parameter. -- Notice the use of escape quotes. select @query = 'select * from ' + @table + ' where ' + @idName + ' = ''' + @idValue + '''' exec (@query) go 쿼리가 자동으로 매개 변수화되지 않는다고 가정하는 경우 pubs 예제 데이터베이스에 있는 titles 테이블에 대해 @idValue 매개 변수에 다른 값을 사용하여 이 프로시저를 두 번 실행하면 SQL Server에서 각 실행에 대해 별도의 쿼리 계획을 생성해야 합니다. 예를 들면 다음과 같습니다. exec dynamicUsingEXECUTE 'titles', 'title_id', 'MC2222' go exec dynamicUsingEXECUTE 'titles', 'title_id', 'BU7832' 참고 이 예제에서 쿼리는 SQL Server에서 자동으로 쿼리를 매개 변수화하고 실제로 실행 계획을 재사용할 수 있을 정도로 간단합니다. 그러나 SQL Server가 자동으로 매개 변수화할 수 없는 복잡한 쿼리인 경우에는 @idValue 매개 변수가 변경되었을 때 SQL Server에서 두 번째 실행에 계획을 재사용할 수 없습니다. 아래의 간단한 쿼리는 예제의 복잡성을 제한합니다. EXECUTE 문 대신 sp_executesql을 사용하도록 이 프로시저를 다시 작성할 수 있습니다. 매개 변수 대체를 지원하면 SQL Server에서 실행 계획을 재사용할 가능성이 높아지므로 sp_executesql이 더 효율적입니다. 예를 들면 다음과 같습니다. drop proc dynamicUsingSP_EXECUTESQL go create proc dynamicUsingSP_EXECUTESQL @table sysname, @idName varchar(10), @idValue varchar(10) as declare @query nvarchar(4000) -- Build query string with parameter select @query = 'select * from ' + @table + ' where ' + @idName + ' = @idValue' -- Now execute with parameter exec sp_executesql @query, N'@idValue varchar(10)', @idValue go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id', 'MC2222' go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id', 'BU7832' 이 예제에서 sp_executesql 문이 처음 실행되면 SQL Server에서는 title_id를 매개 변수로 사용하는 titles에서 SELECT 문에 대해 매개 변수화된 계획을 생성합니다. 두 번째 실행할 때는 SQL Server에서 새 매개 변수 값과 함께 계획을 재사용합니다. sp_executesql에 대한 자세한 내용은 SQL Server 7.0 온라인 설명서의 "sp_executesql (T-SQL)" 및 "Using sp_executesql" 항목을 참조하십시오.
•	SP:RECOMPILES 이 이벤트는 저장 프로시저가 실행 중에 다시 컴파일되었음을 나타냅니다. 재컴파일 이벤트가 많은 경우 SQL Server가 쿼리 실행 대신 쿼리 컴파일에 리소스를 사용하고 있음을 나타냅니다.

이러한 이벤트가 나타나지 않으면 응용 프로그램에서 SQL Server에 대해 임의(ad-hoc) 쿼리만 실행하고 있는 것입니다. SQL Server에서 특정 쿼리를 자동으로 매개 변수화할 수 있거나 동일한 매개 변수가 반복적으로 사용되는 경우가 아니면 실행되는 각 쿼리에 대해 SQL Server에서 새 실행 계획을 생성해야 합니다. SQL Server 성능 모니터에는 많은 SQL Compilations/sec가 표시됩니다. 이것은 많은 사용자가 동시에 CPU를 많이 사용하고 있음을 나타낼 수 있습니다. 이 문제를 해결하려면 가장 자주 실행되는 쿼리를 찾아서 이 쿼리에 대한 저장 프로시저를 만들거나 매개 변수 표식 또는 sp_executesql을 사용하는 것을 고려하십시오.

위로 가기

참조

SQL Server에서 성능을 모니터링하고 성능 문제를 해결하는 방법에 대한 자세한 내용은 Microsoft 기술 자료의 다음 문서를 참조하십시오.

224587 (http://support.microsoft.com/kb/224587/) HOWTO: SQL Server에서 응용 프로그램 성능 문제 해결

224453 (http://support.microsoft.com/kb/224453/) INF: SQL Server 7.0 블로킹 문제 파악 및 해결 방법

243586 (http://support.microsoft.com/kb/243586/) 저장 프로시저 재컴파일 문제 해결

243589 (http://support.microsoft.com/kb/243589/) INF: SQL Server 7.0에서 느리게 실행되는 쿼리 문제 해결 방법

251004 (http://support.microsoft.com/kb/251004/) INF: SQL Server 7.0 블로킹 모니터링 방법




원본 위치 <http://support.microsoft.com/kb/243588/ko>