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

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

• 매개 변수 및 실행 계획 재사용

 

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

 

ADO, OLE DB, ODBC 응용 프로그램의 매개변수 표식을 포함하여 매개 변수 매개 변수를 사용하면 실행 계획을 좀더 많이 재사용 할 수 있다.

또한 보안 측면에서는 최종 사용자가 입력한 값을 갖는 매개 변수 표식을 사용하는 것이 데이터 액세스 API에서드, EXECUTE 문 또는 sp_executesql 저장 프로시저 중 하나를 사용하여 실행하는 문자열에 값을 연결하는 것보다 안전하다.

 

아래 스크립트에서 동일한 구문에 Where 절의 조건만 다른 스크립트 이다. 동일한 구문임에도 불구하고 두 쿼리는 다르게 인식 된다.

• 플랜 캐시 삭제

DBCC FREEPROCCACHE GO

 

• 각 쿼리 실행

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

 

• 플랜 캐시 확인

SELECT qt.text ,cp.size_in_bytes ,cp.cacheobjtype ,cp.objtype from sys.dm_exec_cached_plans cp join sys.dm_os_memory_cache_entries ce on cp.memory_object_address=ce.memory_object_address cross apply sys.dm_exec_sql_text(cp.plan_handle) as qt

 

 

 

SQL Server에서 항상 해당 문이 기본적으로 동일한 계획을 생성하고 그 계획을 재사용 한다는 것을 인식하게 하려는 것이 목적이지만 때떄로 SQL Server는 복잡한 SQL문에서 이러한 사실을 감지 하지 못한다.

매개 변수를 사용하여 SQL 문에서 상수를 분리하면 관계형 엔진이 중복된 계획을 인식하는데 도움이 된다.

다음 실습을 통하여 매개 변수를 사용하여 보자.

 

[T-SQL 에서 sp_executesql 사용]

SQL문을 동적으로 생성하는 T-SQL, 저장 프로시저 또는 트리거에 대해서는 sp_executesql을 사용하는 것이 좋다.

• 플랜 캐시 삭제

DBCC FREEPROCCACHE GO

 

• sp_executesql 실행

DECLARE @MyIntParm INT SET @MyIntParm = 1 EXEC sp_executesql N'SELECT * FROM AdventureWorks2008R2.Production.Product WHERE ProductSubcategoryID = @Parm', N'@Parm INT', @MyIntParm

 

이 때 @MyIntParm = 1 의 값에서 상수를 바꾸어서 여러 번 실행 하여 본다.

 

• 플랜 캐시 확인

SELECT qt.text ,cp.size_in_bytes ,cp.cacheobjtype ,cp.objtype from sys.dm_exec_cached_plans cp join sys.dm_os_memory_cache_entries ce on cp.memory_object_address=ce.memory_object_address cross apply sys.dm_exec_sql_text(cp.plan_handle) as qt

 

 

매개변수화를 이용한 사용에는 다른 상수의 값이 입력 되었는데에도 플랜을 새로 생성하지 않고 재사용하는 것을 확인 할 수 있다.

 

 

[ADO. OLE DB, ODBC 매개변수 표식 사용]

매개 변수 표식은 SQL문의 상수를 대신하는 물음표(?)로 프로그램 변수에 바인딩 된다.

다음과 같은 매개변수 표식을 사용하는 예를 들어보자.

SQLExecDirect(hstmt, "SELECT * FROM AdventureWorks2008R2.Production.Product WHERE ProductSubcategoryID = ?", SQL_NTS);

1. SQLBintParameter를 사용하여 SQL문에서 정수 변수를 첫째 매개 변수 표식에 바인딩 한다.
2. 변수에 정수 값을 배치 한다
3. 매개 변수 표식(?)을 지정하여 문을 실행 한다.

 

응용프로그램에서 매개 변수 표식이 사용된 경우 SQL Server에 포함된 SQL Server Native Client OLE DB 공급자와 SQL Server Native Client ODBC 드라이버는 sp_executesql을 사용하여 해당 쿼리문을 SQL Server로 보낸다.

 

 

응용 프로그램 디자인 내에 매개 변수를 명시적으로 구축하지 않은 경우에는 단순 매개 변수화의 기본 동작을 사용하여 SQL Server 쿼리 최적화 프로그램에서 특정 쿼리를 자동으로 매개 변수화하도록 할 수 있다.

ALTER DATABASE 문의 PARAMETERIZATION 옵션을 FORCED로 설정하여 데이터베이스의 모든 쿼리를 강제 매개 변수화하도록 쿼리 최적화 프로그램을 설정 할 수도 있다.

--강제 매개변수화 적용 ALTER DATABASE SW_TEST SET PARAMETERIZATION FORCED GO   --강제 매개변수화 원상 복구 ALTER DATABASE SW_TEST SET PARAMETERIZATION SIMPLE GO

 

 

강제 매개 변수화를 설정한 경우에도 단순 매개 변수화가 계속해서 수행 될 수 있다. 강제 매개 변수화를 시도한 후 실패 하면 그 다음으로는 단순 매개 변수화를 시도 한다.

 

[강제 매개 변수화 예외]

다음의 경우에는 강제 매개 변수화 할 수 없다.

• INSERT … EXECUTE 문
• 저장 프로시저, 트리거 또는 사용자 정의 함수의 본문 안에 있는 문
• 클라이언트측 응용 프로그램에서 이미 매개변수화된 준비된 문
• XQuery 메소스 호출이 포함 된 문. 이러한 문에서는 WHERE 절과 같이 해당 인수가 일반적으로 매개 변수화되는 컨텍스트에서 메서드가 나타난다. 해당 인수가 매개 변수화 되지 않은 컨텍스트에서 메서드가 나타날 경우에는 문의 나머지 부분이 매개 변수화 된다.
• T-SQL 커서 내의 문. API커서 내의 SELECT문을 매개 변수화 된다.
• 사용되지 않는 쿼리 구문
• ANSI_PADDING OFF 또는 ANSI_NULLS OFF로 설정된 컨텍스트에서 실행되는 문
• 매개 변수화하기에 적합한 리터럴이 2097개 이상 포함된 문
• WHERE T.Col1 >= @a 와 같은 변수를 참조 하는 문
• RECOMPILE 또는 OPTIMIZE FOR 쿼리 힌트가 포함된 문
• COMPUTE 절을 포함한 문
• WHERE CURRENT OF 절을 포함하는 문

 

강제 매개 변수화 예외상황이다. 하지만 단순 매개 변수화 규칙에 따르면 매개 변수화 할 수 있다.

SELECT * FROM Person.Address WHERE AddressID = 1 + 2;

 

 

일괄 처리 내에서 전송된 쿼리의 경우 SQL Server는 먼저 SQL 기반 계획 지침에 대해 쿼리를 대응한 다음 쿼리를 매개 변수화 하고 다시 시도하여 계획 치침을 쿼리에 대응시킨다. 아래 그림은 이러한 프로세스의 개요를 나타낸다.

 

 

 

[참고 자료]

매개 변수 및 실행 계획 재사용

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

 

SQL Server에서 계획 지침을 쿼리에 대응시키는 방법:

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

 

강제 매개 변수화

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

 

단순 매개 변수화

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

 

 

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