• 아티클

강제 매개 변수화

데이터베이스의 모든 SELECT, INSERT, UPDATE 및 DELETE 문이 특정 제한에 따라 매개 변수화되도록 지정하여 SQL Server의 기본 단순 매개 변수화 동작을 무시할 수 있습니다. ALTER DATABASE 문에서 PARAMETERIZATION 옵션을 FORCED로 설정하여 강제 매개 변수화를 설정합니다. 강제 매개 변수화를 사용하여 쿼리 컴파일 및 재컴파일 빈도를 줄여 특정 데이터베이스의 성능을 향상시킬 수 있습니다. 일반적으로 POS(Point of Sale) 응용 프로그램과 같은 원본으로부터 대량의 동시 쿼리를 처리하는 데이터베이스에서 강제 매개 변수화를 사용하면 도움이 될 수 있습니다.

PARAMETERIZATION 옵션을 FORCED로 설정하면 임의의 형식으로 전송된 SELECT, INSERT, UPDATE 또는 DELETE 문에 표시되는 리터럴 값이 쿼리 컴파일 중에 매개 변수로 변환됩니다. 그러나 다음 쿼리 구문에 나타나는 리터럴은 예외입니다.

  • INSERT...EXECUTE 문

  • 저장 프로시저, 트리거 또는 사용자 정의 함수의 본문 안에 있는 문. SQL Server에서는 이미 이러한 루틴에 대해 쿼리 계획을 다시 사용하고 있습니다.

  • 클라이언트측 응용 프로그램에서 이미 매개 변수화된 준비된 문

  • XQuery 메서드 호출이 포함된 문. 이러한 문에서는 WHERE 절과 같이 해당 인수가 일반적으로 매개 변수화되는 컨텍스트에서 메서드가 나타납니다. 해당 인수가 매개 변수화되지 않는 컨텍스트에서 메서드가 나타날 경우에는 문의 나머지 부분이 매개 변수화됩니다.

  • Transact-SQL 커서 내의 문. API 커서 내의 SELECT 문은 매개 변수화됩니다.

  • 사용되지 않는 쿼리 구문

  • ANSI_PADDING 또는 ANSI_NULLS가 OFF로 설정된 컨텍스트에서 실행되는 문

  • 매개 변수화하기에 적합한 리터럴이 2,097개 이상 포함된 문

  • WHERE T.col2 >= @bb와 같은 변수를 참조하는 문

  • RECOMPILE 쿼리 힌트가 포함된 문

  • COMPUTE 절을 포함하는 문

  • WHERE CURRENT OF 절을 포함하는 문

또한 다음 쿼리 절은 매개 변수화되지 않습니다. 이러한 경우 해당 절만 매개 변수화되지 않습니다. 동일한 쿼리 내의 다른 절에는 강제 매개 변수화가 적용될 수 있습니다.

  • SELECT 문의 <select_list>. 하위 쿼리의 SELECT 목록 및 INSERT 문 내의 SELECT 목록이 포함됩니다.

  • IF 문 내에 나타나는 하위 쿼리 SELECT 문

  • 쿼리의 TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO 또는 FOR XML 절

  • OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML 또는 모든 FULLTEXT 연산자에 대한 직접 인수 또는 하위 식으로서의 인수

  • LIKE 절의 pattern 및 escape_character 인수

  • CONVERT 절의 style 인수

  • IDENTITY 절 내의 정수 상수

  • ODBC 확장 구문을 사용하여 지정한 상수

  • +, -, *, / 및 % 연산자의 인수인 상수 폴딩 가능 식. SQL Server에서는 식이 강제 매개 변수화에 적합한지 결정할 때 다음 조건 중 하나가 True이면 상수 폴딩 가능 식으로 간주합니다.

    • 식에 열, 변수 또는 하위 쿼리가 나타나지 않습니다.

    • 식에 CASE 절이 포함됩니다.

    상수 폴딩 가능 식에 대한 자세한 내용은 쿼리 성능 저하 문제 해결: 카디널리티 예측 중 상수 폴딩 및 식 평가를 참조하십시오.

  • 쿼리 힌트 절에 대한 인수. FAST 쿼리 힌트의 number_of_rows 인수, MAXDOP 쿼리 힌트의 number_of_processors 인수 및 MAXRECURSION 쿼리 힌트의 number 인수가 포함됩니다.

매개 변수화는 개별 Transact-SQL 문 수준에서 수행됩니다. 다시 말해 일괄 처리 내의 개별 문이 매개 변수화됩니다. 컴파일 후 매개 변수가 있는 쿼리는 쿼리가 원래 전송되었던 일괄 처리의 컨텍스트에서 실행됩니다. 쿼리의 실행 계획이 캐시된 경우에는 sys.syscacheobjects 동적 관리 뷰의 sql 열을 참조하여 쿼리가 매개 변수화되었는지 여부를 확인할 수 있습니다. 쿼리가 매개 변수화된 경우 (@1 tinyint)와 같이 이 열에서 매개 변수의 이름 및 데이터 형식은 전송된 일괄 처리 텍스트 앞에 옵니다. 쿼리 계획을 캐시하는 방법은 실행 계획 캐싱 및 다시 사용을 참조하십시오.

[!참고]

매개 변수 이름은 임의로 지정하므로 사용자나 응용 프로그램에서는 특정 명명 순서를 따를 필요가 없습니다. 또한 다음 요소는 SQL Server 및 서비스 팩 업그레이드의 버전에 따라 달라질 수 있습니다. 매개 변수 이름, 매개 변수화되는 리터럴 선택 항목 및 매개 변수화된 텍스트의 공백이 여기에 포함됩니다.

