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 SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38458
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20748
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1644
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 4038
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2332
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5473
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 4279
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1649
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 2038
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1544
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1373
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1725
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1304
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 4935
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1304
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1125
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1258
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1138
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1218
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1370
» Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1761
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1310





XE Login