掃描觸發 SSMS 上的搜尋

我在表上插入/刪除時遇到觸發器的性能問題，它會掃描與視圖關聯的索引，但如果我手動執行更新，它會生成一個查找。

我更新了視圖和 SubscriberXList 表的統計資訊，但它繼續進行掃描。還要重新編譯…重新啟動引擎（這是一個測試環境）…向上帝祈禱：p

表：

訂戶

SubscriberXList（Subscribers 和 List 之間的 N 對 N 關係）

SubscribersAmountByList（索引視圖）

CREATE TABLE [dbo].[SubscriberXList] (
   [IdList] INT      NOT NULL,
   [IdSubscriber]      INT      NOT NULL,
   [Active]            BIT      NOT NULL,
   CONSTRAINT [PK_SubscriberXList] PRIMARY KEY CLUSTERED ([IdList] ASC, [IdSubscriber] ASC),
   CONSTRAINT [FK_SubscriberXList_Suscriber] FOREIGN KEY ([IdSubscriber]) REFERENCES [dbo].[Subscriber] ([IdSubscriber]),
   CONSTRAINT [FK_SubscriberXList_List] FOREIGN KEY ([IdList]) REFERENCES [dbo].[List] ([IdList]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_SubscriberXList_IdSubscriber]
   ON [dbo].[SubscriberXList]([IdSubscriber] ASC)
   INCLUDE([IdList], [Active]);
GO

CREATE VIEW [dbo].[vw_SubscribersAmountByList]  WITH SCHEMABINDING
AS
SELECT 
   [sxl].IdList,
   COUNT_BIG(*) AS Amount
   FROM  [dbo].[SubscriberXList] AS [sxl] 
   INNER JOIN [dbo].[Subscriber] AS [s]  ON [sxl].[IdSubscriber] = [s].[IdSubscriber]
   WHERE (1 = [sxl].[Active]) AND (1 = [s].[Status] OR 2 = [s].[Status])
   GROUP BY [sxl].IdList
GO
CREATE UNIQUE CLUSTERED INDEX [CI_SubscribersAmountByList]
   ON [dbo].[SubscribersAmountByList]([IdList] ASC);

生成掃描的查詢屬於 SubscriberXList 插入和刪除上的觸發器。在觸發器結束時有一個更新，當需要更新與 SubscriberXList 和 Subscriber 相關的視圖時會生成掃描。

CREATE TRIGGER dbo.T_SubscriberxList_I
ON dbo.SubscriberXList
AFTER INSERT
AS
BEGIN
   DECLARE @Subscribers TABLE (IdSubscriber INT PRIMARY KEY CLUSTERED, Total INT)
   DECLARE @TotalSubscribers INT


   UPDATE dbo.SubscriberXListAmount
   SET Amount = Amount + Total
   OUTPUT inserted.IdSubscriber, inserted.Amount INTO @Subscribers
   FROM dbo.SubscriberXListAmount SXLA
   JOIN (SELECT IdSubscriber, COUNT(1) Total
       FROM INSERTED I
       INNER JOIN dbo.List SL on SL.IdList = I.IdList
       WHERE SL.IsSegment = 0 AND SL.Active = 1 AND SL.Visible = 1
       GROUP BY I.IdSubscriber) s on s.IdSubscriber = SXLA.IdSubscriber

   SET @TotalSubscribers = @@ROWCOUNT;

   DELETE FROM @Subscribers
   FROM @Subscribers s
   JOIN dbo.Subscriber  s2 ON s2.IdSubscriber = s.IdSubscriber
   WHERE S2.IdSubscribersStatus <> 2

   SET @TotalSubscribers = @TotalSubscribers - @@ROWCOUNT;

   IF @TotalSubscribers > 0
   BEGIN
       declare @query nvarchar(max) = 'DECLARE @Subscribers TABLE (IdSubscriber INT PRIMARY KEY CLUSTERED)'

       SELECT @query = @query + '
       INSERT INTO @Subscribers (IdSubscriber) VALUES ('+ CONVERT(varchar,IdSubscriber) + ')'
       FROM @Subscribers

       SET @Query = @query + '
       UPDATE dbo.Subscriber
       SET IdSubscribersStatus = 1
       FROM @Subscribers s2 
       JOIN dbo.Subscriber s ON s2.IdSubscriber = s.IdSubscriber
       OPTION(force order, fast '+convert(varchar,@totalSubscribers)+')';
       EXEC (@query)
   END
END
GO

觸發觸發器時的執行計劃 https://www.brentozar.com/pastetheplan/?id=r1mEYGNEB

手動執行更新的執行計劃 https://www.brentozar.com/pastetheplan/?id=B11hKM4EB

計算索引視圖的新值時會發生這種情況。

同樣在執行計劃中，它推薦了一個新索引

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SubscriberXList] ([Active])
INCLUDE ([IdList],[IdSubscriber])

這個索引是一個壞索引，因為 SubscriberXList 表有 80% 的行 Active = 1，它是我的數據庫中最大的表之一。

我嘗試使用動態查詢設置要在“Option (fast ‘+convert(varchar,@AmountSubscribersToUpdate)+’)”上更新的行數，但繼續生成掃描。

是什麼導致在觸發器和手動 ssms 之間更改執行計劃？

謝謝！

這並不是真正的“蘋果對蘋果”的比較。查詢的觸發器版本如下所示：

UPDATE dbo.Subscriber
SET IdSubscribersStatus = 1
FROM @Subscribers s2 
JOIN dbo.Subscriber s ON s2.IdSubscriber = s.IdSubscriber
OPTION(force order, fast 1)

查詢的手動版本如下所示：

update dbo.Subscriber
set IdSubscribersStatus = 2
FROM dbo.subscriber s
JOIN @SUbscriber S2 on s2.IdSubscriber = s.IdSubscriber

在觸發器版本中，連接順序是強制的，表的寫入順序已經與手動更新相反。由於快速計劃沒有FORCE ORDER提示，我建議刪除它，並反轉觸發器中的表名以匹配您的手動更新查詢。

您還可以從添加語句中受益OPTION (RECOMPILE)，UPDATE以便 SQL Server 準確了解表變數中有多少行。

---

為了完整起見，執行計劃中還有一些其他差異：

• 快速計劃是在 SQL Server 2014 的一個新版本上。

  • 快速：SP3 CU2
  • 慢速：SP1+GDR

• 快速計劃有更多可用記憶體，這會影響優化器的計劃選擇

  • 快速地：EstimatedAvailableMemoryGrant="583270" EstimatedPagesCached="583270"
  • 慢的：EstimatedAvailableMemoryGrant="307200" EstimatedPagesCached="153600"

• 快速計劃是在具有更多可用於並行性的核心的伺服器上

  • 快速地：EstimatedAvailableDegreeOfParallelism="8"
  • 慢的：EstimatedAvailableDegreeOfParallelism="4"

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