Simple update takes forever On SQL Server I want to update a 0/1 interger value for a specific set of records. For some reason this takes very long (let it run for 2 hours, still not finished). I can't figure out what is wrong or what to tweak.

The query: 

 update workorder set INCTASKSINSCHED=0 where siteid = 'RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;


There are 282320 workorder records, only 103565 are for the RAILINFRA site. So it should be very, very fast.

In SQL Management studio I have this as an estimated execution plan. You can see a huge cost at the workorder_ndx1 index. I'm not sure why that is...



I tried to count the number of records, this works very fast:

SELECT COUNT(*) FROM  workorder WHERE   siteid ='RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;


1 row: 5430
Execution time 0sec



The select statement took just under 1 sec, and has a different execution plan:

SELECT * FROM  workorder WHERE   siteid ='RAILINFRA' AND HISTORYFLAG=0 AND ISTASK=0;


Gives the 5430 rows



This is the information of the indices used:

workorder_ndx1 (clustered)


SITEID
WONUM


workorder_ndx24 (non-clustered)


HISTORYFLAG
ISTASK
WONUM


There is only one index with the to be updated field INCTASKINSCHED

workorder_ndx38 (non-clustered)


WONUM
INCTASKINSCHED
SITEID


I do have the XML's of the executionplans (slqplan-files), let me know if you need more information from them.

I suspect there is somekind of a loop that it uses an index that needs updating, but not sure why that should be the workorder_ndx1 index, because it doesn't include the INCTASKINSCHED field.
It turns out there was a trigger on the samen table that also updates it, that forces an update for the index used. It works near instant with the trigger disabled.