매개 변수의 데이터 형식

SQL Server에서 리터럴을 매개 변수화하면 매개 변수가 다음 데이터 형식으로 변환됩니다.

  • 정수 리터럴은 그 크기가 int 데이터 형식에 적합하면 int로 매개 변수화됩니다. <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN 및 IN과 같은 비교 연산자와 관련된 조건자의 일부인 큰 정수 리터럴은 numeric(38,0)으로 매개 변수화됩니다. 비교 연산자와 관련된 조건자의 일부가 아닌 큰 리터럴은 전체 자릿수가 리터럴의 크기를 지원할 만큼 크고 소수 자릿수가 0인 numeric으로 매개 변수화됩니다.

  • 비교 연산자와 관련된 조건자의 일부인 고정 소수점 숫자 리터럴은 전체 자릿수가 38이고 소수 자릿수가 리터럴의 크기를 지원할 만큼 큰 numeric으로 매개 변수화됩니다. 비교 연산자와 관련된 조건자의 일부가 아닌 고정 소수점 숫자 리터럴은 전체 자릿수 및 소수 자릿수가 리터럴의 크기를 지원할 만큼 큰 numeric으로 매개 변수화됩니다.

  • 부동 소수점 숫자 리터럴은 float(53)으로 매개 변수화됩니다.

  • 비유니코드 문자열 리터럴은 리터럴의 크기가 8,000자 내일 때는 varchar(8000)로 매개 변수화되고 8,000자보다 클 때는 varchar(max)로 매개 변수화됩니다.

  • 유니코드 문자열 리터럴은 리터럴의 크기가 유니코드 문자로 4,000자 내일 때는 nvarchar(4000)로 매개 변수화되고 4,000자보다 클 때는 nvarchar(max)로 매개 변수화됩니다.

  • 이진 리터럴은 리터럴 크기가 8,000바이트 내일 때는 varbinary(8000)로 매개 변수화되고 8,000바이트보다 클 때는 varbinary(max)로 변환됩니다.

  • 통화 유형 리터럴은 money로 매개 변수화됩니다.

강제 매개 변수화 사용 지침

PARAMETERIZATION 옵션을 FORCED로 설정할 때는 다음 사항을 고려해야 합니다.

  • 강제 매개 변수화를 적용하면 쿼리 컴파일 시 쿼리의 리터럴 상수가 매개 변수로 변경됩니다. 따라서 쿼리 최적화 프로그램에서는 만족스럽지 못한 쿼리 계획을 선택할 수 있습니다. 특히 쿼리 최적화 프로그램에서는 인덱싱된 뷰 또는 계산 열의 인덱스에 쿼리를 대응시키지 못할 수 있습니다. 또한 분할된 테이블 및 분산형 분할 뷰에 대해 만족스럽지 못한 쿼리 계획을 선택할 수도 있습니다. 계산 열의 인덱스 및 인덱싱된 뷰를 많이 사용하는 환경에서는 강제 매개 변수화를 사용하면 안 됩니다. 일반적으로 PARAMETERIZATION FORCED 옵션은 숙련된 데이터베이스 관리자가 성능에 영향을 주지 않는다는 것을 확인한 후에만 사용해야 합니다.

  • 둘 이상의 데이터베이스를 참조하는 분산 쿼리에 강제 매개 변수화를 사용하면 좋습니다. 단, 쿼리가 실행되는 데이터베이스의 컨텍스트에서 PARAMETERIZATION 옵션이 FORCED로 설정되어 있어야 합니다.

  • PARAMETERIZATION 옵션을 FORCED로 설정하면 현재 컴파일되거나 다시 컴파일되거나 실행되고 있는 쿼리 계획을 제외한 모든 쿼리 계획이 데이터베이스의 계획 캐시에서 플러시됩니다. 설정을 변경할 때 컴파일 또는 실행 중이었던 쿼리 계획은 다음에 쿼리가 실행될 때 매개 변수화됩니다.

  • PARAMETERIZATION 옵션을 설정하는 작업은 온라인으로 수행되므로 데이터베이스 수준의 배타적 잠금이 필요하지 않습니다.

  • SQL Server 데이터베이스의 호환성 수준이 80으로 설정되었거나 이전 버전의 인스턴스에 있는 데이터베이스가 SQL Server 2005 이상 버전의 인스턴스에 연결된 경우에는 강제 매개 변수화가 해제됩니다(SIMPLE로 설정됨).

  • 현재 PARAMETERIZATION 옵션 설정은 데이터베이스를 다시 연결하거나 복원할 때도 그대로 유지됩니다.

단일 쿼리 또는 구문은 동일하고 매개 변수 값만 다른 기타 쿼리는 단순 매개 변수화되지 않도록 지정하여 강제 매개 변수화의 동작을 무시할 수 있습니다. 반대로 데이터베이스에서 강제 매개 변수화가 해제된 경우에도 구문이 동일한 쿼리에 한해 강제 매개 변수화가 수행되도록 지정할 수 있습니다. 이와 같은 작업을 수행할 때 계획 지침을 사용합니다. 자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하십시오.

[!참고]

PARAMETERIZATION 옵션이 FORCED로 설정되어 있으면 오류 메시지 보고가 단순 매개 변수화의 경우와 다를 수 있습니다. 단순 매개 변수화에서 보고되는 메시지보다 많은 오류 메시지가 보고될 수 있으며 오류가 발생한 줄 번호가 잘못 보고될 수 있습니다.