MSSQL 使用Index Seek了，為什麼還是慢？ CONVERT_IMPLICIT 導致全表 Seek

問題

最近在看一個 MSSQL 的效能問題，多筆的 select COUNT(1) ... 會分別花費 2~6秒，如下圖，

問題解析

它的SQL及執行計畫，如下圖，

sp_executesql @statement=
N'select COUNT(1) as "Count" from [DialogHistory] 
where [ConversationId] = @p0 
and [Watermark] > @p1 and "ActivityContent" & 4 > 0'
,@params=N'@p0 nvarchar(1), @p1 int',@p0=N'',@p1=0

針對成本最高的節點來看，如下圖，

1. 它是索引搜尋(Index Seek)
2. 它的述詞為([ActivityContent]&(4))>(0)
3. 實際批次數目為0
4. 實際讀取的資料列數為4,376,257(如果用到 Index Seek，怎麼還會讀取那麼多的資料?)
5. select count(*) from [DialogHistory] 值為 4,376,257
6. 搜尋述詞為
   1. 起點(StartRange): ConversationId > 純量運算子([Expr1007])
   2. 終點(EndRange): ConversationId < 純量運算子([Expr1008])
7. 整個SQL的邏輯讀取為 34,255

從上述幾點來看，雖然是索引搜尋，但它實際上還是找了整個Table的資料(Actual Rows Read=4,376,257、Actual Rows=0)。
ConversationId在Table中是唯一的，資料型別為VARCHAR，查詢條件是CONVERT_IMPLICIT(nvarchar(250),[ConversationId],0)=[@p0]，找資料會用Range Search(ConversationId > 純量運算子([Expr1007]) AND ConversationId < 純量運算子([Expr1008]))

問題驗證

有可能是因為CONVERT_IMPLICIT的問題，所以將查詢條件從將它轉回VARCHAR來看看它的效果如何，SQL如下，

sp_executesql @statement=
N'select COUNT(1) as "Count" from [DialogHistory] 
where [ConversationId] = CAST(@p0 AS VARCHAR(250)) 
and [Watermark] > @p1 and "ActivityContent" & 4 > 0'
,@params=N'@p0 nvarchar(1), @p1 int',@p0=N'',@p1=0

• 將 @p0 改成 CAST(@p0 AS VARCHAR(250))

針對成本最高的節點來看，如下圖，

1. 它是索引搜尋(Index Seek)
2. 它的述詞為[Watermark]>CONVERT_IMPLICIT(bigint,[@p1],0) AND CONVERT_IMPLICIT(nvarchar(250),[ConversationId],0)=[@p0] AND ([ActivityContent]&(4))>(0)
3. 實際批次數目為0
4. 搜尋述詞為
   1. 前置詞(Prefix): ConversationId = 純量運算子(CONVERT(varchar(250),[@p0],0))
   2. 起點(StartRange): Watermark > 純量運算子(CONVERT_IMPLICIT(bigint,[@p1],0))
5. 整個SQL的邏輯讀取為 5

另外值得注意的是，ConversationId在Table中是唯一的，應該不會有空字串的查詢，如果將''改成'8051f015-06a8-4547-b2a7-7c478fca7739'，它的邏輯讀取會從34,255降到2,802，實際讀取的資料列數降為357,889，所以在程式中，如果ConversationId為空值，直接回傳0，不用再進DB查詢。

結論

當看到索引搜尋(Index Seek)，但 Actual Rows Read（實際讀取的資料列數）遠大於 Actual Rows（實際輸出的資料列數），
且 Seek 使用的是Range Search而非等值搜尋時，根本原因通常是**隱式型別轉換(CONVERT_IMPLICIT)**。

本例中，ConversationId 欄位為 VARCHAR，但應用程式傳入的參數型別為 NVARCHAR，
SQL Server 為了完成比較，將欄位值轉換為 NVARCHAR，破壞了 SARGability，
導致原本應該是等值 Seek 的查詢改為 Range Search，而查詢的值又為空字串，導致掃描了整個 Index。

解決方案： 確保應用程式端傳入的參數型別與欄位型別一致，或在 SQL 中明確用 CAST/CONVERT 轉換參數（而非欄位），
本例改為 CAST(@p0 AS VARCHAR(250)) 後，邏輯讀取從 34,255 降至 5。