SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화

SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용

• 단순 매개 변수화

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서는 T-SQL문에 매개변수를 사용하여 새 SQL문을 이전에 컴파일된 기존의 실행 계획과 일치 시켜 관계형 엔진의 성능을 향상 시킨다. 매개 변수를 사용하지 않고 SQL문이 실행되면 SQL Server는 내부적으로 해당 문을 매개 변수화하여 기존 실행계획과 일치할 가능성을 높인다. 이 프로세스를 단순 매개변수화라 한다. (SQL Server 2000에서는 자동 매개변수화라 한다)

 

다음 스크립트를 보면 Where 절의 값이 1인 매개변수로 지정될 수 있다. 관계형 엔진은 값 1 대신 매개변수가 지정된 것처럼 일괄 처리에 대해 실행 계획을 작성한다.

SELECT * FROM AdventureWorks2008R2.Production.Product WHERE ProductSubcategoryID = 1;

 

아래 스크립트를 실행하여 실행 계획을 확인해 보면 두 문장이 기본적으로 동일한 실행 계획을 생성하는 것을 확인 할 수 있다. 이때 Where절의 값이 4인 두 번째 문이 Where 값이 1인 첫 번째 계획을 재사용 하는 것을 확인 할 수 있다.

SELECT * FROM AdventureWorks2008R2.Production.Product WHERE ProductSubcategoryID = 1;   SELECT * FROM AdventureWorks2008R2.Production.Product WHERE ProductSubcategoryID = 4;

 

 

 

복잡한 SQL 문을 처리할 때 관계형 엔진은 매개 변수화할 수 있는 식을 결정하기 어려울 수 있다. 복잡한 SQL 문을 사용되지 않은 기존 실행 계획과 일치시키는 관계형 엔진의 성능을 향상시키려면 sp_executesql 또는 매개변수 표식을 사용하여 매개 변수를 명시적으로 지정 한다.

 

+, - *, /, % 산술 연산자를 사용하여 int, smallint, tinyint, bigint의 상수 값을 float, real, decimal, numeric 데이터 형식으로 암시적 또는 명시적으로 변환할 때 SQL Server에서는 특정 규칙에 따라 식 결과의 형식과 전체 자릿수를 계산한다. 그러나 이러한 규칙은 매개 변수화 여부에 따라 달라진다. 따라서 쿼리에서 유사한 식을 사용하여도 다른 결과가 발생하는 경우가 있다.

 

SQL Server는 단순 매개 변수화의 기본 동작에 따라 비교적 작은 클래스의 쿼리를 매개 변수화 한다. 그러나 ALTER DATABASE 명령의 PARAMETRIZATION 옵션을 FORCED로 설정하여 데이터베이스의 모든 쿼리를 특정 제한 사항에 따라 매개 변수화하도록 지정할 수 있다. 이렇게 하면 쿼리의 컴파일 빈도를 낮추어 대량의 동시 쿼리가 발생하는 데이터베이스의 성능이 향상 될 수 있다.(강제 매개 변수화)

 

데이터베이스 옵션에서 PARAMETERIZTION 값을 SIMPLE로 설정하면 특정 쿼리에 대해 강제 매개 변수화가 아닌 단순 매개 변수화만 시도 되도록 지정 할 수 있다. 이렇게 하려면 매개 변수가 있는 쿼리 형식에 대한 TEMPLATE 계획 지침을 만들고 PARAMETERIZATION FORCED 쿼리 힌트를 sp_create_plan_guide 저장 프로시저에 지정 한다. 모든 쿼리 대신 특정 쿼리에만 강제 매개 변수화를 사용하여 이러한 종류의 계획 지침을 고려 할 수 있다.

 

 

[참고자료]

단순 매개 변수화 : http://msdn.microsoft.com/ko-kr/library/ms186219(v=sql.105).aspx

플랜가이드를 사용하여 쿼리 매개 변수화 동작 지정:

http://msdn.microsoft.com/ko-kr/library/ms191275(v=sql.105).aspx

 

 

2013-05-13 / 강성욱 / http://sqlmvp.kr