SQL Server Profiler를 사용하여 계획 가이드 만들기 및 테스트

  • 아티클

적용 대상:SQL ServerAzure SQL Database

계획 지침을 만들 때 SQL Server Profiler를 사용하여 sp_create_plan_guide 저장 프로시저의 statement_text 인수에 사용할 정확한 쿼리 텍스트를 캡처할 수 있습니다. 이렇게 하면 계획 지침이 컴파일 시간에 쿼리와 일치하도록 할 수 있습니다. 계획 가이드를 만든 후 SQL Server Profiler를 사용하여 계획 가이드가 실제로 쿼리와 일치하는지 테스트할 수도 있습니다. 일반적으로 SQL Server Profiler를 사용하여 계획 지침을 테스트하여 쿼리가 계획 가이드와 일치하는지 확인해야 합니다.

SQL Server Profiler를 사용하여 쿼리 텍스트 캡처

쿼리를 실행하고 SQL Server 프로파일러를 사용하여 SQL Server에 제출된 대로 텍스트를 캡처하는 경우 쿼리 텍스트와 정확히 일치하는 SQL 또는 TEMPLATE 형식의 계획 지침을 만들 수 있습니다. 이렇게 하면 계획 가이드가 쿼리 최적화 프로그램에서 사용됩니다.

애플리케이션에서 독립 실행형 일괄 처리로 제출하는 다음 쿼리를 고려합니다.

SELECT COUNT(*) AS c  
FROM Sales.SalesOrderHeader AS h  
INNER JOIN Sales.SalesOrderDetail AS d  
  ON h.SalesOrderID = d.SalesOrderID  
WHERE h.OrderDate BETWEEN '20000101' and '20050101';

병합 조인 작업을 사용하여 이 쿼리를 실행하려고 하지만 SHOWPLAN은 쿼리가 병합 조인을 사용하고 있지 않음을 나타낸다고 가정합니다. 애플리케이션에서 쿼리를 직접 변경할 수 없으므로 대신 컴파일 시간에 MERGE JOIN 쿼리 힌트를 쿼리에 추가하도록 지정하는 계획 지침을 만듭니다.

SQL Server가 수신하는 대로 쿼리의 텍스트를 정확히 캡처하려면 다음 단계를 수행합니다.

  1. SQL Server Profiler 추적을 시작하여 SQL:BatchStarting 이벤트 유형이 선택되어 있는지 확인합니다.

  2. 애플리케이션에서 쿼리를 실행하게 합니다.

  3. SQL Server 프로파일러 추적을 일시 중지합니다.

  4. 쿼리에 해당하는 SQL:BatchStarting 이벤트를 클릭합니다.

  5. 마우스 오른쪽 단추를 클릭하고 이벤트 데이터 추출을 선택합니다.

    Important

    프로파일러 추적 창의 아래쪽 창에서 선택하여 일괄 처리 텍스트를 복사하지 마세요. 이로 인해 만든 계획 지침이 원래 일괄 처리와 일치하지 않을 수 있습니다.

  6. 이벤트 데이터를 파일에 저장합니다. 일괄 처리 텍스트입니다.

  7. 메모장에서 일괄 처리 텍스트 파일을 열고 텍스트를 복사하여 버퍼에 붙여넣습니다.

  8. 계획 지침을 만들고 복사한 텍스트를@stmt인수에 대해 지정된 따옴표( '' ) 안에 붙여넣습니다. 다른 작은따옴표 앞에 @stmt 인수에서 작은따옴표를 이스케이프해야 합니다. 이러한 작은따옴표를 삽입할 때 다른 문자를 추가하거나 제거하지 않도록 주의하세요. 예를 들어 날짜 리터럴 '20000101'은 ''20000101''으로 구분되어야 합니다.

계획 가이드는 다음과 같습니다.

EXEC sp_create_plan_guide   
    @name = N'MyGuide1',  
    @stmt = N'<paste the text copied from the batch text file here>',  
    @type = N'SQL',  
    @module_or_batch = NULL,  
    @params = NULL,  
    @hints = N'OPTION (MERGE JOIN)';

SQL Server Profiler를 사용하여 계획 가이드 테스트

계획 지침이 쿼리와 일치하는지 확인하려면 다음 단계를 수행합니다.

  1. SQL Server Profiler 추적을 시작하여 실행 계획 XML 이벤트 유형이 선택되었는지 확인합니다(성능 노드 아래에 있음).

  2. 애플리케이션에서 쿼리를 실행하게 합니다.

  3. SQL Server 프로파일러 추적을 일시 중지합니다.

  4. 영향을 받는 쿼리에 대한 Showplan XML 이벤트를 찾습니다.

    참고 항목

    Showplan XML for Query Compile 이벤트를 사용할 수 없습니다. PlanGuideDB 는 해당 이벤트에 존재하지 않습니다.

  5. 계획 지침이 OBJECT 또는 SQL 유형인 경우 쿼리와 일치할 것으로 예상되는 계획 지침의 PlanGuideDB 및 PlanGuideName 특성이 Showplan XML 이벤트에 포함되어 있는지 확인합니다. 또는 TEMPLATE 계획 가이드의 경우 Showplan XML 이벤트에 예상 계획 가이드에 대한 TemplatePlanGuideDB 및 TemplatePlanGuideName 특성이 포함되어 있는지 확인합니다. 그러면 계획 가이드가 작동하는지 확인합니다. 이러한 특성은 계획의 StmtSimple> 요소 아래에 <포함됩니다.

참고 항목

sp_create_plan_guide(Transact-SQL)