UPDATE slow when setting column to NULL

richinator38 2013-03-22 15:54:56

I have a SQL Server 2008 table with 80,000 rows and am executing the following query:

UPDATE dbo.TableName WITH (ROWLOCK)
SET HelloWorldID = NULL
WHERE HelloWorldID = @helloWorldID

HelloWorldID is an int and the @helloWorldID parameter is also int.

The query is taking too long and I'd like to optimize it. I created a nonclustered index on HelloWorldID but it didn't matter. I may have to redesign this…maybe put the HelloWorldID on another table that links it to the TableName table?

Related to StackOverflow question here: http://stackoverflow.com/questions/15573411/update-slow-when-setting-column-to-null

Aaron Bertrand 2013-03-22 16:18:09
While it's only an estimated plan, and doesn't show important runtime variables (such as duration), I do not see anything in the plan that would lead me to believe this should take a long time. Pretty standard single-row update (to a CI and an additional NCI). My theory is that this is part of a larger, long-running transaction and is potentially getting blamed for the slowness of another part of the transaction. 🙂

Are there other variables we don't know about? Is this part of a larger transaction? Are there triggers, cascade, CDC/Change Tracking, Resource Governor, anything else going on that we wouldn't know about?

On StackOverflow you mentioned that the wait type in sys.dm_exec_requests was DELETE. This implies to me that there is something else going on aside from just this query.

Aaron Bertrand 2013-03-22 19:34:16
Turns out there was a nested stored procedure call that did have a slow DELETE trigger.
Jonathan Kehayias 2013-03-22 16:28:50
My guess would be that you have Foreign Key constraints that have to be checked as a result of doing this and there isn't a supporting index on the other side of the constraint to support the CHECK operation so it has to do a full INDEX SCAN of the table referencing this one or that is being referenced.

That or you have CASCADE enabled and it is having a similar issue but affecting a lot more rows downstream to prevent orphaned records.

richinator38 2013-03-22 19:42:30
Thanks for your help!