SQL Server 索引有時工作

所以我的桌子上有以下索引：

CREATE NONCLUSTERED INDEX [$5] ON [dbo].[Record Link]
(
   [Company] ASC,
   [Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [$6] ON [dbo].[Record Link]
(
   [Notify] ASC,
   [Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [$7] ON [dbo].[Record Link]
(
   [Company] ASC,
   [Type] ASC,
   [Link ID] ASC,
   [Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [$8] ON [dbo].[Record Link]
(
   [Company] ASC,
   [Type] ASC,
   [Record ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [bain_test] ON [dbo].[Record Link]
(
   [Record ID] ASC,
   [Type] ASC,
   [Company] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [$4] ON [dbo].[Record Link]
(
   [Notify] ASC,
   [Company] ASC,
   [Link ID] ASC,
   [To User ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

問題是一些索引只被使用了幾次。作為範例查看此查詢和執行計劃，您可以看到它在 EXISTS 內執行，但在查詢的其餘部分不執行。兩個 where 子句是相同的。

這是查詢統計時間和IO：

*SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Record Link'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*

*(1 row affected)*

*SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.*
*(1 row affected)*

*Table 'Record Link'. Scan count 1, logical reads 1017129, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.*

*(1 row affected)*

*SQL Server Execution Times:
  CPU time = 1969 ms,  elapsed time = 1963 ms.*

我沒有從 ERP 系統更改查詢的選項，我正在嘗試優化速度。

雖然我們在這裡，但查詢此表有一些不同的變體。

這是另一個根本不使用索引的。

有了這件事很奇怪，所以我首先嘗試使用欄位的索引：記錄 ID，公司，直到我創建一個新索引並創建它：公司，記錄 ID，這才起作用。

我昨天做了這個並且它正在工作，但是今天早上在分析器中它不再使用索引。

SELECT  TOP (1) 
   "timestamp","Link ID",
   "Record ID",
   "URL1",
   "URL2",
   "URL3",
   "URL4",
   "Description",
   "Type",
   DATALENGTH("Note"),
   "Created",
   "User ID",
   "Company",
   "Notify",
   "To User ID" 
FROM 
   "Live".dbo."Record Link" WITH(UPDLOCK)  
WHERE 
   ("Record ID"=@0 AND "Company"=@1) 
ORDER BY 
   "Link ID" ASC 
OPTION(OPTIMIZE FOR UNKNOWN)

有了這個，我真的想不通，我認為帶有公司、通知、使用者 ID 的索引會起作用：

SELECT TOP (@0)
   "timestamp", "Link ID", "Record ID",
   "URL1", "URL2", "URL3", "URL4",
"Description", "Type", DATALENGTH("Note"), "Created", "User ID",
   "Company", "Notify", "To User ID"
FROM "Live_Replica".dbo."Record Link" WITH(READUNCOMMITTED)
WHERE ("Link ID">@1
 AND (("Company"=@2 OR "Company"=@3)
 AND "Notify"=@4
 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5
   OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6)))
ORDER BY "Link ID" ASC
OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

我試過用Google搜尋這個問題，但似乎根本找不到答案。所有索引每晚都會重建，我根本不收縮數據庫，這樣索引就不會碎片化。

作為範例查看此查詢和執行計劃，您可以看到它在 EXISTS 內執行，但在查詢的其餘部分不執行。兩個 where 子句是相同的。

兩個where子句相同，但select列表不同。

在您的問題中顯示的那些查詢中沒有covering index您的查詢，除了那些exists。

對於exists查詢，重要的是只知道這樣的記錄（帶有[Record ID]= …，[Type]= …，[Company]= …）是否存在，因此伺服器只能控制[bain_test]包含所有這些欄位的索引作為key fiels.

對於第二個query，您向伺服器詢問了許多其他欄位（請參閱您的SELECT列表），這些欄位未包含在此中且未包含在index其他欄位中index，因此，如果伺服器選擇您index，則它必須對lookups基表進行處理，並且由於statistics不能在您的情況下使用（您使用oprimize for unknown了提示），伺服器估計返回的行數太多並選擇執行scan. Scan至少是連續閱讀，而lookups它們是隨機的，而且成本很高。

第三個查詢也是如此。沒有一個索引是covering, 你select的欄位不包含在你的indexes中，即使看起來這個查詢應該只返回一行，它不是random一行，你的tophasorder by子句，所以所有的記錄都應該首先讀取和 10 sorted。

我認為你應該檢查你的 misimg 索引

sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats,

sys.dm_db_missing_index_details

來決定indexes你應該創造什麼。

也看看就知道sys.dm_db_index_usage_stats了unused indexes。

您的索引非常相似，您的條件也相似，至少所有查詢都有Record IDAND Company。你為什麼不認為你的clustered index（在

$$ Record ID $$我想）不是最優的？它已經根據您的條件之一進行了排序，很可能unique並且總是covering。您的索引很可能根本沒有使用。 請用clustered index定義更新你的問題。

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