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

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

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

  • Preparing SQL Statements

 

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

 

SQL Server 관계형 엔진에서는 SQL 문을 실행하기 전에 SQL 명령을 준비할 수 있는 기능을 제공한다. 응용 프로그램에서 SQL문을 여러 번 실행해야 하는 경우에는 데이터베이스 API를 사용하여 다음을 수행 할 수 있다.

  • 프리페어드 쿼리를 한 번 실행한다. 이렇게 하면 SQL문이 실행 계획으로 컴파일 된다.
  • 첫 번째 쿼리 실행 이후 프리페어드 쿼리문을 실행 할 때마다 미리 컴파일된 실행 계획을 사용한다. 이렇게 하면 쿼리를 실행 할 때마다 SQL 문을 다시 컴파일할 필요가 없다.

 

프리페어드 쿼리 실행은 API 함수 및 메서드에 의해 제어된다. 이는 SQL Server Native Client OLE DB 공급자 및 SQL Server Native Client ODBC 드라이버에 의해 지원된다. 프리페어드 방식으로 요청 시 해당 드라이버는 요청과 함께 SQL 문을 SQL Server로 호출 한다. SQL Server는 실행 계획을 컴파일 하고 해당 드라이버에 해당 계획에 대한 핸들을 반환 한다. 실행 요청 시 드라이버는 핸들과 관련 된 계획의 실행 요청을 서버에 보낸다.

 

프리페어드 쿼리는 임시 테이블과 같은 임시 개체를 만드는 시스템 저장 프로시저를 참조 할 수 없기 때문에 SQL Server에서 임시 개체를 만드는데 사용할 수 없다. 이러한 프로시저는 직접 실행 되어야 한다.

 

프리페어드 형식의 쿼리를 과도하게 사용하면 성능이 저하될 수 있다. 쿼리가 한 번만 실행 되는 경우 직접 호출 서버로의 네트워크 왕복은 1회만 필요하다. 하지만 프리페어드 방식은 SQL문을 준비하는데 한번, 실행 하는데 한번으로 합이 2번의 네트워크 왕복이 필요하다.

 

매개 변수 표식이 사용되는 경우에는 프리페어드 방식이 좀더 효과적이다. 다음 스크립트를 예를 들어보자.

 

AdventureWorks2008R2 예제 데이터베이스에서 제품 정보를 검색하는 쿼리가 있다. 응용프로그램에서 다음과 같이 쿼리를 호출 하는 경우 제품 ID에 따라 별도의 쿼리를 실행 한다.

SELECT * FROM AdventureWorks2008R2.Production.Product

WHERE ProductID = 63;

 

 

응용 프로그램에서 다음과 같은 쿼리문을 수행 할때에는

  1. 매개 변수 표식(?)을 포함하는 쿼리문 준비
  2. 프로그램 변수를 매개 변수 표식에 바인딩
  3. 제품 정보가 필요할 때 마다 키 값으로 바인딩된 변수를 채우고 쿼리문을 실행

으로 진행 할 수 있다.

SELECT * FROM AdventureWorks2008R2.Production.Product

WHERE ProductID = ?;

 

 

프리페어드방식의 경우 쿼리가 4번이상 실행 될 때 효율적이다. 위에서 설명 할 때는 직접 실행 하는것과 비교해서 성능상 이점이 없다고 하였는데 응용 프로그램 매개 변수 표식을 사용하여 프리페어드 쿼리를 반복적으로 실행 할 때 SQL Server에서는 해당 계획이 프로시저 캐시에 에이징 되지 않는 한 반복 되는 쿼리(두 번째 쿼리) 부터는 첫 번째 실행의 실행 계획을 재사용 한다.

다음과 같은 이점이 있다.

  • 프리페어드 된 쿼리의 식별 핸들로 실행 계획을 찾는 것이 기존의 실행 계획 비교 알고리즘보다 효율적이다.
  • 응용 프로그램의 실행 계획이 만들어지고 재사용되는 시기를 제어 할 수 있다.
  • 다른 SQL Server 및 데이터베이스로 이식이 가능하다.

[프리페어드 사용 이슈]

자료를 정리하다 보니 MSDN 블로그에서 다음과 같은 이슈를 찾을 수 있었다.

원문 링크 : http://blogs.msdn.com/b/sqlcat/archive/2010/12/21/watch-out-those-prepared-sql-statements.aspx

 

간단히 요약하면 시간이 지남에 따라 초다 배치수는 저하되고 SQL Server 연결 메모리가 계속 증가하였다. 실제로 ODBC 드라이버에서 sp_prepexec 에서 쿼리를 래핑하지만 sp_unprepare를 실행하는데 프리페이드 핸들 누수가 발생 하였다. 이 문제를 해결 하기 위해 ODBC 드라이버 핫픽스 될 때까지 응용 프로그램에서 쿼리를 직접 실행 방식으로 바꾸어 해결 하였다.

 

 

 

[참고자료]

Preparing SQL Statements : http://msdn.microsoft.com/ko-kr/library/ms175528(v=sql.105).aspx

Watch out those prepared SQL statements :

http://blogs.msdn.com/b/sqlcat/archive/2010/12/21/watch-out-those-prepared-sql-statements.aspx

 

 


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

No. Subject Author Date Views
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1250
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1788
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2528
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1361
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1360
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1858
1784 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1357
1783 sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기 jevida(강성욱) 2016.09.28 2831
1782 SQL Server 쿼리 처리 아키텍처_분산 쿼리 아키텍처 jevida(강성욱) 2016.09.28 1142
1781 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 jevida(강성욱) 2016.09.28 1335
1780 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준 jevida(강성욱) 2016.09.28 1836
1779 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 jevida(강성욱) 2016.09.28 1746
» SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - Preparing SQL Statements jevida(강성욱) 2016.09.28 1007
1777 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 강제 매개 변수화 jevida(강성욱) 2016.09.28 994
1776 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화 jevida(강성욱) 2016.09.28 875
1775 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 매개 변수 및 실행 계획 재사용 jevida(강성욱) 2016.09.28 1095
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1423
1773 DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 978
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 939
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1335





XE Login