帶重新編譯的儲存過程

我有這個儲存過程..當我告訴開發人員不建議使用重新編譯選項時，他們回答說“這是因為這個 SP 可以用許多不同的參數呼叫，我們希望優化器用每次呼叫（並不理想，但它試圖讓它每次執行得更可靠，比使用舊的記憶體計劃）”

他們說的對嗎？他們有什麼辦法可以做到這一點而無需重新編譯

  create PROCEDURE [dbo].[VERIFIER_QUEUE]
  @cas_name varchar(20) = NULL,
  @instance_name varchar(50) = NULL,
  @verifier_id int = NULL,
  @applicant_type VARCHAR(20) = NULL

   WITH RECOMPILE     
   AS

  BEGIN 
SET NOCOUNT ON

DECLARE @cas_name_x varchar(20)
DECLARE @instance_name_x varchar(50)
DECLARE @verifier_id_x int
DECLARE @InstanceId INT
...............

Michael Green 是對的：開發人員正試圖阻止參數嗅探，當 SQL Server 編譯一個對一組參數值很好但對其他參數值很糟糕的計劃時，就會發生這種情況。

您將希望OPTION (RECOMPILE)在有問題的語句上使用，而不是WITH RECOMPILE在程序上使用。而且我不推薦使用局部變數“技巧”——它只會讓程式碼更混亂；OPTIMIZE FOR UNKNOWN如果這是最適合您的場景的方法，則最好在現代版本上使用。（有關此主題的更多資訊，請參閱Paul White 的這篇精彩文章。）

此外，如果許多參數是可選的（所以查詢有類似的東西WHERE col = @param or @param IS NULL），這就是我所說的“廚房水槽”——有時動態 SQL 可能是一個更有效的解決方案。您沒有顯示其餘程式碼，只是您已經在使用局部變數技巧，但它基本上看起來像這樣：

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ... WHERE 1 = 1';

IF @cas_name IS NOT NULL
 SET @sql += N' AND cas_name = @cas_name';

IF @instance_name IS NOT NULL
 SET @sql += N' AND instance_name = @instance_name';

IF @verified_id IS NOT NULL
 SET @sql += N' AND verifier_id = @verifier_id';

...

SET @sql = @sql + N' OPTION (RECOMPILE);';
PRINT @sql;

EXEC sys.sp_executesql @sql,
 N'@cas_name VARCHAR(20), @instance_name VARCHAR(50), @verifier_id INT, ...',
 @cas_name, @instance_name, @verifier_id, ...;

這種只為實際提供的參數添加子句的方法可以保護您免受基於不同參數集的記憶體計劃（例如，如果我@FirstName在第一次執行時提供，則在我要求@LastName LIKE N'%s%'）。最後OPTION (RECOMPILE);的 可以保護您免受計劃的影響，這些計劃可能會根據執行到執行的相同參數的值而有很大差異（例如，WHERE name LIKE N'%s%'應該產生與 不同的計劃形狀WHERE name LIKE N'Q%'）。

這通常最適合伺服器設置optimize for ad hoc workloads，您可以在此處和此處閱讀。本質上，這樣做是為了防止您的計劃記憶體被所有這些輕微的計劃變化填滿，除非它們被多次使用。（是的，使用OPTION (RECOMPILE)，這一點沒有實際意義；但是，伺服器設置不會對您的其他臨時查詢工作負載造成傷害，而且我從來沒有遇到過使用它的缺點。）

這對於 SQL 注入是非常安全的，因為您不必擔心將使用者輸入連接到 SQL 字元串中（所有參數都是強類型的），但是閱讀有關動態 SQL 的這些主題也不會有什麼壞處：

• 保護自己免受 SQL 注入 - 第 1 部分
• 保護自己免受 SQL 注入 - 第 2 部分
• 動態 SQL 的禍與福(Erland Sommarskog)
• T-SQL 中的動態搜尋條件（也適用於 Erland）

我在這里和這裡有關於我的“廚房水槽”解決方案的影片以及關於它的部落格文章。

引用自：https://dba.stackexchange.com/questions/108593