파라메터 스니핑과 데이터 스큐
- Version : SQL Server 2005, 2008, 2008R2, 2012
SQL Server에서 Procedure를 호출 할 때 대부분 잘 실행 되지만 간혹 매우 느리게 실행 될 때가 있다. 이런 현상을 다음 사례를 통해서 알아 보자.
이번 포스트는 SQL Server Premier Field Engineer Blog에 게시된 내용으로 필자가 읽고 이해한 내용을 정리 하였으며 번역의 오류나 기술적 오류가 있음을 미리 알려 둔다. 자세한 내용은 원문을 참고하길 바란다.
대부분 이런 현상이 나타날 때 일반적인 상황은 파라메터 스니핑과 관계가 있다. 이 문제는 간혹 고르지 않은 데이터 분포 때문이다. 스큐된 데이터를 표시하고 있지만 파라메터 스니핑은 컴파일 타임에 알 수 없는 변수 값, 코드 분기 등과 같은 많은 요인이 있다. 여기서 중요한 점은 항상 특정 프로시저의 계획이 빗나갈때를 정확하게 캐치하기가 어렵다는 것이다. 하지만 파라메터 스니핑문제는 실제 실행 계획의 런타임 대 컴파일 값으로 확인 할 수 있다.
XML에서는 ParameterList에서 확인 할 수 있다.
실제 실행계획 표시를 선택한 다음 다음 스크립트를 실행 하면 각 ProductID에 대하여 가장 효율적인 실행 계획을 확인 할 수 있다.
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 897
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 870 |
첫 번째의 경우 인덱스를 이용하여 실행 하지만 두 번째의 경우는 인덱스를 스캔한다. 이 것은 데이터 스큐 때문이다.
다음 통계를 이용하여 데이터 분포를 살펴보자. 870에 해당 행 수는 4688이며 895-897범위 내에서는 227개의 레코드가 있다.
dbcc show_statistics ('Sales.SalesOrderDetail', 'IX_SalesOrderDetail_ProductID') |
실행계획은 먼저 실행된 두 가지 중 하나의 값을 캐시하여 사용한다. 따라서 생성된 저장 프로시저를 실행 할 때 어떤 것을 먼저 실행하느냐에 따른 실제 실행 계획과 IO 통계를 확인해 보자.
다음 프로시저를 생성 한다.
CREATE PROCEDURE Sales.usp_GetSalesOrders
@ProductID INT
AS
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID |
다음 스크립트를 실행 한다. 컴파일시 파라메터로 897의 값을 캐시하고 프로시저는 모든 요청에대해 897에 대한 계획을 사용한다.
SET STATISTICS IO ON GO
DBCC FREEPROCCACHE GO
EXECUTE Sales.usp_GetSalesOrders @ProductID = 897 GO
EXECUTE Sales.usp_GetSalesOrders @ProductID = 870 GO |
(2개행이영향을받음) 테이블'SalesOrderDetail'. 검색수1, 논리적읽기수10, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.
(1개행이영향을받음)
(4688개행이영향을받음) 테이블'SalesOrderDetail'. 검색수1, 논리적읽기수14378, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. |
이번에는 실행 순서를 바꾸보자. 컴파일시 파라메터는 870을 사용하여 캐시 한다.
DBCC FREEPROCCACHE GO
EXECUTE Sales.usp_GetSalesOrders @ProductID = 870 GO
EXECUTE Sales.usp_GetSalesOrders @ProductID = 897 GO |
(4688개행이영향을받음) 테이블'SalesOrderDetail'. 검색수1, 논리적읽기수1240, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.
(1개행이영향을받음)
(2개행이영향을받음) 테이블'SalesOrderDetail'. 검색수1, 논리적읽기수1240, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. |
위 두 사례에서 볼 수 있듯이 제품 ID 897의 경우 논리적 읽기가 1240으로 897을 컴파일하여 사용하는 것보다 덜 효율적이지만 전체적으로 870에 대한 논리적 읽기가 많은 혜택을 받았다.
인덱스 및 쿼리에 따라 모든 파라메터 값이 시나리오에 가장 적합한 계획을 생성하기에는 불가능하므로 다음 옵션의 사용을 고려할 수도 있다.
- OPTION (OPTIMIZER FOR <X>)
- OPTION (RECOMPILE)
- Create the procedure WITH RECOMPILE
- Plan Guide
- Create separate stored procedures (more on this option below)
최적의 시나리오를 찾기 위해 여러 솔루션을 테스트하는 것이 중요하며 파라메터 값이 시나리오에 가장 적합한 계획을 생성 할 수 있도록 하면 많은 효율을 얻을 수 있다.
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
