SQL Server Profiler를 사용하여 계획 지침 작성 및 테스트Use SQL Server Profiler to Create and Test Plan Guides

계획 지침을 만들 때는 SQL Server 프로파일러SQL Server Profiler 를 사용하여 sp_create_plan_guide 저장 프로시저의 statement_text 인수에서 사용할 정확한 쿼리 텍스트를 캡처할 수 있습니다.When you are creating a plan guide, you can use SQL Server 프로파일러SQL Server Profiler to capture the exact query text for use in the statement_text argument of the sp_create_plan_guide stored procedure. 이렇게 하면 컴파일 시 계획 지침이 쿼리와 일치하도록 보장할 수 있습니다.This helps make sure that the plan guide will be matched to the query at compile time. 계획 지침을 만든 다음 SQL Server 프로파일러SQL Server Profiler 를 사용하여 실제로 계획 지침이 쿼리와 일치하는지 여부를 테스트할 수도 있습니다.After the plan guide is created, SQL Server 프로파일러SQL Server Profiler can also be used to test that the plan guide is, in fact, being matched to the query. 일반적으로 쿼리가 계획 지침과 일치하는지 확인하기 위해 SQL Server 프로파일러SQL Server Profiler 를 사용하여 계획 지침을 테스트해야 합니다.Generally, you should test plan guides by using SQL Server 프로파일러SQL Server Profiler to verify that your query is being matched to your plan guide.

SQL Server Profiler를 사용하여 쿼리 텍스트 캡처Capturing Query Text by Using SQL Server Profiler

쿼리를 실행하고 SQL ServerSQL Server 를 사용하여 SQL Server 프로파일러SQL Server Profiler에 전송된 대로 정확히 텍스트를 캡처하려는 경우 쿼리 텍스트와 정확히 일치하는 SQL 또는 TEMPLATE 유형의 계획 지침을 만들 수 있습니다.If you run a query and capture the text exactly as it was submitted to SQL ServerSQL Server by using SQL Server 프로파일러SQL Server Profiler, you can create a plan guide of type SQL or TEMPLATE that will match the query text exactly. 이렇게 하면 쿼리 최적화 프로그램에서 이 계획 지침이 사용되도록 보장할 수 있습니다.This makes sure that the plan guide is used by the query optimizer.

응용 프로그램에 의해 독립 실행형 일괄 처리로 전송되는 다음 쿼리를 고려하십시오.Consider the following query that is submitted by an application as a stand-alone batch:

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에 이 쿼리가 병합 조인을 사용하지 않는 것으로 표시된다고 가정해 보십시오.Suppose you want this query to execute using a merge join operation, but SHOWPLAN indicates that the query is not using a merge join. 응용 프로그램에서는 쿼리를 직접 변경할 수 없기 때문에 그 대신 컴파일 시 쿼리에 MERGE JOIN 쿼리 힌트를 첨부하도록 지정하는 계획 지침을 만듭니다.You cannot change the query directly in the application, so instead you create a plan guide to specify that the MERGE JOIN query hint be appended to the query at compile time.

