SQL Server Plan Guide 생성 및 사용

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/

 

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