아티클
09/27/2010

커서 쿼리에서 USE PLAN 쿼리 힌트 사용

커서 요청을 지정하는 쿼리에서 USE PLAN 쿼리 힌트를 사용할 수 있습니다. 다음 표에서는 API 서버 커서, Transact-SQL 확장 구문을 사용하는 Transact-SQL 커서 및 ISO 구문을 사용하는 Transact-SQL 커서에 대해 USE PLAN에서 지원되는 커서 스크롤 옵션 조합을 보여 줍니다.

스크롤 옵션(API 서버 커서에 대한 @scrollopt 값)	API 서버 커서에 지원되는 USE PLAN	Transact-SQL 확장 구문을 사용하는 Transact-SQL 커서에 지원되는 USE PLAN	ISO 구문을 사용하는 Transact-SQL 커서에 지원되는 USE PLAN
STATIC	Y	Y	해당 사항 없음
DYNAMIC	N	N	해당 사항 없음
KEYSET	N	N	해당 사항 없음
FORWARD_ONLY	N	N	해당 사항 없음
FAST_FORWARD	Y	Y	해당 사항 없음
FORWARD_ONLY STATIC	해당 사항 없음	Y	해당 사항 없음
INSENSITIVE	해당 사항 없음	해당 사항 없음	Y

커서를 사용하지 않고 전송되는 쿼리와 관련된 계획은 하나가 있지만 커서가 있는 쿼리에는 쿼리와 관련된 쿼리 계획이 두 개 있습니다. 이러한 계획의 형식으로는 커서의 유형에 따라 OPEN, FETCH 또는 REFRESH 등이 있습니다.

커서에 대한 두 가지 계획 중 하나는 입력 쿼리에서 직접 생성되고 다른 하나는 자동으로 생성됩니다. 이 두 계획을 각각 입력 쿼리 계획과 생성된 계획이라고 합니다. 아래의 표에서는 FAST_FORWARD 및 STATIC(INSENSITIVE) 커서에 대해 생성되는 계획을 보여 줍니다.

커서 유형	Open 커서 계획	Fetch 커서 계획	Refresh 커서 계획
FAST_FORWARD	해당 사항 없음	입력 쿼리 계획	생성된 계획
STATIC	입력 쿼리 계획	생성된 계획	해당 사항 없음

커서 쿼리에 대한 XML 쿼리 계획은 때때로 두 계획이 모두 포함된 하나의 XML 문서로 나타납니다. 이러한 계획을 두 부분으로 이뤄진 계획이라고 합니다.

커서에 대한 계획은 별도의 두 계획으로 표시되기도 합니다. 예를 들어 STATIC API 또는 Transact-SQL 커서 쿼리 계획에 대한 SQL Server Profiler 추적에서 두 개의 다른 Showplan XML For Query Compile 이벤트가 생성되는 것을 볼 수 있습니다. 이러한 경우 계획 적용에는 입력 쿼리(OPEN) 계획만 중요합니다. USE PLAN 힌트에서는 입력 쿼리 계획을 사용해야 합니다. 간단히 생성된(FETCH) 계획도 만들어지지만 계획 적용에 필요하지도 않고 허용되지도 않습니다. 입력 쿼리(OPEN) 계획이 커서 쿼리와 일치하는 행 집합을 먼저 수집하는 계획이므로 이 계획을 인식할 수 있습니다.

중요
커서 계획에 대해 비커서 계획을 적용하지 마십시오. 마찬가지로 비커서 계획에 대해 커서 계획을 적용하지 마십시오. 이렇게 하면 커서 쿼리와 비커서 쿼리가 동일해도 계획 적용이 실패할 수 있습니다.

커서 계획을 설명하는 XML 쿼리 계획 출력 중 다음 형식은 특정 커서 유형에 대해 USE PLAN을 사용하여 계획을 적용하는 데 사용될 수 있습니다.

커서에 대한 두 부분으로 이뤄진 계획
커서에 대한 한 부분 입력 쿼리 계획

XML 쿼리 계획을 얻기 위한 다음과 같은 메커니즘 중 하나를 통해 얻을 수 있는 커서 계획을 적용할 수 있습니다.

XML 기반의 SQL Server Profiler 추적 이벤트. 이러한 이벤트로는 Showplan XML, Showplan XML For Query Compile 및 Showplan XML Statistics Profile이 있습니다.
SET SHOWPLAN_XML ON
SET STATISTICS XML ON

다음 쿼리와 같은 동적 관리 뷰 및 함수

SELECT *
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

응용 프로그램에 의한 API 서버 커서 사용 관찰

