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/

 


강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice [IT재직자] 개강임박!! 올해 마지막 무료 및 국비지원 교육 (JAVA, 리눅스 기초/고급 , CCNA, CCIE 등) 코난(김대우) 2022.12.02 132
Notice 2022년 11월 SQLER의 강좌 업데이트 리스트 코난(김대우) 2022.12.01 25
Notice 2022 공개SW 페스티벌 - Open Up 코난(김대우) 2022.12.01 15
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 4238
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1274
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1083
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1211
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1091
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1163
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1326
» Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1705
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1259
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1466
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1090
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 977
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3173
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1512
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1296
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1104
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 962
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 867
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 925
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 997





XE Login

테스트 팝업
Close