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

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 28138
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 15437
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1489
1773 DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 1047
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 980
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1371
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 1003
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1490
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 967
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 2205
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1256
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1133
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1426
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1134
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1202
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1576
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1086
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1137
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1289
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 837
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1177
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 1026





XE Login