查詢鍵查找

SELECT   Col1, Col2, Col3, Col4, Col5, Col6, Col25...
FROM     Tabl1
WHERE    (   Col30 IS NOT NULL
      AND   Col28 <> @P0
      AND   DATEDIFF(
                dd,
                Col31,
                CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4
      AND   DATEDIFF(
                dd,
                Col31,
                CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8 )
AND      Col27 = @P9
ORDER BY 18 ASC, 31 DESC, 30 ASC;

上面的範例查詢來自第三方應用程序。我無法控制應用程序。查詢更像是使用者查詢。

該查詢使用索引查找，但它具有 20 列的鍵查找。

當我在非聚集索引中添加 20 個包含列時，查詢不再尋找索引 - 它使用非聚集索引掃描。非聚集索引掃描解決了死鎖，但CPU使用率比使用索引查找+鍵查找高15%。

索引鍵是 Col30,Col28,Col27,Col31,ClusteredIndexKey，包含 20 列。

我正在使用 SQL Server 2016 SP2。

現在，我在 prod 中啟用了查詢儲存。我看到 3 個 plan_id，其中一個失敗了。三角符號，另外兩個查詢執行計劃相同，但plan_id不同。一個比另一個花費更長的時間。有任何想法嗎？

有什麼想法嗎？

您對應用程序有任何控制權嗎？

如果實際上不需要所有列，則此問題的理想解決方案是更改應用程序以選擇更少的列。這看起來像是由 ORM（如實體框架）生成的查詢，預設情況下將選擇所有列（完全公開：那是我的部落格）。

該部落格中有關於如何獲取實體框架查詢以選擇更少列的詳細資訊。該解決方案的本質是在查詢執行之前為查詢定義一個“投影”。

Select(...)在 LINQ for Entity Framework 和 NHibernate 中，當使用方法語法（或select關鍵字，如果您使用查詢語法）時，這是通過擴展方法完成的。

---

正如您所發現的，對於這個問題並沒有真正好的基於索引的解決方案。您已經介紹了兩個選項：

• 使用鍵查找進行搜尋（您提到此計劃形狀存在死鎖問題）
• NC 索引中有很多包含，導致索引掃描和高 CPU

另一種選擇是刪除非聚集（行儲存）索引，並添加一個非聚集列儲存索引。如果索引被使用，您仍然需要掃描 - 但掃描應該更有效。這主要是推測，因為它在很大程度上取決於列的數據類型的大小、表中的行數等。

我發現當我的查詢花費大量時間做鍵時，如果我稍微改變一下，我可以為引擎提供一個更好的機制來訪問主表。CTE_Keys 僅使用非聚集索引，並且連接允許它避免鍵查找或聚集索引掃描。我假設 Col1 是表的主鍵。

;WITH CTE_Keys AS
   (
   SELECT Col1
   FROM Tabl1
   WHERE    (   Col30 IS NOT NULL
      AND   Col28 <> @P0
      AND   DATEDIFF(
                dd,
                Col31,
                CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4
      AND   DATEDIFF(
                dd,
                Col31,
                CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8 )
   AND      Col27 = @P9 
   )
SELECT T.Col1, T.Col2, T.Col3, T.Col4, T.Col5, T.Col6, T.Col25...
FROM Tabl1 AS T
   INNER JOIN CTE_Keys AS K ON K.Col1 = T.Col1
ORDER BY 18 ASC, 31 DESC, 30 ASC;

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