January 14, 2010 at 9:37 am
Hi there,
I was discussing with a collegue the different concepts of database-systems to write updates/insert into the database when Recovery-Mode is Full on sql server 2008.
My Opinion has been the following:
- You make an Update on a Table -> Update is written into logfile. The Database writes the actual change physically from the logfile when the logfile is backed up or shrinked.
- Select on the table means then -> Reading the physical table + reading the logfile for changes
My collegues opinion:
- You make an Update on a Table -> Update is written into logfile + into (ram-)buffer of the database like in sql server 2000. The Database writes the actual change from the buffer physically recurringly (e.g. checkpoints) or when buffer is required from new changes.
- Select on the table means then -> Reading the physical table + reading the buffer for changes
Can you enlighten us, which is the accurate way?
my, his or a third one?? 🙂
Cheers
Mitch
January 14, 2010 at 10:11 am
You colleague is closer to correct, but he's still wrong in one place.
Regardless of recovery model, SQL follows this method for reading/writing. (this has not changed fundamentally since SQL 2000)
Update/insert/delete. Changes are made to the pages in memory. At the point that the transaction is complete, the record of the changes will be written into the log file, on disk. The changed data pages are written to disk at a later point by one of two operations - checkpoint or lazy writer.
Select. Reads the data from the pages in memory. If the page required is not in memory, request the storage engine to fetch it, then, once it is in memory, read it.
The query processor never reads or writes directly to disk. It's always operating on the data pages in memory, in the buffer pool. It's the storage engine that's responsible for getting pages into memory if they're needed but not there.
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply