Hello Ann and all Thank you so much for this interesting information. I have tried to set the transaction level within an SQL statement and a stored procedure.
I just get Dynamic SQL error when I run this. What am I doing wrong. SET TRANSACTION READ ONLY; select * from "Booking" --- In [email protected], Ann Harrison <aharrison@...> wrote: > > 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] >
