23 October 2006 08:00
tonyrogerson
UPDATE, columns specified and effect on whats logged
Did you know SQL Server is clever enough not to log stuff it doesn't really need to.
So, this statement ->
UPDATE mytest
SET smallcol = smallcol + 1
, somedata = somedata
WHERE id = @i
Is the same as writing this ->
UPDATE mytest
SET smallcol = smallcol + 1
WHERE id = @i
Also, it's not the whole 8KB page that gets logged, it's just the data that's changed, so in this case the column smallcol; the above statements actually cause 512bytes write activity to the log.
Ok, referencing back to my commission server article increasing the number of rows in the transaction improves performance dramatically in this case; the single row, single integer column update requires a log write of 512 bytes per row, so for 100,000 rows its 48MBytes; however, changing the transaction so that 10000 rows are updated per transaction (still 10,000 update statements per commit) we get 11MBytes written, what a difference!
Finally, doing the UPDATE in one go as in below gives our best performance (doh, no suprises there!) just 9MBytes written to the log in 168 IO's.
UPDATE mytest
SET smallcol = smallcol + 1
I suppose this is more directly applicable to anybody doing batch job cursor updates than anything else.
I guess the moral of the story is this: when writing your update or population routines always go for the option that creates the least IO's; that means benchmarking using the statistics from ::fn_virtualfilestats (NumberWrites and NumberReads).
Filed under: SQL Server