SQL Server using index scan instead of seek when updating a view that has an INSTEAD OF UPDATE trigger via a cursor

Question

I have a 3rd party application which uses a cursor that updates rows in my SQL Server 2014 table. I am unable to modify this code and have therefore devised a scheme to point the cursor to a view which has an INSTEAD OF UPDATE trigger where I can intercept and fully control the update. I am very aware that cursors are evil, but I am forced to work with them because I cannot modify the source programs.

When the UPDATE...WHERE CURRENT OF statement executes, it's performing a clustered index scan rather than a seek to locate the record that the cursor is currently pointing at. I cannot determine why the optimizer is doing a scan when it could be doing a seek. I believe that it's a combination of cursor + view + instead of update trigger that is causing the problem, because if I remove any one of these 3 variables from my tests, the index seek is used properly.

Note that it doesn't matter how many rows are in the table; the optimizer always uses a scan, according to the execution plan. I've even removed all of the logic within the trigger to further simplify the test.

Here's some simple code to recreate the issue:

CREATE TABLE [dbo].[Person](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](40) NULL,
  CONSTRAINT [PK_Person_Id] PRIMARY KEY CLUSTERED ( [Id] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
  ON [PRIMARY]
GO

CREATE VIEW [dbo].[vPerson]
AS SELECT Id, Name FROM dbo.Person
GO

CREATE TRIGGER [dbo].[InsteadOfUpdate_vPerson] ON [dbo].[vPerson]
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON
END
GO

INSERT INTO Person VALUES('John Doe');
GO

--This will use a seek
UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE Id = 1
GO

--This will not use a seek
DECLARE c CURSOR FOR
SELECT * FROM dbo.vPerson
WHERE Id = 1
FOR UPDATE

OPEN c
FETCH NEXT FROM c

UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE CURRENT OF c
CLOSE c
DEALLOCATE c
GO

Any help or suggestions on things to try would be greatly appreciated.

Answer 1

This looks like an oversight.

The optimizer cannot generate an apply-style indexed loops join when the update is performed using WHERE CURRENT OF and the target view (schema-bound or not) has a T-SQL instead-of update trigger, regardless of the number of rows, or any other considerations:

That shows an example with almost 20,000 rows in the table (copied from the AdventureWorks' Person table, as it happens).

The join predicate gets 'stuck' on the nested loops join operator itself, rather than being pushed into the inner side to produce a seek:

Since you cannot change the code, you should report this as a bug through your normal Microsoft Support channel. You may also report the bug on Connect, but the chances are much lower of getting a quick response or fix via that route.

---

Just for interest's sake, the plan you are after is possible using an API cursor positioned update (the most analogous operation internally):

DECLARE 
    @cur integer,
    @scrollopt integer = 2 | 8192 | 32768 | 131072, -- DYNAMIC | AUTO_FETCH | CHECK_ACCEPTED_TYPES | DYNAMIC_ACCEPTABLE
    @ccopt integer = 2 | 32768 | 131072, -- SCROLL_LOCKS | CHECK_ACCEPTED_OPTS | SCROLL_LOCKS_ACCEPTABLE
    @rowcount integer = 1;

-- Open the cursor
EXECUTE sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT * FROM dbo.vPerson WHERE Id = 1;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

-- Request a positioned update
EXECUTE sys.sp_cursor
    @cur,
    1, -- UPDATE
    1, -- row number in buffer
    'dbo.vPerson', -- table (unambiguous in this case)
    'Name=''Banana'''; -- new value

-- Close    
EXECUTE sys.sp_cursorclose -1;

The execution plan is:

Notice the index seek on Person (the predicate is not 'stuck'):

This is not a workaround for you since you cannot change the source query. There's no way to hint or plan guide your way around your issue; the optimizer simply cannot generate the seek plan you expect in your specific case. Attempting e.g. a FORCESEEK hint simply results in an error message saying the optimizer could not produce an execution plan.

Answer 2

I'm not sure if this will fit your requirements, but I did get a CLR INSTEAD OF trigger to generate a seek plan.

The full repro is here, but the basic CLR trigger is as follows (excuse the VB, feel free to convert to c#:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


Partial Public Class Triggers
    <Microsoft.SqlServer.Server.SqlTrigger(Name:="SqlTrigger1", Target:="dbo.Person", Event:="INSTEAD OF UPDATE")> _
    Public Shared Sub SqlTrigger1()

        SqlContext.Pipe.Send("Trigger FIRED")

        'Do some actual work against the table; need to make sure trigger does not fire again.
        Try
            Using conn As New SqlConnection("context connection=true")
                conn.Open()
                Dim sqlComm As New SqlCommand
                Dim sqlP As SqlPipe = SqlContext.Pipe()

                sqlComm.Connection = conn

                sqlComm.CommandText = "UPDATE p SET p.Name = i.name + 'z' FROM dbo.Person p INNER JOIN inserted i ON p.Id = i.Id"

                sqlComm.ExecuteNonQuery()

            End Using
        Catch ex As Exception
        End Try

    End Sub
End Class

The trigger intercepts the initial update from the cursor and tags on a letter z just for demonstration purposes. The trigger must be against the base table not a view so I don't know if that makes it difficult for you to use. I think the same principle applies though; intercept the main update, do something different with it.

Results from Plan Explorer:

Give it a try and let me know if it helps.