regarding Inserton of records in table RRS feed

  • Question

  • Hi,

    I have couple of questions as below

    1 ) I had read that when records are inserted into a table, first the records are inserted in buffer then in transaction log and then to the physical table.  I was wondering why is Transaction log as well as buffer kept in between..why cannot be the data directly written to the physical table.

    2) Also when i am fetching a records from the table then how will the flow be i.e. will the records be fetched directly from a table or through buffer or transaction log ..... if am not wrong then first the records are placed in buffer and then the same are fetched from that buffer....

    3) If in a transaction there is bulk insertion in a table say around 10 million records. And if someone from other terminal is reading the records from that table then which records will be read...the one from transaction log/buffer or from table.

    Thanks & Regards

    Saumik Vora
    Thursday, February 4, 2010 6:32 PM


All replies

  • Transaction log article: During the course of normal operations, SQL Server utilizes a transaction log to track all of the modifications performed within a database. This log ensures both that the database is able to recover when abruptly interrupted (such as a loss of power) and that users are able to undo (or "rollback" in database lingo) the results of a database transaction.

    Transaction logs are also used for disaster recovery: log-shipping from production to standby, point-in-time database restore and more.

    Writing to memory buffer is much faster than writing to disk. Same for reading. Therefore buffering data pages in memory yields better performance for read queries (SELECT) and write queries (INSERT/UPDATE).  If a data page (8K ) is in buffer, it is going to be read from buffer. If not, it is read from disk. If a data page is used frequently, it stays in memory buffer, otherwise it is removed.

    Related article from BOL 2008: Buffer Management

    Let us know if helpful.

    Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, February 4, 2010 8:31 PM
  • In other words, a query does not read from the transaction log.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, February 4, 2010 9:10 PM
  • Wow...quite a comprehensive answer... thank you...

    Well in the above connection i tried to insert 10 million records using While loop and immediately stopped SQL services. On checking SQL Server logs, i found that the SQL server itself has done rollforward and committed some 19000 records into the table. Well i tried this so that i can manually rollback the transactions. Can you please suggest me some excercie where i can have hands on experience in data backup and recovery.

    Sunday, February 7, 2010 3:44 AM
  •  Can you please suggest me some excercie where i can have hands on experience in data backup and recovery.

    Recovery exercises:

    1. User has accidentally updated table Alpha. Restore table as of yesterday night.

    2. User has accidentally updated table Alpha at 11:10am. Restore table as of 11:00am (point-in-time restore).

    3. Move database Delta files to a different drive

    Kalman Toth, SQL Server 2008 & BI Training, OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Sunday, February 7, 2010 8:02 AM
  • When I was studying for my first SQL exam, I was using CBT Nuggets (excellent tutorials there BTW) and after talking about back ups and restores, the instructor said "let's have some fun!" He inserted 1000 records, deactivated the sample DB and simply deleted the MDF file from the disk. I did a loud WTF??!

    It was a pretty shocking thing after all that studying and seeing the MDF file as the holy of holies. But in minutes he restored the back up, restored the tail log, and we were back in business. You might try that just to shock someone (on a TEST DB of course). See how fast you can get it back into production. :)
    Monday, February 8, 2010 4:14 PM