MS SQL 2005、パラメータスニッフィング、ストアドプロシージャの禁止

最近、私は問題に遭遇しました。QueryAnalyzerで起動されたスクリプトはすぐに機能し、すでに50秒間はストアドプロシージャの形式でした。 リクエストを最適化するために設計されたパラメータスニッフィングの問題であることが判明しました。 :)



ストレージをコンパイルするときに、SQLサーバーは実行計画を作成します。実行計画では、プロシージャの入力パラメーターが特定の種類のものであると想定します。 これは、パラメータスニッフィングと呼ばれます。

パラメーターがわずかに異なることが判明した場合、実行計画は、リクエストの最適な実行にはやや不適切である可能性があります。



問題を解決する最も簡単な方法は、パラメータスニッフィングを無効にすることです。 これは次のように行われます。ローカル変数がプロシージャの本体で作成され、入力パラメーターの値が割り当てられます。

ALTER PROCEDURE [dbo].[REPORT_USERS_BRANCHES]



@branchId INT ,

@branchName NVARCHAR(100) OUTPUT ,

@filterByPaymentActivity VARCHAR (50),

@dateFrom DATETIME,

@dateTo DATETIME



AS



DECLARE @dtFrom DATETIME, @dtTo DATETIME, @filter VARCHAR (50), @bId INT

SET @bId = @branchId

SET @dtFrom = @DateFrom

SET @dtTo = @DateTo

SET @filter = @filterByPaymentActivity

...



* This source code was highlighted with Source Code Highlighter .

また、最適化オプション（OPTION（OPTIMIZE FOR ...））を使用して、プロシージャを作成するときに、毎回プロシージャを再コンパイルする必要があることを示します（WITH RECOMPILE）。

しかし、これらのオプションは私を助けませんでした。

ディシジョンツリーSPメソッドもありますが、すでに完全に怪物です。



追伸：これは単なる松葉杖であり、問​​題の正しい解決策ではないと確信していますが、プログラマーとしてはこれで十分です。 おそらくDBAはここで最善の方法をアドバイスするでしょうか？