CODEWITHSUNDEEP
sql-server
XabiZitu
XabiZitu

Reputation: 33

Simple SQL Server Update query taking over 15 mins

I have a SQL Server database table which contains 5 columns and 3 rows. Schema description:

Although cIdFoot and cwId are both foreign keys since they are nearly empty tables in this table those are currently not created as foreign keys.

My update is this: 

UPDATE myTable 
SET desc1 = '2018', desc2 = '2018'
WHERE (cId = 1)

This simple SQL script takes over 6 mins and the table just has 3 rows.

Doing selects seems ok but when I try to update it takes forever and it usually times out. I have seen that in the log -> .ldf file does not seem to get any bigger, could it be that it has been corrupted or that it was not created well. Strangely bigger tables with over 10000 rows work correctly when updating or inserting.

Any help would be grateful.

The execution plan: The Execution plan

enter image description here

Upvotes: 1

Views: 203

Answers (1)

S3S
S3S

Reputation: 25112

Based on your comments, SPID 85 is blocking your update query. Your update will not finish until what ever this process is doing completes or releases the lock it has, which your update needs.

If you kill SPID 85, the update will run, but you need to figure out what this process is doing and why it's holding a lock on that table. It's not likely going to stop obtaining a lock on this table (and thus blocking your update) until you fix it.

Upvotes: 3

Related Questions