September 7, 2012 at 3:04 pm
(Starting a new thread to keep the noise down...)
Ok, to clarify my understanding after skimming the 100+posts:
CREATE TABLE t1 (i int)
INSERT INTO t1 (i) values (1)
INSERT INTO t1 (i) values (1)
INSERT INTO t1 (i) values (1)
INSERT INTO t1 (i) values (1)
UPDATE t1 SET i = 1 WHERE i = 1
UPDATE t1 SET i = 1 WHERE i = 1 OPTION (RECOMPILE)
I'm seeing 2 different plans, the first having 2 parameters @1 and @2, so the plan can be reused.
@2 is being used as a predicate for the "Table Scan", and @1 is being used for the "Table Update" operator (what the value gets set to)
In the second, I'm seeing a constant of 1 for the table scan predicate, and a defined expression, which is used for the "Table Update".
The plans don't change if it's a non-updating update or an updating update, so therefore from the query engine's perspective, it identified the rows to update and updated them, being totally ignorant that it was doing no real work.
However from the storage engine's perspective:
1. "Table Update" is called a row at a time, being passed in a RID, and the new row value.
2. It takes a latch (PAGELATCH_EX) on the page target to prevent others from accessing it.
3a. It reads the existing value from the page, and compares to the new value, if different, writes new value to the page in memory, writes log, recalculates the page checksum and marks the page dirty.
3b. -Or- It blindly writes the new value to the page in memory, writes log, recalculates the page checksum and marks the page dirty.
3c. -Or- It blindly writes the new value to the page in memory, recalculates the page checksum and if changed, writes log and marks the page dirty.
4. releases the LATCH
5. repeat for the next row.
Between 3a, 3b and 3c, I imagine that 3a or 3c happens for small in-row value types as an optimization. 3b might happen for LOB or row overflow columns, where it would be less expensive to just write the new value.
Would switching between CHECKSUM, TORN_PAGE or NONE affect the behavior of non-updating updates? specifically, does turning on CHECKSUM cause non-updating updates to turn into updating updates?
Would SQL 2012's (forget what it's actually called) delayed schema modifications affect this behavior?
September 7, 2012 at 3:29 pm
Not in a place to test it right now, but does CDC in 2012 capture an update that doesn't change the updated column's value?
September 8, 2012 at 4:29 am
If the binary value does not change, the page is not marked dirty, the log record for the update is not written.
Page verify has no effect.
Page checksums are not calculated when the page is modified in memory. They are calculated only when a dirty page is written to disk.
You'll forgive me if I don't want to go over all this yet again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply