SQL Server 파라메터 스니핑의 다양한 접근

SQL Server 파라메터 스니핑의 다양한 접근

 

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

 

SQL Server에서 저장프로시저가 잘 수행되다가 갑자기 실행 시간이 급등한 경우가 있다. 다양한 이유가 있지만 대부분 이런 현상이 나타날 때 일반 적인 상황은 파라메터 스니핑과 관련이 있다.

 

다음 포스트는 파라메터 스니핑과 관련된 내용이다,

• 파라메터 스니핑과 데이터 스큐 : http://sqlmvp.kr/140196990001
• 매개변수 값의 변경과 SQL 서버 성능 저하 : http://sqlmvp.kr/140193296636

 

파라메터 스니핑(Parameter Sniffing)은 SQL Server에서 프로시저를 처음 호출 할 때(또는 SQL Server 재실행) 생성된 실행 계획을 플랜 캐시에 저장한 뒤 이후 프로시저가 호출되면 실행계획을 세우지 않고 재사용한다. 그래서 저장프로시저를 사용하면 성능상 이점이 있다. 하지만 어느 검색 조건을 먼저 실행하는지에 따라 성능에 좋지 않은 영향을 미치는 경우가 있다.

 

SQL Server가 최고의 계획을 선택 하는 방법은 비용을 추정하는 것이다. 그래서 가장 좋은 쿼리 계획은 입력 매개 변수 및 통계를 기반으로 카디널리티를 추정하는 것이다. 다음 예제를 통하여 SQL Server 파라메터 스니핑에 따른 실행 계획의 변화를 살펴 보자.

 

테스트 테이블을 생성한다.

USE SW_TEST GO   IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers GO   CREATE TABLE Customers ( CustomerID INT identity NOT NULL , CustomerName VARCHAR(50) NOT NULL , CustomerAddress VARCHAR(50) NOT NULL , [State] CHAR(2) NOT NULL , CustomerCategoryID CHAR(1) NOT NULL , LastBuyDate DATETIME , PRIMARY KEY CLUSTERED ( CustomerID ) )   IF OBJECT_ID('dbo.CustomerCategory', 'U') IS NOT NULL DROP TABLE dbo.CustomerCategory GO   CREATE TABLE CustomerCategory ( CustomerCategoryID CHAR(1) NOT NULL , CategoryDescription VARCHAR(50) NOT NULL , PRIMARY KEY CLUSTERED ( CustomerCategoryID ) )   CREATE INDEX IX_Customers_CustomerCategoryID ON Customers(CustomerCategoryID)

 

테스트 데이터를 생성한다.

INSERT INTO [dbo].[Customers] ( [CustomerName], [CustomerAddress], [State], [CustomerCategoryID], [LastBuyDate]) SELECT 'Desiree Lambert', '271 Fabien Parkway', 'NY', 'B', '2013-01-13 21:44:21'   go   INSERT INTO [dbo].[Customers] ( [CustomerName], [CustomerAddress], [State], [CustomerCategoryID], [LastBuyDate]) SELECT 'Pablo Terry', '29 West Milton St.', 'DE', 'A', GETDATE()   go 15000

 

쿼리를 실행 하고 실행 계획을 살펴 보면 첫 번째 쿼리의 경우 CustomerCategory 테이블에서는 Index Seek를 사용하고 Customer 테이블에서는 Index Scan을 사용한다. 두 번째 쿼리의 경우 Customer 테이블에서 비클러스터 인덱스(IX_Customers_CustomerCategoryID)를 사용한다.

쿼리 옵티마이저는 지정된 파라메터에 대해 조회 결과를 예상하여 비클러스터 인덱스에서 키를 찾고 인덱스 조회를 수행하지만 첫 번째의 인덱스 스캔은 거의 모든 테이블 데이터를 반환하기 때문에 더 큰 비용이 발생 한다.

SELECT C.CustomerName, C.LastBuyDate FROM dbo.Customers C INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID WHERE CC.CustomerCategoryID = 'A'   SELECT C.CustomerName, C.LastBuyDate FROM dbo.Customers C INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID WHERE CC.CustomerCategoryID = 'B'

 

 

 

위의 실행한 쿼리를 프로시저로 만든다.

CREATE PROCEDURE Test_Sniffing @CustomerCategoryID CHAR(1) AS   SELECT C.CustomerName, C.LastBuyDate FROM dbo.Customers C INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID WHERE CC.CustomerCategoryID = @CustomerCategoryID   GO

 

캐시를 비운 다음 첫 번째 프로시저 호출에 파라메터 조건을 'A'를 실행하여 실행 계획을 생성하고 두 번째 호출에서 'B'를 사용한 경우 A와 동일한 실행 계획을 사용하는 것을 확인 할 수 있다.

DBCC FREEPROCCACHE() GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'A' EXEC dbo.Test_Sniffing @CustomerCategoryID GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'B' EXEC dbo.Test_Sniffing @CustomerCategoryID GO

 

 

역순으로 실행 한 경우에도 B에서 생성된 실행계획을 A에서 동일하게 사용하는 것을 확인 할 수 있다.

