如何教優化器在數據記錄主/明細表上使用索引而不是 fts 連接？

SQL Server 2012 優化器不正確。

測試案例，總結：

這是一個簡化的測試場景。底部的 DDL 語句。

我有兩個用於數據記錄的表，A並且B. 有一個 1:n 的關係 -A具有呼叫日期時間的標題記錄a_time並B具有詳細記錄，其中一個欄位B.akey是引用A.id，並且欄位name和(data)。

A有大約。25,000,000 條記錄，B大約有 25,000,000 條記錄。500,000,000 條記錄。B大約有 200 條記錄引用A. 一A和大約。B每五分鐘一次插入200條記錄，反映為A.a_time.

聚集索引是主鍵id，類型 int 標識。

B``IX_B_akey在 上有一個名為的非聚集索引B.akey。

A.a_time也被（非聚集）索引。

現在這個查詢：

SELECT A.a_time, B.*
FROM B
  join A on B.akey = A.id
where  
   A.a_time > '2017-01-13T01:30:00' and A.a_time < '2017-01-14T07:30:00'
      and B.name in ('name33', 'name55', 'name66')

在我的數據庫伺服器上大約需要*3 分鐘。*執行計劃：here（更準確的執行計劃見下文）

當我添加一個簡單的使用提示時 IX_B_akey：

SELECT A.a_time, B.*
FROM B
  with (index(IX_B_akey))
  join A on B.akey = A.id
where  
   A.a_time > '2017-01-13T01:30:00' and A.a_time < '2017-01-14T07:30:00'
      and B.name in ('name33', 'name55', 'name66')

它執行不到一秒鐘。執行計劃：here（更準確的執行計劃見下文）

update statistics當我在兩個表上手動時，這不會改變。

沒有提示的查詢的查詢計劃顯示伺服器將對 進行表掃描B，尋找匹配name的 s。這需要一段時間也就不足為奇了。通過提示，它使用索引並通過索引查找 B引用匹配記錄的A記錄。這要快得多。

我不想將查詢優化器程式碼放入我的軟體中。另外，我使用 NHibernate。雖然有可能，但使用 NHibernate 攔截器並編輯其 SQL 會很醜陋。

也許優化器不知道B引用一條A記錄的所有記錄在物理上彼此相鄰。它們彼此相鄰，因為它們是同時插入的。如果它們分散在整個數據庫中，則進行所有查找的成本可能會更高。

問題：如何幫助優化器選擇快速計劃而不在查詢中提示？我可以在這裡添加特定的統計數據來提供幫助嗎？我需要儲存的查詢計劃嗎？

作為參考，這裡是用於創建表的 DDL 語句。

create table A (
   id int not null identity(1,1),
   a_time datetime,
   constraint pkA primary key (id)
)

create table B (
   id int not null identity(1,1),
   akey int not null references A (id),
   name nvarchar(50),
   d decimal(5,3),
   constraint pkB primary key (id)
   )

create index IX_B_akey on B (akey)
create index IX_A_a_time on A (a_time)

更新：添加name到索引IX_B_Akey可能會有所幫助，但它也會使數據量幾乎翻倍。這不是一個好的選擇。

執行計劃更新：發布問題後，我創建了另一個具有相同資料結構但數據更多的測試場景。查詢相同，但查詢的日期範圍已擴展。該數據庫在 A 中包含 1 個 mio 記錄，在 B 中包含 200 個 mio 記錄。這使我可以提供實際的執行計劃：

無提示查詢，耗時27s，時間可重現

帶提示查詢，耗時3s，也可複現

糟糕的計劃來自艱難的選擇。您可以重組 B 以優化從 A 到 B 的訪問路徑，而不是讓優化器在具有兩個嵌套循環連接的計劃和具有大並行雜湊連接的計劃之間進行選擇。

這裡最好的索引大概是做B(akey,id)的聚群PK。那麼在已經更快的計劃中將只有一個嵌套循環連接，這顯然比並行雜湊連接計劃更好。

您可以強制執行該計劃，但這將假設 NHibernate 總是產生完全相同的查詢（它可能），或者您可以強制統計資訊使其認為 A 中幾乎沒有任何內容與您正在查看的時間相匹配（所以它認為它不必對 B) 執行 42k 操作。

問題是它預計這些 RID 查找的成本會如此之高，而掃描的預期成本最終會更低。如果您說服它不必進行如此多的 RID 查找，因為您偽造了統計數據，您可能會得到一個更好的計劃。但這通常很危險，因為它可能影響的不僅僅是這個查詢。

所以我會先嘗試強制執行該計劃。

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