得到掃描雖然我期待尋找

我需要優化SELECT語句，但 SQL Server 總是執行索引掃描而不是查找。當然，這是在儲存過程中的查詢：

CREATE PROCEDURE dbo.something
 @Status INT = NULL,
 @IsUserGotAnActiveDirectoryUser BIT = NULL    
AS

   SELECT [IdNumber], [Code], [Status], [Sex], 
          [FirstName], [LastName], [Profession], 
          [BirthDate], [HireDate], [ActiveDirectoryUser]
   FROM Employee
   WHERE (@Status IS NULL OR [Status] = @Status)
   AND 
   (
     @IsUserGotAnActiveDirectoryUser IS NULL 
     OR 
     (
       @IsUserGotAnActiveDirectoryUser IS NOT NULL AND       
       (
         @IsUserGotAnActiveDirectoryUser = 1 AND ActiveDirectoryUser <> ''
       )
       OR
       (
         @IsUserGotAnActiveDirectoryUser = 0 AND ActiveDirectoryUser = ''
       )
     )
   )

這是索引：

CREATE INDEX not_relevent ON dbo.Employee
(
   [Status] DESC,
   [ActiveDirectoryUser] ASC
)
INCLUDE (...all the other columns in the table...); 

計劃：

為什麼 SQL Server 選擇掃描？我該如何解決？

列定義：

[Status] int NOT NULL
[ActiveDirectoryUser] VARCHAR(50) NOT NULL

狀態參數可以是：

NULL: all status,
1: Status= 1 (Active employees)
2: Status = 2 (Inactive employees)

IsUserGotAnActiveDirectoryUser 可以是：

NULL: All employees
0: ActiveDirectoryUser is empty for that employee
1: ActiveDirectoryUser  got a valid value (not null and not empty)

我不認為掃描是由搜尋空字元串引起的（雖然您可以為這種情況添加過濾索引，但它只會幫助查詢的非常具體的變體）。您更有可能成為參數嗅探的受害者，並且單個計劃未針對您將提供給此查詢的所有各種參數（和參數值）組合進行優化。

我將此稱為“廚房水槽”過程，因為您期望一個查詢來提供所有東西，包括廚房水槽。

我有關於我的解決方案的影片here和here以及關於它的部落格文章，但基本上，我對此類查詢的最佳體驗是：

• 動態建構語句- 這將允許您省略提及未提供參數的列的子句，並確保您將擁有一個針對通過值傳遞的實際參數精確優化的計劃。
• 使用OPTION (RECOMPILE)- 這可以防止特定參數值強制執行錯誤類型的計劃，當您有數據傾斜、錯誤統計資訊或當第一次執行語句使用會導致不同計劃的非典型值時特別有用處決。
• 使用伺服器選項optimize for ad hoc workloads- 這可以防止只使用一次的查詢變體污染您的計劃記憶體。

啟用針對臨時工作負載的優化：

EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

更改您的程序：

ALTER PROCEDURE dbo.Whatever
 @Status INT = NULL,
 @IsUserGotAnActiveDirectoryUser BIT = NULL
AS
BEGIN 
 SET NOCOUNT ON;
 DECLARE @sql NVARCHAR(MAX) = N'SELECT [IdNumber], [Code], [Status], 
    [Sex], [FirstName], [LastName], [Profession],
    [BirthDate], [HireDate], [ActiveDirectoryUser]
  FROM dbo.Employee -- please, ALWAYS schema prefix
  WHERE 1 = 1';

  IF @Status IS NOT NULL
    SET @sql += N' AND ([Status]=@Status)'

  IF @IsUserGotAnActiveDirectoryUser = 1
    SET @sql += N' AND ActiveDirectoryUser <> ''''';
  IF @IsUserGotAnActiveDirectoryUser = 0
    SET @sql += N' AND ActiveDirectoryUser = ''''';

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

  EXEC sys.sp_executesql @sql, N'@Status INT, @Status;
END
GO

一旦您有了基於您可以監控的那組查詢的工作負載，您就可以分析執行並查看哪些執行最能從附加或不同的索引中受益 - 您可以從多個角度執行此操作，從簡單的“哪種組合最常提供參數？” 到“哪些單個查詢的執行時間最長？” 我們無法僅根據您的程式碼回答這些問題，我們只能建議任何索引僅對您嘗試支持的所有可能參數組合的子集有幫助。例如，如果@Status為 NULL，則無法針對該非聚集索引進行搜尋。因此，對於那些使用者不關心狀態的情況，您將進行掃描，除非您有一個滿足其他子句的索引（但考慮到您目前的查詢邏輯，這樣的索引也沒有用處- 空字元串或非空字元串不是完全有選擇性的）。

在這種情況下，根據可能Status值的集合以及這些值的分佈情況，OPTION (RECOMPILE)可能不需要。但是，如果您確實有一些會產生 100 行的值和一些會產生數十萬行的值，您可能希望它在那裡（即使 CPU 成本，考慮到此查詢的複雜性，這應該是微不足道的），這樣您就可以get 在盡可能多的情況下尋找。如果值的範圍足夠有限，你甚至可以用動態 SQL 做一些棘手的事情，你說“我有這個非常有選擇性的值@Status，所以當這個特定的值被傳遞時，對查詢文本進行輕微的修改，以便這被認為是一個不同的查詢，並針對該參數值進行了優化。”

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