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

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

SQL Server Plan Guide 생성 및 사용

 

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

 

회사에서 사용하는 응용프로그램이 내가 운영하고 있는 데이터베이스에 쿼리를 호출 한다. 이 때일부 특정 쿼리가 매우 느리게 실행 되는 것을 확인 하였다. 하지만 나는 응용 프로그램의 코드를 수정 할 수 없다. 어떻게 쿼리 최적화를 할 수 있을까?

 

위의 상황은 우리가 일반적으로 말하는 웹 서버, 또는 기타 응용프로그램에서 Ad-hoc 쿼리를 호출하였을 때 발생하는 문제이다. 물론 해당 프로그램을 만든 담당자에게 이슈를 전달하여 쿼리를 수정 할 수도 있지만 외부 제품을 구입한 경우에는 이 또한 쉽지 않다.

 

어떻게 하면 느린 쿼리의 성능을 올릴 수 있을까? Plan Guide를 사용하여 ad-hoc 쿼리 실행 시 힌트를 적용 할 수 있는 방법을 알아 보자.

 

SQL Server 2005부터 Plan Guide 라는 기능이 추가 되었다. 이것은 프로시저에 속한 쿼리나 ad-hoc 쿼리에 대하여 해당 쿼리가 실행될 때 필요한 실행 계획을 가이드 할 수 있게 한다.

 

생성할 수 있는 Plan Guide는 3종류가 있다.

  • Object plan guide : 저장프로시저, 펑션, 트리거에 가이드 사용
  • SQL Plan Guide : CLR, 확정 저장프로시저, 동적 SQL 등에 가이드 사용
  • Template Plan Guide : 지정된 양식에 parameterization 옵션을 override 할 때 사용.

 

자세한 내용은 MSDN을 참고 한다.

관련 링크 : http://msdn.microsoft.com/en-us/library/ms189854(SQL.90).aspx

 

대부분의 경우에는 옵티마이저가 플랜을 작성하도록 하는 것이 좋다. 하지만 일부 경우에는 옵티마이저가 최적화된 플랜을 생성하지 못할 때 DBA가 최적화 플랜을 작성하여 플랜을 강제 유도 할 수 있다.

 

실습을 통해서 Plan Guide 생성 및 사용법을 알아 보자.

 

다음의 예제 스크립트를 실행 하여 보자.

EXEC sp_executesql

@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',

@params = N'@ProductID int', @ProductID = 870

GO

EXEC sp_executesql

@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',

@params = N'@ProductID int', @ProductID = 897

GO

 

 

첫 번째 쿼리문에서는 옵티마이저가 최적의 플랜을 통하여 전체 121317 행을 조회하여 4688행을 나타내었다.

두 번째 쿼리문에서는 쿼리문1과 동일한 플랜으로 2건을 조회 하였다. 쿼리2의 경우에는 조건 탐색에 의하여 탐색이 더 나은 대안이 될 수 있다.

 

DBA는 recompile 옵션을 사용하여 새로운 플랜을 생성하고 싶다. Plan Guide에 recompile 옵션을 추가해서 생성해 보자.

EXEC sp_create_plan_guide

@name = N'GETSALESPRODUCTS_RECOMPILE_Fix',

@stmt = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID',

@type = N'SQL',

@module_or_batch = NULL,

@params = N'@ProductID int',

@hints = N'OPTION (RECOMPILE)'

GO

 

 

 

플랜 생성이 완료 되었다. 처음과 동일한 쿼리문을 실행 하여 보자. 이 때 플랜 가이드가 잘 사용되고 있는지 확인하기 위하여 프로파일러를 이용하여 확인 하였다. 첫 번째 쿼리의 경우에는 최적화된 플랜으로 인덱스 스캔을 실행 하였고 두 번째 쿼리의 경우에는 인덱스 탐색을 진행한 것을 확인 할 수 있다.

 

 

프로파일러를 통해서 확인해 보면 SET STATISTICS XML ON 문이 호출 되었다. 플랜가이드를 활용한 것을 확인 할 수 있다.

 

 

생성된 플랜을 확인하는 방법에는 다음의 스크립트를 이용하여 확인 할 수 있다.

select * from sys.plan_guides

 

 

 

쿼리 플랜이 설정되어 있는 프로시저의 경우 삭제 및 수정이 불가능 하다. 쿼리 플랜의 삭제는 다음 스크립트를 실행 한다.

EXEC sp_control_plan_guide N'DROP', N'GETSALESPRODUCTS_RECOMPILE_Fix'

GO

 

 

Plan Guide는 옵티마이저의 선택에 영향을 미치므로 데이터베이서의 특성 및 비즈니스 환경을 잘 알며 매우 구체적인 상황일 때 신중하게 사용하여야 한다.

 

[참고 자료]

http://www.mssqltips.com/sqlservertip/1630/how-to-use-a-sql-server-plan-guide-to-tune-queries/

 



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5164
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3721
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1584
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1714
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1435
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1310
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1587
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1239
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3710
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1239
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1180
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1075
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1134
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1304
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1669
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1233
» SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1427
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1067
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 959





XE Login