パラメーター スニッフィングによる 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 中国語 Web サイトの他の関連記事を参照してください。