Why would s simple UPDATE statement would take 14 minutes to complete?

Martin Surasky 2017-04-03 17:20:30

I have a very, extremely, super, extra dumb, UPDATE statement in a script that goes like this

UPDATE [some_table]
SET [some_column] = 1

… and it is taking almost 14 minutes to complete!

Granted, the box where I'm running this statement is my local PC, not a high end performing server, and also the table has about 2.8 million rows. But still, this is way too much.

I tried to create a dummy table with also about 2.8 millions of rows in the same box, but in that test, the UPDATE takes about a few seconds. What can be special in this particular table that makes it so freaking slow?

Investigating the actual query plan I do see that the query reports using about 63GB of data (Actual Data Size) so I suspect this can be the result of page splits, but looking into the Clustered Index structure with dm_db_index_physical_stats, I see no evidence of the index being too full to not being able to acomodate the change (this is just setting a 1 in a BIT column for the love of G.d!)

I'm a bit lost here, there must be a very simple explanation to all of that, I just can't find it!

Martin Surasky 2017-04-04 15:38:52
I did not find totally conclusive evidence of the solution to this problem, but I did to improve performance and make this update 5 times faster (more or less). In my case this was related to index-fragmentation.

Since the details are kind of long, I decided to write a small blog post about it, if you are curious please take a peek.