Why would s simple UPDATE statement would take 14 minutes to complete?
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!
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.