非聚集索引掃描屬性視窗 - 謂詞順序與查詢順序不同

SQL 2012 數據倉庫類型環境…

我在 Type 2 Dimension 表上有一個非聚集索引，我將其稱為 DimStudent。

索引定義如下，數據類型和其他資訊在上下文的括號中：

(

[StudentID] ASC (VARCHAR(10))

,[EffectiveStartDate] ASC (SMALLDATETIME)

,[EffectiveEndDate] ASC (SMALLDATETIME)

)

INCLUDE [StudentKey] (INT, IDENTITY, PK).

有問題的查詢在 SSIS ETL 包期間執行，定義如下。它是在使用部分記憶體的查找轉換中指定的，在“高級”選項卡中使用“自定義查詢”部分指定，該部分主要由 Visual Studio 自動生成（將

$$ refTable $$alias 和 SELECT * 進入查詢），對 SCD2 查找用法的查詢進行了一些修改：

select * from (SELECT

LTRIM(RTRIM(StudentID)) AS StudentID_Trimmed

,StudentKey

,EffectiveStartDate

,EffectiveEndDate

FROM dbo.DimStudent

)
AS [refTable]

where [refTable].[StudentID_Trimmed] = @P1

and [refTable].[EffectiveStartDate] <= @P2

and [refTable].[EffectiveEndDate] > @P3

請注意，查詢中列的順序是學生 ID (@P1)，然後是開始日期 (@P2)，然後是結束日期 (@P3)。

另請注意，我目前正在修剪 StudentID。我已經確定這不再是必要的（由於某種原因在某些時候是必要的），並且我已經在努力更改 ETL 以刪除該 TRIM 方面。

在此查詢的查詢計劃中（使用 Adam Macahanic 的 sp_WhoIsActive），我已確認它正在使用此索引進行非聚集掃描。當我查看掃描運算符的屬性時，它將謂詞列為：（刪除了數據庫和架構）

[DimStudent].[EffectiveStartDate] <= **[@P2]**

AND [DimStudent].[EffectiveEndDate] > **[@P3]**

AND ltrim(rtrim([DimStudent].[StudentID])) = **[@P1]**

我的問題是：

1. 為什麼查詢計劃中的謂詞與查詢的編寫順序（@P2、@P3、@P1）不同？（@P1、@P2、@P3）由 TRIM 引起？
2. 為什麼它是掃描而不是搜尋，因為索引是為此查詢設計的？由 TRIM（不是 SARGable）引起的？

SQL Server 認為日期更具選擇性，因為它知道開始日期和結束日期欄位中的值是什麼樣的。由於 StudentId 列的修剪，它不知道有多少行。一旦您將其修復為不使用修剪後的計算列，它應該可以按預期工作。

你有幾件事在這裡發生。首先，由於您使用的是修剪，因此優化器可能首先使用日期範圍，因為它會更好地了解在哪裡查看以及將返回多少行。

第二。您可能正在接受掃描，因為您已要求部分記憶體，因此它與個別行不匹配。如果您不進行無記憶體查找，我認為您會開始看到索引搜尋。

第三。如果您的維度表相對較大並且您的事實表很大，那麼簡單地在學生表上進行完整記憶體可能是有意義的。

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