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

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

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

 

 


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

No. Subject Author Date Views
1793 메모리 관리 아키텍처 – NUMA(Non-Uniform Memory Access)이해 jevida(강성욱) 2016.09.29 1424
1792 메모리 관리 아키텍처 – Hot Add 메모리 jevida(강성욱) 2016.09.28 930
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1056
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1254
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1825
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2568
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1366
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1365
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1878
1784 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1373
1783 sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기 jevida(강성욱) 2016.09.28 2932
1782 SQL Server 쿼리 처리 아키텍처_분산 쿼리 아키텍처 jevida(강성욱) 2016.09.28 1148
1781 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 jevida(강성욱) 2016.09.28 1348
1780 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준 jevida(강성욱) 2016.09.28 1871
1779 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 jevida(강성욱) 2016.09.28 1797
1778 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - Preparing SQL Statements jevida(강성욱) 2016.09.28 1014
1777 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 강제 매개 변수화 jevida(강성욱) 2016.09.28 1001
» SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화 jevida(강성욱) 2016.09.28 880
1775 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 매개 변수 및 실행 계획 재사용 jevida(강성욱) 2016.09.28 1108
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1446





XE Login