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

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

파라메터 스니핑과 데이터 스큐

 

  • 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)

 

최적의 시나리오를 찾기 위해 여러 솔루션을 테스트하는 것이 중요하며 파라메터 값이 시나리오에 가장 적합한 계획을 생성 할 수 있도록 하면 많은 효율을 얻을 수 있다.

[참고자료]

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/08/27/back-to-basics-sql-parameter-sniffing-due-to-data-skews.aspx

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1542
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1721
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4498
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1715
1846 강제 매개변수화로 인한 성능 저하 사례 jevida(강성욱) 2016.10.07 1537
» 파라메터 스니핑과 데이터 스큐 jevida(강성욱) 2016.10.07 1528
1844 DBCC CHECKDB 버그 및 해결 방법 jevida(강성욱) 2016.10.07 1536
1843 NOLOCK HINT 이해 jevida(강성욱) 2016.10.07 7700
1842 인증으로부터 분리된 사용자 방지 jevida(강성욱) 2016.10.07 2103
1841 비관리자 계정으로 쿼리 계획 보기 jevida(강성욱) 2016.10.07 1241
1840 SSMS 폴링 간격 구성 jevida(강성욱) 2016.10.07 1770
1839 Deadlock 감지하여 알림하기 jevida(강성욱) 2016.10.07 1766
1838 Suspect_pages 테이블 이해 및 관리 jevida(강성욱) 2016.10.07 1370
1837 SSRS SocketException jevida(강성욱) 2016.10.07 1469
1836 파티션 분할 시 I/O 최소화 하기 jevida(강성욱) 2016.10.07 1854
1835 대량 BCP 작업 시 발생하는 오류 (665, 1450, 33) jevida(강성욱) 2016.10.07 1643
1834 기본 추적(default tace) 활성화 및 로그 확인 jevida(강성욱) 2016.10.07 1388
1833 SQL Server ALTER TABLE syntax diagrams jevida(강성욱) 2016.10.07 1157
1832 SQL Server Performance Counter Guidance jevida(강성욱) 2016.09.30 2471
1831 SQL Server CREATE TABLE syntax diagrams jevida(강성욱) 2016.09.30 1421





XE Login