Doug Chamberlin <chamberlin.doug@...> wrote:

>
> Two things come immediately to mind:
>
> 1) Everything gets read and written to the file system in blocks. So
> even if you just update one byte in one record at least one whole block
> is read and written. Of course, that is subject to caching, etc, but if
> the monitor you are watching is counting bytes you will see lots more
> moving around than you might have expected.
>

Correct. The minimal write block size is a page, so if you're using 16K
pages, changing someones age from 39 to 40 will cause a 16K write.

>
> 2) Everything takes place in a transaction and the server needs to track
> transactions so there is housekeeping done to record when transactions
> start and stop. This applies to simple transactions that just read data
> as well as those that change data. So consider an additional block or
> two being read and written for each transaction. (For example, there is
> a Transaction Inventory Page which is the most frequently read and
> written block in the whole database.)
>

Close, but not entirely right. READ_ONLY READ_COMMITTED transactions don't
get unique transaction identifiers and don't cause writes to the header and
Transaction Inventory Pages (tip). And although the last tip is pretty
busy, the busiest page in the database is the header page, which changes
once for every transaction that's not RO/RC and once for every new
connection.

>
> I'm sure Ann and others will add more to this.
>

Correct.

Good luck,

Ann


[Non-text portions of this message have been removed]