Ad-hoc 쿼리를 매개변수화 하여 성능 높이기

Ad-hoc 쿼리를 매개변수화 하여 성능 높이기

 

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

 

많은 응용 프로그램에서 SQL Server에 연결하여 쿼리를 호출 한다. 보통 튜닝 가이드를 할 때 임의의 쿼리보다는 프로시저를 만들어서 호출 하기를 권장한다. 이러한 이유는 프로시저를 사용함으로써 여러 가지 이점이 있기 때문이다. 대표적인 예가 쿼리를 계속해서 컴파일 하지 않는다는 것이다. 컴파일에 따른 비용 절약과 플랜을 재사용 할 수 있기 때문에 SQL Server의 성능을 높일 수 있다.

 

일반적으로 날쿼리라 불리는 응용프로그램에서 매개변수화 하지 않는 쿼리를 요청 한다고 가정하자. 대부분의 쿼리는 같은 쿼리를 사용하면서 Where 절의 조건만 변형된 쿼리를 사용 할 것이다.

 

다음과 같이 동일한 쿼리에서 Where 절의 조건만 변형하여 호출 하여 보자.

(예제코드 : C#)

 

플랜캐쉬를 확인하여 플랜이 사용되고 있는지 확인 하자.

select qs.usecounts, cacheobjtype, objtype, qt.text from sys.dm_exec_cached_plans qs cross apply sys.dm_exec_sql_text(qs.plan_handle) as qt order by qt.text go

 

 

구문을 동일하나 Where 절의 조건에 따라 각 다르게 쿼리를 인식하여 플랜을 재사용하지 못하고 모든 쿼리를 컴파일 하여 사용 하였다.

 

 

쿼리를 매개변수화 시켜 호출 하여 보자.

 

 

Where 절이 다름에도 불구하고 처음 플랜을 생성한 뒤로 9번을 재사용한 것을 확인 할 수 있다.

프로파일러에서도 RPC 호출을 통하여 바인드된 것을 확인 할 수 있다.

 

 

DB튜닝을 하다 보면 데이터베이스뿐만 아니라 여러 응용 프로그램을 함께 수정해야 할 때가 많다. 처음 개발부터 이러한 환경의 차이를 알고 개발 한다면 성능에 여러 이점이 있으리라 생각한다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/1632/are-your-sql-server-application-queries-wasting-memory/

 

2013-03-06 / 강성욱 / http://sqlmvp.kr