[MSSQL] SQL Server 파라미터 스니핑(Parameter Sniffing)

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

 

1. 파라미터 스니핑이란?

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

 

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

 

2. 테스트

 * 테이블을 생성

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

 

3. 대안

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

 

(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

 

(2) RECOMPILE 힌트 옵션 사용

--1. Create Procedure with option recomplie hint
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

--2. Exec Procedure
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

 

(3) OPTIMIZE FOR 힌트 옵션 사용

--1. Create Procedure with optimize for unknown hint
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

--2. Exec Procedure
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

 

(4) SQL Server 저장 프로시저에 더미 변수 사용

--1. Create procedure with Using Dummy
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

--2. Exec Procedure
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

 

(5) 인스턴스 수준에서 파라메터 스니핑 사용제한

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

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

 

(6) 특정 쿼리에 대해 스니핑 비활성화

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

--1. Create Procedure with querytraceon option
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

--2. Exec procudure
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/

https://sungwookkang.com/m/entry/SQL-Server-%ED%8C%8C%EB%9D%BC%EB%A9%94%ED%84%B0-%EC%8A%A4%EB%8B%88%ED%95%91%EC%9D%98-%EB%8B%A4%EC%96%91%ED%95%9C-%EC%A0%91%EA%B7%BC

https://sisseo.tistory.com/15