계획 지침을 사용하여 쿼리 매개 변수화 동작 지정

아티클
07/12/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

PARAMETERIZATION 데이터베이스 옵션을 SIMPLE로 설정하면 SQL Server 쿼리 최적화 프로그램이 쿼리를 매개 변수화하도록 선택할 수 있습니다. 즉, 쿼리에 포함된 모든 리터럴 값은 매개 변수로 대체됩니다. 이 프로세스를 단순 매개 변수화라고 합니다. SIMPLE 매개 변수화가 적용되는 경우 매개 변수가 있는 쿼리와 매개 변수가 없는 쿼리를 제어할 수 없습니다. 하지만 PARAMETERIZATION 데이터베이스 옵션을 FORCED로 설정하여 데이터베이스의 모든 쿼리를 매개 변수화하도록 지정할 수 있습니다. 이 프로세스를 강제 매개 변수화라고 합니다.

다음 방법으로 계획 지침을 사용하여 데이터베이스의 매개 변수화 동작을 무시할 수 있습니다.

PARAMETERIZATION 데이터베이스 옵션을 SIMPLE로 설정하면 특정 쿼리 클래스에 대해 강제 매개 변수화를 시도하도록 지정할 수 있습니다. 이렇게 하려면 매개 변수가 강제로 지정된 쿼리 형식에 대한 TEMPLATE 계획 지침을 만들고 sp_create_plan_guide 저장 프로시저에 PARAMETERIZATION FORCED 쿼리 힌트를 지정합니다. 이러한 종류의 계획 지침을 모든 쿼리가 아닌 특정 쿼리 클래스에만 강제 매개 변수화를 사용하도록 설정하는 방법으로 활용할 수 있습니다. 단순 매개 변수화에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.
PARAMETERIZATION 데이터베이스 옵션을 FORCED로 설정하면 특정 쿼리 클래스에 대해 강제 매개 변수화가 아닌 단순 매개 변수화만 시도되도록 지정할 수 있습니다. 이렇게 하려면 매개 변수가 강제로 지정된 쿼리 형식에 대한 TEMPLATE 계획 지침을 만들고 PARAMETERIZATION SIMPLE 쿼리 힌트를 sp_create_plan_guide에 지정합니다. 강제 매개 변수화에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.

AdventureWorks2022 데이터베이스에서 다음 쿼리를 고려합니다.

SELECT pi.ProductID, SUM(pi.Quantity) AS Total  
FROM Production.ProductModel AS pm   
    INNER JOIN Production.ProductInventory AS pi   
        ON pm.ProductModelID = pi.ProductID   
WHERE pi.ProductID = 101   
GROUP BY pi.ProductID, pi.Quantity HAVING SUM(pi.Quantity) > 50;

데이터베이스 관리자로서, 데이터베이스의 모든 쿼리에 강제 매개 변수화를 사용하도록 설정하지 않기로 결정했습니다. 하지만 이전 쿼리와 구문상 동일하지만 상수 리터럴 값만 다른 모든 쿼리에서 컴파일 비용을 방지하려고 합니다. 즉, 이러한 종류의 쿼리에 대한 쿼리 계획을 다시 사용할 수 있도록 쿼리를 매개 변수화하려고 합니다. 이 경우 다음 단계를 완료합니다.

매개 변수가 있는 형식의 쿼리를 검색합니다. sp_create_plan_guide에서 사용하기 위해 이 값을 안전하게 가져오는 유일한 방법은 sp_get_query_template 시스템 저장 프로시저를 사용하는 것입니다.
PARAMETERIZATION FORCED 쿼리 힌트를 지정하여 매개 변수화된 형식의 쿼리에 대한 계획 지침을 만듭니다.

Important

쿼리 매개 변수화의 일환으로 SQL Server는 리터럴의 값과 크기에 따라 리터럴 값을 대체하는 매개 변수에 데이터 형식을 할당합니다. sp_get_query_template의 @stmt 출력 매개 변수에 전달된 상수 리터럴 값과 동일한 프로세스가 발생합니다. sp_create_plan_guide의 @params 인수에 지정된 데이터 유형이 SQL Server에 의해 매개 변수화된 쿼리의 데이터 유형과 일치해야 하므로 쿼리에 사용할 수 있는 매개 변수 값의 전체 범위를 포함하는 계획 지침을 두 개 이상 만들어야 할 수 있습니다.

다음 스크립트를 사용하여 매개 변수가 있는 쿼리를 가져온 다음 계획 지침을 만들 수 있습니다.

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 AS pm   
      INNER JOIN Production.ProductInventory AS 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;  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';

마찬가지로, 이미 강제 매개 변수화를 사용하도록 설정된 데이터베이스에서 샘플 쿼리와 해당 상수 리터럴 값을 제외하고 구문적으로 동등한 다른 쿼리가 단순 매개 변수화 규칙에 따라 매개 변수화되도록 할 수 있습니다. 이렇게 하려면 OPTION 절에 PARAMETERIZATION FORCED 대신 PARAMETERIZATION SIMPLE을 지정합니다.

참고 항목

TEMPLATE 계획 지침은 단일 문으로만 구성된 배치로 제출된 쿼리와 문 매칭을 안내합니다. 다중 상태 일괄 처리 내의 문은 TEMPLATE 계획 지침에서 매칭할 수 없습니다.

다음을 통해 공유

계획 지침을 사용하여 쿼리 매개 변수화 동작 지정

피드백

추가 리소스