Scan on trigger Seek on SSMS

Question

I'm having a performance issue with a trigger when insert/delete on a table, it scan an index associated to a view, but if I execute the update manually it generate a seek.

I updated statistics for the view and SubscriberXList table but it keeps doing the scan. Also recompile ... restart the engine (it's a test environment) ... pray to God :p

Tables:

Subscriber

SubscriberXList (N to N relationship between Subscribers and List)

SubscribersAmountByList (indexed view)

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);

The query that generate the scan belongs to a trigger on SubscriberXList insert and delete. At the end of the trigger has an update that generate a scan when need to update a view related to SubscriberXList and 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

Execution plan when trigger is fired https://www.brentozar.com/pastetheplan/?id=r1mEYGNEB

Execution plan for manually execution of the update https://www.brentozar.com/pastetheplan/?id=B11hKM4EB

This happen when calculate the new value for the indexed view.

Also on the execution plan it recommends a new index

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

This index it is a bad index because the SubscriberXList table has the 80% rows with Active = 1 and it's one of the biggest tables on my DB.

I tried with a dynamic query setting the amount of rows to be updated on "Option (fast '+convert(varchar,@AmountSubscribersToUpdate)+')" but keep generating the scan.

What lead to change the execution plan between trigger and manually on ssms??

Thanks!

score 4 · Accepted Answer · answered Aug 16 '19 at 15:23

This isn't really an "apples to apples" comparison. The trigger version of the query looks like this:

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

And the manual version of the query looks like this:

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

In the trigger version, the join order is being forced, and the written order of the tables has been reversed from the manual update. Since the fast plan doesn't have the FORCE ORDER hint, I would suggest removing that, and reversing the table names in the trigger to match your manual update query.

You may also benefit from putting OPTION (RECOMPILE) on the UPDATE statement, so that SQL Server has an accurate picture of how many rows are in the table variable.

For completeness, there are some other differences in the execution plans:

Fast plan was on a much new version of SQL Server 2014.
- Fast: SP3 CU2
- Slow: SP1+GDR
Fast plan had more memory available, which affects the optimizer's plan choices
- Fast: EstimatedAvailableMemoryGrant="583270" EstimatedPagesCached="583270"
- Slow: EstimatedAvailableMemoryGrant="307200" EstimatedPagesCached="153600"
Fast plan was on a server with more cores available for parallelism
- Fast: EstimatedAvailableDegreeOfParallelism="8"
- Slow: EstimatedAvailableDegreeOfParallelism="4"

Scan on trigger Seek on SSMS

1 Answers1