Sergiy (8/15/2012)
1. UPDATE actually writes to the pages, regardless if the new values are the same or different from the old values.But it writes to the pages in memory.
I disagree. If it did, the page would be marked dirty after the update. It is not as I showed with the DMV (I can prove the same thing with DBCC Page, it's just a lot harder)
2. CHECKPOINT compares binary contents of the pages in memory and writes to disk only those ones which have become different after completed transactions.
Checkpoint just writes dirty pages to disk. It doesn't check the pages and it's quite happy to write out pages dirtied by transactions that have not committed.
If checkpoint had to take the page in memory, read the matching page from disk, compare the two and only write out changed pages it would be massively less efficient than it is and would require a lot of extra memory.
3. DMV shows the outcome of CHECKPOINT, not UPDATE, therefore the pages updated in memory don't appear there.
The DMV is buffer descriptors (ie buffer pool), it's a dmv that shows pages in memory, it does not have anything to do with checkpoint, it does not look at the pages on disk.
Per BoL:
Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.
and
is_modified bit 1 = Page has been modified after it was read from the disk.
As I said earlier, the update runs, it marks all rows as updated in the rows affected (even in cases it didn't change the values), those rows will appear in the trigger pseudo-tables. Locks will have to be taken, execution plan will have to run the update, SQL doesn't know the rows are the same until the update actually runs. There is simply an optimisation that does not log updates where the value has not changed and does not mark the pages as dirty until a value on it is actually changed.
Your odd examples, no rows qualify for the update at all so there's no transaction to begin or commit and no rows to take locks on. Hence SQL can optimise away the update even earlier and not even begin a transaction.
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