DB Library, ODBC, ADO 및 OLEDB 응용 프로그램은 종종 API 서버 커서를 사용하여 SQL Server와 상호 작용합니다. 이러한 인터페이스 중 하나를 사용하여 작성된 응용 프로그램이 실행 중인 경우에 SQL Server ProfilerRPC:Starting 이벤트를 검사하면 API 서버 커서 저장 프로시저로 전송되는 호출을 확인할 수 있습니다.

예: 커서 쿼리에 계획 강제 적용

이 예에서는 ODBC 커서를 사용하여 AdventureWorks 데이터베이스와 상호 작용하는 응용 프로그램을 사용하고 있고 API 서버 커서 루틴을 사용하여 SQL Server로 전송되는 쿼리에 대한 계획을 적용한다고 가정합니다. 계획을 적용하려면 커서 API 루틴을 통해 전송되는 쿼리에 대한 계획을 수집한 다음 해당 쿼리에 대한 계획을 적용할 계획 지침을 만듭니다. 응용 프로그램에서 쿼리를 다시 실행하고 계획을 검사하여 적용했는지 확인합니다.

1단계: 계획 수집

SQL Server Profiler 추적을 시작하고 Showplan XML 및 RPC:Starting 이벤트를 선택합니다. 응용 프로그램에서 계획을 적용할 쿼리를 실행하도록 합니다. 생성되는 RPC:Starting 이벤트를 클릭합니다. RPC:Starting 이벤트에 다음과 같은 텍스트 데이터가 있다고 가정합니다.

DECLARE @p1 int
SET @p1=-1
DECLARE @p2 int
SET @p2=0
DECLARE @p5 int
SET @p5=8
DECLARE @p6 int
SET @p6=8193
DECLARE @p7 int
SET @p7=0
EXEC sp_cursorprepexec @p1 OUTPUT,@p2 OUTPUT,NULL,N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT
SELECT @p1, @p2, @p5, @p6, @p7

위의 sp_cursorprepexec 문에 인수로 나타나는 쿼리에 대한 입력 쿼리 계획을 포함하고 있는 Showplan XML 추적 이벤트를 마우스 오른쪽 단추로 클릭한 다음 이벤트 데이터 추출을 선택하여 쿼리에 대한 계획을 수집합니다. 이벤트 데이터(XML 실행 계획)를 바탕 화면의 CursorPlan.SQLPlan 파일에 저장합니다. CursorPlan.SQLPlan 파일을 CursorPlan.txt로 복사합니다. SQL Server Management Studio에서는 편집기 창에서 CursorPlan.txt를 엽니다. 나중에 시간을 절약하려면 찾기 및 바꾸기를 사용하여 계획에 있는 각 작은따옴표(')를 4개의 작은 따옴표('''')로 바꿉니다. CursorPlan.txt를 저장합니다.

2단계: 계획을 강제 적용할 계획 지침 만들기

다음 sp_create_plan_guide 문을 작성하고 실행하여 계획 지침을 만들어서 계획을 적용합니다. 이 계획 지침 정의에는 이전 단계에서 계획 지침의 USE PLAN 쿼리 힌트에 캡처한 XML 계획이 있습니다.

이 계획 지침 정의를 작성할 때 CursorPlan.txt의 내용을 @hints 인수에서 OPTION(USE PLAN N'') 바로 뒤의 적절한 위치에 붙여 넣습니다.

exec sp_create_plan_guide 
@name = N'CursorGuide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderID AND h.OrderDate BETWEEN ''20030101'' AND ''20040101''',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan'''' Version=''''0.5'''' Build=''''9.00.1116''''><BatchSequence><Batch><Statements><StmtSimple>
   …
</StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>'')'

3단계: 쿼리를 실행하고 계획 지침이 적용되었는지 확인

응용 프로그램에서 다시 쿼리를 실행하고 SQL Server Profiler에서 XML Showplan 이벤트를 사용하여 해당 XML 실행 계획을 수집합니다.

해당 계획에 대한 XML Showplan 이벤트를 클릭합니다. 이 계획이 계획 지침에서 적용했던 계획인지 확인해야 합니다.

매개 변수가 있는 커서 쿼리

계획 지침을 만들려는 API 서버 커서 쿼리에 매개 변수가 있으면 SQL Server ProfilerRPC:Starting 이벤트에 있는 문 문자열과 매개 변수 정의 문자열을 계획 지침 정의에 모두 포함시켜야 합니다. 또한 sp_executesql을 사용하여 전송된 매개 변수가 있는 쿼리에서처럼 성공적으로 계획 지침을 일치시키려면 매개 변수 정의 문자열도 필요합니다.

다음을 통해 공유