매개 변수가 있는 쿼리를 위한 계획 지침 디자인
매개 변수가 있는 쿼리에 대한 계획 지침을 만들 수 있습니다. 쿼리는 다음과 같은 이유 중 하나로 매개 변수화될 수 있습니다.
- 쿼리가 sp_executesql을 사용하여 제출됩니다.
- 강제 매개 변수화가 데이터베이스에 설정되어 있습니다. 이로 인해 모든 적합한 쿼리가 매개 변수화됩니다.
- 개별적인 계획 지침은 이 쿼리가 속하는 쿼리 클래스에서 생성되어 매개 변수화됨을 지정합니다.
매개 변수가 있는 쿼리에서 계획 지침을 만드는 것은 본질적으로 같은 형식으로 매개 변수화하지만 해당 상수 리터럴 값만 다른 모든 쿼리에 대한 계획 지침을 만드는 것입니다. 예를 들어 강제 매개 변수화가 설정된 데이터베이스에서 다음 두 쿼리는 동일한 형식으로 매개 변수화합니다.
SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 50
SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 100
매개 변수가 있는 쿼리에서 계획 지침을 만들려면 SQL 유형의 계획 지침을 만들고 sp_create_plan_guide 저장 프로시저에서 쿼리의 매개 변수가 있는 형식을 지정합니다.
예를 들어 이전 예에서 쿼리 중 하나의 매개 변수가 있는 형식을 가져오고 계획 지침을 만들어서 최적화 프로그램이 해시 조인을 사용하도록 하려면 다음 단계를 따르십시오.
- sp_get_query_template을 실행하여 쿼리의 매개 변수가 있는 형식을 가져 옵니다.
- SQL Server 에서 sp_executesql 또는 PARAMETERIZATION FORCED 데이터베이스 SET 옵션을 사용하여 쿼리를 아직 매개 변수화하지 않은 경우 TEMPLATE 유형의 계획 지침을 만들어서 매개 변수화를 강제 적용합니다.
- 매개 변수가 있는 쿼리에서 SQL 유형의 계획 지침을 만듭니다.
다음 일괄 처리는 이러한 3가지 단계를 모두 실행합니다.
--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID WHERE pi.ProductID = 101 GROUP BY pi.ProductID, pi.Quantity HAVING sum(pi.Quantity) > 50',
@stmt OUTPUT,
@params OUTPUT
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1',
@stmt,
N'TEMPLATE',
NULL,
@params, N'OPTION(PARAMETERIZATION FORCED)'
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1',
@stmt,
N'SQL',
NULL,
@params,
N'OPTION(HASH JOIN)'
지정된 형식으로 매개 변수화되지만 다른 상수 리터럴 값이 포함된 모든 쿼리에 계획 지침이 적용됩니다.
참고 항목
개념
계획 지침을 사용하여 배포된 응용 프로그램의 쿼리 최적화
관련 자료
쿼리 성능
sp_create_plan_guide(Transact-SQL)