데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1056
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1250
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1818
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2557
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1365
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1363
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1873
1784 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1368
1783 sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기 jevida(강성욱) 2016.09.28 2887
1782 SQL Server 쿼리 처리 아키텍처_분산 쿼리 아키텍처 jevida(강성욱) 2016.09.28 1145
1781 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 jevida(강성욱) 2016.09.28 1341
1780 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준 jevida(강성욱) 2016.09.28 1853
1779 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 jevida(강성욱) 2016.09.28 1791
1778 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - Preparing SQL Statements jevida(강성욱) 2016.09.28 1009
1777 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 강제 매개 변수화 jevida(강성욱) 2016.09.28 999
1776 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화 jevida(강성욱) 2016.09.28 880
» SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 매개 변수 및 실행 계획 재사용 jevida(강성욱) 2016.09.28 1098
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1440
1773 DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 989
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 941





XE Login