SQL Server–If 儲存過程中的邏輯和計劃記憶體

SQL Server 2012 和 2016 標準版：

如果我將if-else邏輯放入儲存過程中以執行兩個程式碼分支之一，具體取決於參數的值，引擎是否記憶體最新版本？

如果在接下來的執行中，參數的值發生了變化，是否會重新編譯並重新記憶體儲存過程，因為必須執行不同的程式碼分支？（這個查詢編譯起來非常昂貴。）

SQL Server 2012 和 2016 Standard：如果我將 if-else 邏輯放入儲存過程中以執行兩個程式碼分支之一，取決於參數的值，引擎是否記憶體最新版本？

不，它會記憶體所有版本。或者更確切地說，它記憶體了一個版本，其中包含探索的所有路徑，使用第一組傳入的變數進行編譯。所有計劃的基數估計都將使用它們完成。如果某些傳入的值是 NULL，這可能會更加糟糕。

這是一個使用 Stack Overflow 數據庫的快速展示。

創建索引：

CREATE INDEX ix_yourmom ON dbo.Users (Reputation) INCLUDE (Id, DisplayName);
GO 

在分支程式碼中使用指向不存在的索引的索引提示創建儲存過程。

CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS 
BEGIN

   IF @Reputation = 1
   BEGIN
       SELECT u.Id, u.DisplayName, u.Reputation
       FROM dbo.Users AS u WITH (INDEX = PK_Users_Id)
       WHERE u.Reputation = @Reputation;
   END;
   
   IF @Reputation > 1
   BEGIN
       SELECT u.Id, u.DisplayName, u.Reputation
       FROM dbo.Users AS u WITH (INDEX = ix_yourdad)
       WHERE u.Reputation = @Reputation;
   
   END;

END;

如果我執行該儲存過程以查找 Reputation = 1，則會出現錯誤。

EXEC dbo.YourMom @Reputation = 1;

消息 308，級別 16，狀態 1，程序 YourMom，第 14 行

$$ Batch Start Line 32 $$表“dbo.Users”（在 FROM 子句中指定）上的索引“ix_yourdad”不存在。

如果我們修復索引名稱並重新執行查詢，記憶體計劃如下所示：

在內部，XML 將有兩個對@Reputation變數的引用。

<ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" />

一個稍微簡單的測試是只獲得儲存過程的估計計劃。您可以看到優化器正在探索兩條路徑：

如果在接下來的執行中，參數的值發生了變化，是否會重新編譯並重新記憶體儲存過程，因為必須執行不同的程式碼分支？（這個查詢編譯起來非常昂貴。）謝謝。

不，它將保留第一次編譯的執行時值。

如果我們用不同的方式重新執行@Reputation：

EXEC dbo.YourMom @Reputation = 2;

從實際計劃：

<ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(2)" />

我們的編譯值仍然為 1，但現在執行時值為 2。

在計劃記憶體中，您可以使用我公司開發的免費工具sp_BlitzCache 進行檢查：

儲存過程被呼叫了兩次，其中的每個語句都被呼叫了一次。

那麼我們有什麼？儲存過程中兩個查詢的一個記憶體計劃。

如果您想要這種分支邏輯，則必須呼叫子儲存過程：

CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS 
BEGIN

   IF @Reputation = 1
   BEGIN
       
       EXEC dbo.Reputation1Query;

   END;
   
   IF @Reputation > 1
   BEGIN
       
       EXEC dbo.ReputationGreaterThan1Query;
   
   END;

END;

或動態 SQL：

DECLARE @sql NVARCHAR(MAX) = N''

SET @sql +=
N'
SELECT u.Id, u.DisplayName, u.Reputation
       FROM dbo.Users AS u '
IF @Reputation = 1
BEGIN
   SET @sql += N' (INDEX = PK_Users_Id)
       WHERE u.Reputation = @Reputation;'
END;


IF @Reputation > 1 
BEGIN

SET @sql += ' WITH (INDEX = ix_yourmom)
       WHERE u.Reputation = @Reputation;'

END;


EXEC sys.sp_executesql @sql;

希望這可以幫助！

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