매개 변수 스니핑으로 인한 SQL Server 저장 프로시저 성능 저하를 어떻게 해결할 수 있습니까?

매개변수 스니핑으로 인한 SQL Server 저장 프로시저 성능 저하

매개변수 스니핑은 SQL Server에서 다음을 선택하여 쿼리 성능을 향상시키기 위해 사용하는 최적화 기술입니다. 저장 프로시저가 처음 컴파일될 때의 매개 변수 값을 기반으로 하는 실행 계획입니다. 그러나 어떤 경우에는 매개변수 스니핑으로 인해 성능 문제가 발생할 수 있습니다.

다음 저장 프로시저를 고려하세요.

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

이 저장 프로시저가 @MyDate를 NULL로 사용하여 처음 실행되면 쿼리는 옵티마이저는 특정 매개변수 값에 최적화된 실행 계획을 생성합니다. 그러나 나중에 @MyDate에 대해 다른 값을 사용하여 저장 프로시저가 실행되면 쿼리 최적화 프로그램이 최적의 실행 계획을 컴파일하지 못하여 성능 저하로 이어질 수 있습니다.

이 문제를 "매개 변수 스니핑 불량"이라고 합니다. . 위의 예에서는 저장 프로시저에서 @MyDate를 사용할 때 실제로 NULL이더라도 초기 컴파일을 기반으로 생성된 잘못된 실행 계획이 그대로 유지됩니다.

이 문제를 해결하려면 매개 변수를 비활성화하면 됩니다. 매개변수를 스푸핑하여 스니핑:

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

이 접근 방식은 입력 매개변수의 복사본을 생성하고 해당 복사본을 쿼리 최적화 프로세스에 사용하여 매개변수 편향을 방지합니다. 스니핑.

SQL Server 2008 이상에서는 OPTIMIZE FOR UNKNOWN 힌트를 사용하여 매개 변수 스니핑을 최적화할 수 있습니다.

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
WITH RECOMPILE, OPTIMIZE FOR UNKNOWN
AS
    -- Do Something using @MyDate

이 힌트는 쿼리 최적화 프로그램에 알 수 없는 내용을 기반으로 실행 계획을 생성하도록 지시합니다. 매개변수 값을 사용하여 매개변수 스니핑 관련 성능 문제의 위험을 제거합니다.

위 내용은 매개 변수 스니핑으로 인한 SQL Server 저장 프로시저 성능 저하를 어떻게 해결할 수 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!