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

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

 

 

 

2013-05-15 / 강성욱 / http://sqlmvp.kr / http://datawaffle.com