SQL ServerSQL Server 에서 수신하는 것과 정확히 같은 쿼리 텍스트를 캡처하려면 다음 단계를 수행하십시오.To capture the text of the query exactly as SQL ServerSQL Server receives it, follow these steps:

  1. SQL Server 프로파일러SQL Server Profiler 추적을 시작하고 SQL:BatchStarting 이벤트 유형을 선택합니다.Start a SQL Server 프로파일러SQL Server Profiler trace, making sure that the SQL:BatchStarting event type is selected.

  2. 응용 프로그램이 쿼리를 실행하도록 합니다.Have the application run the query.

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

  4. 이 쿼리에 해당하는 SQL:BatchStarting 이벤트를 클릭합니다.Click the SQL:BatchStarting event that corresponds to the query.

  5. 마우스 오른쪽 단추로 클릭하고 이벤트 데이터 추출을 선택합니다.Right-click and select Extract Event Data.

    중요

    프로파일러 추적 창의 아래쪽 창에서 일괄 처리 텍스트를 선택하여 복사하려고 하지 마십시오.Do not try to copy the batch text by selecting it from the lower pane of the Profiler trace window. 이렇게 하면 만든 계획 지침이 원본 일괄 처리와 일치하지 않을 수 있습니다.This might cause the plan guide that you create to not match the original batch.

  6. 이벤트 데이터를 파일에 저장합니다.Save the event data to a file. 이 데이터는 일괄 처리 텍스트입니다.This is the batch text.

  7. 일괄 처리 텍스트 파일을 메모장에서 열고 텍스트를 복사 및 붙여 넣기 버퍼로 복사합니다.Open the batch text file in Notepad and copy the text to the copy and paste buffer.

  8. 계획 지침을 만들고 복사한 텍스트를@stmt인수에 대해 지정된 따옴표( @stmt ) 안에 붙여넣습니다.Create the plan guide and paste the copied text inside the quotation marks ('') specified for the @stmt argument. @stmt 인수에 있는 작은따옴표 앞에 다른 작은따옴표를 붙여 이스케이프 처리해야 합니다.You must escape any single quotation marks in the @stmt argument by preceding them with another single quotation mark. 작은따옴표를 삽입할 때는 다른 문자를 추가 또는 제거하지 않도록 주의해야 합니다.Be careful not to add or remove any other characters when you insert these single quotation marks. 예를 들어 날짜 리터럴 '20000101'''20000101''로 구분해야 합니다.For example, the date literal '20000101' must be delimited as ''20000101''.

    계획 지침은 다음과 같습니다.Here is the plan guide:

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를 사용하여 계획 지침 테스트Testing Plan Guides by Using SQL Server Profiler

계획 지침이 쿼리와 일치하는지 확인하려면 다음 단계를 수행하십시오.To verify that a plan guide is being matched to a query, follow these steps:

  1. SQL Server 프로파일러SQL Server Profiler 추적을 시작하고 성능 노드 아래에 있는 Showplan XML 이벤트 유형을 선택합니다.Start a SQL Server 프로파일러SQL Server Profiler trace, making certain that the Showplan XML event type is selected (located under the Performance node).

  2. 응용 프로그램이 쿼리를 실행하도록 합니다.Have the application run the query.

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

  4. 해당 쿼리에 대한 Showplan XML 이벤트를 찾습니다.Find the Showplan XML event for the affected query.

    참고

    Showplan XML for Query Compile 이벤트를 사용할 수 없습니다.The Showplan XML for Query Compile event cannot be used. 해당 이벤트에PlanGuideDB 가 없습니다.PlanGuideDB does not exist in that event.

  5. 계획 지침이 OBJECT 또는 SQL 유형인 경우 쿼리와 일치할 것으로 예상되는 계획 지침의 PlanGuideDBPlanGuideName 특성이 Showplan XML 이벤트에 포함되어 있는지 확인합니다.If the plan guide is of type OBJECT or SQL, verify that the Showplan XML event contains the PlanGuideDB and PlanGuideName attributes for the plan guide that you expected to match the query. 또는 TEMPLATE 계획 지침의 경우 예상되는 계획 지침의 TemplatePlanGuideDBTemplatePlanGuideName 특성이 Showplan XML 이벤트에 포함되어 있는지 확인합니다.Or, in the case of a TEMPLATE plan guide, verify that the Showplan XML event contains the TemplatePlanGuideDB and TemplatePlanGuideName attributes for the expected plan guide. 그러면 계획 지침이 작동하는지 확인할 수 있습니다.This verifies that the plan guide is working. 이러한 특성은 계획의 <StmtSimple> 요소에 포함됩니다.These attributes are contained under the <StmtSimple> element of the plan.

관련 항목:See Also

sp_create_plan_guide(Transact-SQL)sp_create_plan_guide (Transact-SQL)