Post reply

TOP operator in UPDATE execution plan

  • September 13, 2016 at 4:03 am

    #330116

    I'm looking into a deadlock issue with two UPDATE statements hitting a heap and whilst looking at the execution plans I've noticed that there's a TOP operator present. The UPDATE statement is fairly simple: -

    DECLARE Variable1 datetime,Variable2 varchar(9),Variable3 uniqueidentifier

    UPDATE Object1 SET Column1 = Variable1, Column2 = Variable2 WHERE Column3 = Variable3

    Column3 is the nonclustered PK on the table (it's a GUID) and there's a covering index present which the query seeks against.

    Here's the plan (using Brent Ozar's handy new tool): - https://www.brentozar.com/pastetheplan/?id=HyWKprB3

    Does anyone know why the TOP operator would be there?

    Thanks

  • September 13, 2016 at 4:14 am

    #1900493

    Can you not just attach the .sqlplan file?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 13, 2016 at 4:19 am

    #1900495

    Yes, no problem. sqlplan file attached.

    Thank you.

  • September 13, 2016 at 4:27 am

    #1900496

    Do you have an Actual plan please?

    I suspect that the top's not removing any rows, that it's something put into update plans for cases where it's a joined update and there are more than one row coming from the FROM portion for each row to be updated.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 13, 2016 at 4:33 am

    #1900497

    I've only got an estimate plan at the moment as I'm pulling the statements out of the deadlock xml.

    I'll see if I can grab an actual now.

  • September 13, 2016 at 4:53 am

    #1900500

    Actual plan attached.

    Looking at the details for the TOP and SEEK operators, only one row is found so you're right that the TOP isn't removing any rows but I can't see any information that would shed light on why the operator is there.

  • September 13, 2016 at 9:46 am

    #1900589

    My guess would be that at least one of the indexes being affected by the UPDATE is defined as UNIQUE.

    I seem to recall that TOP operators show up in plans when a UNIQUE index is being maintained.

    I'll run some tests to confirm in a bit.

    Cheers!

  • September 13, 2016 at 9:57 am

    #1900597

    Hi Jacob,

    Yep, I think you're right. The table's a heap and has a unique nonclustered PK which is being referenced in the WHERE clause of the UPDATE statement.

    I disabled the unique index and the TOP operator is no longer in the plan (on a test system of course :-)).

    Thanks!

  • September 13, 2016 at 10:09 am

    #1900605

    Glad to help!

    Yeah, ran a few quick tests, and it seems that pattern happens when two conditions are met: you reference a unique index in the WHERE clause and it's being affected by the update.

    If only one of the conditions is met I didn't see the same pattern.

    Cheers!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply