UPDATE slow when setting column to NULL
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
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.
That or you have CASCADE enabled and it is having a similar issue but affecting a lot more rows downstream to prevent orphaned records.