SQL Server 의 장점 중의 하나는 Query Optimizer 가 있다는 것이다. 그런데 이 옵티마이저는 Parameter Sniffing 이 일어날 경우에 성능의 저하를 일으킬 수 있다는 것이다.

Parameter Sniffing 은 Stored Procedure 에서 파라미터를 사용하여 Value 를 Pass 할 경우에 전형적인 경우에서 발생하는 캐쉬가 저장되지 않고 비전형적인 Value 가 Pass 되었을 경우의 캐쉬가 저장되어 전형적인 경우에서도 계속적으로 캐쉬가 사용되어 비효율과 성능저하를 나타내는 경우를 말한다.

적어놓고도 말이 좀 어려운데 간단한 예를 들어 설명하겠다. 미국 쇼핑몰을 예를 들면, 물건을 주문했을 경우에 Country 라는 파라미터에는 전형적으로 "United States" 라는 Value 가 입력될 것이다. 이 경우에 생성되는 캐쉬는 Clustered Index Scan 을 사용하여 쿼리를 서비스 할 것이다. 하지만 자주 주문이 되지 않는 "France" 라는 Value 가 입력이 되었다면 옵티마이저는 Nonclustered Index Seek 를 쿼리 플랜에 사용하게 될 것이다. 그렇담, 비전형적인 후자의 Value 가 먼저 입력이 되어 캐쉬가 생성된다면 전형적인 경우에도 캐쉬를 사용하여 처리를 하게 된다는 것이다. 다시 말해, 비전형적인 경우로 전형적인 경우를 처리하니 성능이 저하되는 것은 물론이다.

이에 대한 해결책으로 다음과 같은 쿼리문이 사용될 수 있겠다.

Query 문 (Language : sql)

1. CREATE PROC prSalesByCountry
2. @country char(3),
3. @template_country char(3)="United States"
4. AS
5. SET @template_country=@country
6. SELECT * FROM sales WHERE country=@template_country
7. GO

위의 쿼리문을 사용한다면 Default Value인 United States 가 컴파일시에 사용이 될 것이며 런타임에 사용되어 지지는 않게 된다. 그래서 전형적인 방법으로 캐쉬가 생성되어 지며 그 캐쉬가 계속적으로 사용되어 지는 것이다.

위의 방법도 있지만 그 외에도 WITH RECOMPILE 옵션을 사용하여 Stored Procedure 를 생성하여 Parameter Sniffing 을 막을 수 있다.