@고동하

Parameter Sniffing 이란?

SQL Server에서 파리미터가 사용된 SP 또는 쿼리(이하 parameterized query)를 실행할 때 옵티마이저가 파라미터 값을 전달 받아 효율적인 실행 계획을 생성하는 프로세스

간단한 예를 먼저 살펴보자

테스트 환경, 데이터 및 ERD

• DBMS : Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

• SSMS : v18.6

• DATA : AdventureWorks2019(https://docs.microsoft.com/ko-kr/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms)

• 예제 참고 : SQL Server 2017 Query Performance Tuning(Chapter 17)

  

-- [1] Person.Address.City 컬럼을 'London' 조건으로 검색
USE AdventureWorks2019;

CREATE OR ALTER PROC dbo.AddressByCity_London
AS
    SELECT  a.AddressID,
            a.AddressLine1,
            AddressLine2,
            a.City,
            sp.Name AS StateProvinceName,
            a.PostalCode
    FROM    Person.Address AS a
    JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
    WHERE a.City = 'London';

EXEC AddressByCity_London;

-- [2] Person.Address.City 컬럼을 지역 변수 @City 조건으로 검색
CREATE OR ALTER PROC dbo.AddressByCity_Local
AS
DECLARE @City VARCHAR(30) = 'London';
    SELECT  a.AddressID,
            a.AddressLine1,
            AddressLine2,
            a.City,
	          sp.Name AS StateProvinceName,
	          a.PostalCode
    FROM    Person.Address AS a
    JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
    WHERE a.City = @City;

EXEC dbo.AddressByCity_Local;

-- [3] Person.Address.City 컬럼을 프로시저 파라미터 @City 조건으로 검색
-- 아래에서 재사용 예정
CREATE OR ALTER PROC dbo.AddressByCity @City NVARCHAR(30)
AS
    SELECT  a.AddressID,
            a.AddressLine1,
            AddressLine2,
            a.City,
	          sp.Name AS StateProvinceName,
	          a.PostalCode
    FROM    Person.Address AS a
    JOIN    Person.StateProvince AS sp
        ON a.StateProvinceID = sp.StateProvinceID
    WHERE a.City = @City;

EXEC AddressByCity 'London';

위 실행 계획에서 유심히 볼 필요가 있는 부분은 [예상 행 수]에 맞는 [실제 행 수]의 결과이다. [1]번 쿼리에서는 'London' 이라는 값을 알고 있기 때문에 실행 계획부터 정확한 실행 계획을 생성할 수 있었다.

[2]번 쿼리에서는 컴파일이 될 때까지 옵티마이저는 @City에 어떤 값이 들어갈지 알 수 없기 때문에, 통계 데이터를 이용하여 실행계획을 생성하였다.