DBCC FREEPROCCACHE() GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'B' EXEC dbo.Test_Sniffing @CustomerCategoryID GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'A' EXEC dbo.Test_Sniffing @CustomerCategoryID GO

 

 

SQL Server 파라메터 스니핑에 대한 대안으로 다음과 같은 방법이 있다.

• WITH RECOMPILE 옵션을 사용하여 저장프로시저 생성
• RECOMPILE 힌트 옵션 사용
• OPTIMIZE FOR 힌트 옵션 사용
• SQL Server 저장 프로시저에 더미 변수 사용
• 인스턴스 수준에서 파라메터 스니핑 사용제한
• 특정 쿼리에 대해 스니핑 비활성화

 

1. WITH RECOMPILE을 포함한 프로시저 생성방법은 다음과 같다.

CREATE PROCEDURE Test_Sniffing_Recompile @CustomerCategoryID CHAR(1) WITH RECOMPILE AS   SELECT C.CustomerName, C.LastBuyDate FROM dbo.Customers C INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID WHERE CC.CustomerCategoryID = @CustomerCategoryID   GO

 

 

1. RECOMPILE 힌트를 사용

OPTION RECOMPILE힌트를 적용한 프로시저를 생성한다.

CREATE PROCEDURE Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID CHAR(1) AS   SELECT C.CustomerName, C.LastBuyDate FROM dbo.Customers C INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID WHERE CC.CustomerCategoryID = @CustomerCategoryID OPTION(RECOMPILE)   GO

 

OPTION RECOMPILE이 적용된 프로시저를 실행 한다.

DBCC FREEPROCCACHE() GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'B' EXEC dbo.Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'A' EXEC dbo.Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID GO

 

 

 

1. OPTIMIZE FOR힌트를 사용

OPTIMIZE FOR UNKNOWN 힌트를 적용한 프로시저를 생성한다.

CREATE PROCEDURE Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID CHAR(1) AS   SELECT C.CustomerName, C.LastBuyDate FROM dbo.Customers C INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID WHERE CC.CustomerCategoryID = @CustomerCategoryID OPTION(OPTIMIZE FOR UNKNOWN )   GO

 

OPTIMIZE FOR UNKNOWN이 적용된 프로시저를 실행 한다.

DBCC FREEPROCCACHE() GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'B' EXEC dbo.Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'A' EXEC dbo.Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID GO

 

 

 

1. 저장프로시저에 더미를 사용한 경우

CREATE PROCEDURE Test_Sniffing_Dummy_Var @CustomerCategoryID CHAR(1) AS DECLARE @Dummy CHAR(1)   SELECT @Dummy = @CustomerCategoryID   SELECT C.CustomerName, C.LastBuyDate FROM dbo.Customers C INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID WHERE CC.CustomerCategoryID = @Dummy   GO

 

더미가 적용된 프로시저를 실행 한다.

DBCC FREEPROCCACHE() GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'B' EXEC dbo.Test_Sniffing_Dummy_Var @CustomerCategoryID GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'A' EXEC dbo.Test_Sniffing_Dummy_Var @CustomerCategoryID GO

 

 

 

1. 인스턴스 수준에서 파라메터 스니핑제한은 가급적 권장하지 않는다. 파라메터 스니핑은 본질적으로 나쁜 것아 아니라 최고의 실생계획을 얻을 수 있는 경우 매우 유용하다. 원하지 않는 경우 Trace Flag 4136을 사용하여 사용하지 않도록 설정 할 수 있다. 다음 링크를 참고하여 설정 할 수 있도록 한다.

• http://support.microsoft.com/kb/980653

 

1. 특정 쿼리에 대해 스니핑 비활성화

특정 쿼리에 대해 옵티마이즈의 동작을 변경하는 힌트를 추적플래그로 사용 할 수 있다. 이 작업을 수행하는 방법은 OPTION절에 QUERYTRACEON 힌트를 추가하는 것이다.

CREATE PROCEDURE Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID CHAR(1) AS   SELECT C.CustomerName, C.LastBuyDate FROM dbo.Customers C INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID WHERE CC.CustomerCategoryID = @CustomerCategoryID OPTION(QUERYTRACEON 4136)   GO

 

DBCC FREEPROCCACHE() GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'B' EXEC dbo.Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID GO   DECLARE @CustomerCategoryID CHAR(1)   SET @CustomerCategoryID = 'A' EXEC dbo.Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID GO

 

 

 

 

시스템을 재시작하거나 저장 프로시저를 만들 때 첫 실행의 조건에 따라 실행 계획이 생성되므로 부득이하게 쿼리가 이상하게 풀리는 경우를 방지 하기 위해 메인터넌스 작업에 수동으로 프로시저를 호출하는 프로세스를 추가하거나 SQL Server 시작 파라메터를 설정하여 최적화된 계획을 생성할 수 있도록 하면 좋을 듯 하다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/

 

 

2014-07-02 / 강성욱 / http://sqlmvp.kr

 

 

파라메터 스니핑, 쿼리힌트, 실행계획, 플랜캐시, 옵티마이즈, DB튜닝, OPTION RECOMPILE, TRACE FALG 4136, OPTIMIZE FOR, 플랜최적화, SQL 튜닝