Update May 20 – make sure to read to the end for an update.
Okay, look, it’s a mouthful of a blog post title, and there are only gonna be maybe six of us in the world who get excited enough to check this kind of thing, but if you’re in that intimate group, then the title’s already got you interested in the demo. (Shout out to Riddhi P. for asking this cool question in class.)
The system table sys.sysindexes has a rowmodctr that tells you how many times rows have been updated in an object. This comes in handy when we need to figure out if it’s time to update the statistics on a table.
To set the stage, let’s create a table, put a couple of indexes on it, and check rowmodctr to see how many changes have been made:
No rows have been modified yet since no data has been loaded. Let’s load 1,000 rows of data, and then check rowmodctr to see how many modifications have been made to the table:
Rowmodctr is 1,000 because 1,000 rows have been modified in the table – hey, inserts count as modifications.
Now the fun starts: let’s update everybody’s PersonName to be the same thing:
Remember, we had rowmodctr 1,000 for both indexes just a second ago – and now the index has gone up (saying it was modified), and the clustered index stayed the same (indicating that it wasn’t.)
I’m gonna be honest with you, dear reader: this was not the result I expected.
As a naive, delicate flower, I expected SQL Server to treat the rowmodctr the same way on both the clustered and nonclustered indexes. Instead, here we’re showing zero new modifications on the clustered index, but 1,000 new modifications on the name index.
In the big scheme of things, this isn’t a showstopper problem. It’s not like I’m over here waving my hands going, “OMG, this violates the laws of space and time!” It’s a system table, and if anybody’s relying on this thing for razor-sharp accuracy, they probably deserve what’s coming to them. I just find it so amusing that it’s handled differently on the two kinds of indexes – even though neither of them were modified. Huh.
How about if we explicitly tell SQL Server to set it to the same value:
Again, the nonclustered index shows modifications, and the clustered index doesn’t.
So what’s the takeaway?
I don’t think this is a performance-shattering problem: it’s probably fairly rare that you find an application that runs updates even when no changes were made.
To be clear, the fix isn’t to switch to a delete-the-old-row-and-insert-a-new-row design pattern: that would be twice as bad, since now you’re doubling the number of rows modified with each operation.
People who bought this post also bought “The Impact of Non-Updating Updates” by Paul White and the excellent answers to this DBA.StackExchange question about updates.
Update May 20 – hoooweee, boy are my skills dated on rowmodctr. Since SQL Server 2005, Books Online points out that rowmodctr isn’t a counter for the number of modified rows, as the name might imply. Instead, it’s about the number of updates that would have affected the statistics on the index involved. Since the clustered index here is just about the Id column, and the updates weren’t on the Id, the rowmodctr doesn’t increment there. Doh! Shout out to the folks who DMd me on Twittter and left comments here. Good catch!