1

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...

Estimated execution plan for update

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

Excution plan for count

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

Execution plan for select

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.

  • 2
    Check if the update statement isn't waiting for some locks to be released. Look for the wait type of this process. – BuahahaXD Mar 25 '16 at 11:34
  • any advice how to do that? (I'm quite new to SQL server) – Jeroen Mar 25 '16 at 13:31
  • select * from sys.dm_exec_requests and replace sesssinid with your update session id to see status of update statement.You can see here if your update is running only.if not check in select * from sys.sysprocesses – TheGameiswar Mar 25 '16 at 14:01
  • Check out sp_WhoIsActive. Run it while your queries are executing. sqlblog.com/files/folders/beta/entry42453.aspx – Erik Darling Mar 25 '16 at 21:11
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.