[SQL] SQL의 가난한 저장 프로 시저 실행 계획 성능 - 매개 변수 스니핑

SQL의 가난한 저장 프로 시저 실행 계획 성능 - 매개 변수 스니핑

나는 값이 전달되지 않은 경우 나중에 현재 날짜로 설정된 날짜 입력을 허용하는 저장 프로 시저를 가지고 :

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

@MyDate이 저장 프로 시저가 처음 컴파일 NULL로 전달되는 경우, 성능이 항상 날짜 / 현재 날짜가시에 전달되는 경우 wheras 모든 입력 값 (그렇지 않으면 NULL 또는)에 대한 끔찍한 이에 나는 문제가있어 저장된 절차는 성능이 모든 입력 값 (또는, 그렇지 않으면 NULL) 괜찮지 컴파일된다.

무엇도 혼란되어 생성되는 가난한 실행 계획 @MyDate의 값이 사용 끔찍한도 실제로 NULL되어 있다는 것이다 (그리고 IF 문에 의해 CURRENT_TIMESTAMP로 설정되지 않음)

나는 파라미터 (매개 변수를 위장하여) 스니핑을 사용하지 않도록 설정하는 것은 내 문제를 해결할 수 있음을 발견했습니다 :

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    DECLARE @MyDate_Copy DATETIME
    SET @MyDate_Copy = @MyDate
    IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
    -- Do Something using @MyDate_Copy

나는이 매개 변수 스니핑 함께 할 수있는 뭔가 알고,하지만 난 전달 비 대표 매개 변수를 사용하여 컴파일되는 저장 프로 시저를 포함했다 "매개 변수 사라 나쁜 냄새를 맡고"의 본 적이 모든 예제, 그러나 여기에서 나는 것을보고 있어요 CURRENT_TIMESTAMP 또는 그렇지 않으면 NULL - 실행 계획은 SQL 서버 매개 변수가 명령문이 실행되는 시점에 걸릴 수 있습니다 생각하는 모든 생각할 수있는 값에 대한 끔찍한입니다.

사람이 왜 이런 일이 어떤 통찰력을 가지고 했나요?

해결법

1. ==============================

   1.기본적으로 예 - (일부 패치 수준) 매개 변수 스니핑 SQL 서버 2005 심하게 파손됩니다. 나는 계획을 볼 경우에도 매개 변수가 마스크되면 초 전체 데이터의 작은 (몇 천 행) 세트 (작은 데이터 세트에 시간 이내)을 효과적으로 결코 완전한 그. 그리고 이것은 매개 변수가 항상 같은 수 있었다 경우입니다. 나는 좌 / 널 (NULL)이 완료되지 가입으로 나는이 처리 된 동시에, 나는 많은 문제를 발견하고 내가 NOT IN로 교체 또는 NOT EXISTS이 완료 무언가에 대한 계획을 해결할 것을 추가합니다. 다시, (매우 나쁨) 실행 계획 문제. 나는이 처리 된 시점에서, DBA는 나에게 SHOWPLAN 액세스 권한을 부여하지 않을, 내가 모든 SP 매개 변수를 마스킹 시작한 이후, 나는 비 완성이에 발굴해야 더 실행 계획 문제가 없었습니다 .

   기본적으로 예 - (일부 패치 수준) 매개 변수 스니핑 SQL 서버 2005 심하게 파손됩니다. 나는 계획을 볼 경우에도 매개 변수가 마스크되면 초 전체 데이터의 작은 (몇 천 행) 세트 (작은 데이터 세트에 시간 이내)을 효과적으로 결코 완전한 그. 그리고 이것은 매개 변수가 항상 같은 수 있었다 경우입니다. 나는 좌 / 널 (NULL)이 완료되지 가입으로 나는이 처리 된 동시에, 나는 많은 문제를 발견하고 내가 NOT IN로 교체 또는 NOT EXISTS이 완료 무언가에 대한 계획을 해결할 것을 추가합니다. 다시, (매우 나쁨) 실행 계획 문제. 나는이 처리 된 시점에서, DBA는 나에게 SHOWPLAN 액세스 권한을 부여하지 않을, 내가 모든 SP 매개 변수를 마스킹 시작한 이후, 나는 비 완성이에 발굴해야 더 실행 계획 문제가 없었습니다 .

   SQL 서버 2008에서는 UNKNOWN 최적화를 사용할 수 있습니다.

2. ==============================

   2.내가 (SQL 서버 2005) 대신에 단지 지역의 매개 변수를 재 선언하여 매개 변수를 마스킹에서이 문제를 주변에 얻을 수 있었던 한 가지 방법은 쿼리 옵티 마이저 힌트를 추가하는 것이 었습니다.

   내가 (SQL 서버 2005) 대신에 단지 지역의 매개 변수를 재 선언하여 매개 변수를 마스킹에서이 문제를 주변에 얻을 수 있었던 한 가지 방법은 쿼리 옵티 마이저 힌트를 추가하는 것이 었습니다.

   여기 좋은 블로그 게시물입니다 그것에 대해 이야기 더 : SQLSERVER 2005 년 파라미터 스니핑

   내가 사용 : OPTION (대한 최적화를 (@p = '-1'))

3. ==============================

   3.절차 절차 내부 변수를 선언하고, 내부 .. 컴파일 외부 파라미터를 전달 ..

   절차 절차 내부 변수를 선언하고, 내부 .. 컴파일 외부 파라미터를 전달 ..

from https://stackoverflow.com/questions/1007397/sql-poor-stored-procedure-execution-plan-performance-parameter-sniffing by cc-by-sa and MIT license