Post reply

transaction log

  • January 16, 2011 at 1:09 pm

    #233296

    how often transactions in the log file write to db file ?

    thank's

  • January 16, 2011 at 1:27 pm

    #1274006

    They don't.

    In Exchange log records are later written to the DB, that's not how SQL's log works though. When a change is made in SQL it is made to the data pages in memory and the log record is written to the log buffer. When the transaction commits (or before) the log record is written to the log file. At some point later the data page in memory is written to the data file.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 16, 2011 at 2:41 pm

    #1274010

    ? "At some point later .... ?" - when this is happen

    thanks

  • January 16, 2011 at 2:49 pm

    #1274014

    Literally at some point later. Either when a checkpoint runs or when the lazy writer runs and selects that page.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2011 at 1:34 am

    #1274121

    ok, how can I know the defult time that sql define for checkpoint ?

    I ask you this because when I run some Insert query into table, I sea the values Immeditly On the the DB ?

  • January 17, 2011 at 1:41 am

    #1274125

    kln2020 (1/17/2011)

    ok, how can I know the defult time that sql define for checkpoint ?

    I ask you this because when I run some Insert query into table, I sea the values Immeditly On the the DB ?

    If I'm not mistaken, that is the point.

    Transactions are meant to maintain ACID in the database. So if a transaction finishes, the result must be visible in the DB.

    But the changes are not immediatelly written to the data files. They are stored into a buffer and the checkpointing process marks which transactions are written to the data files. For more information, see this url: http://articles.techrepublic.com.com/5100-10878_11-5173108.html

    So, there is a difference between data in the database and data in the data file.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 17, 2011 at 1:56 am

    #1274132

    kln2020 (1/17/2011)

    ok, how can I know the defult time that sql define for checkpoint ?

    Depends on activity and some config settings.

    I ask you this because when I run some Insert query into table, I sea the values Immeditly On the the DB ?

    Yes you will. Always.

    How and when SQL trickles the data to the actual data file has absolutely no effect whatsoever on the results of your queries.

    When you query SQL it reads from pages in memory, not pages on disk. So if the page has changed in memory that's what you see when you run a query, not what's in the data file.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2011 at 2:50 am

    #1274149

    thank's all.

    so if I read a table that I not change, I read it from the buffer or from the data file ?

  • January 17, 2011 at 3:05 am

    #1274154

    kln2020 (1/17/2011)

    thank's all.

    so if I read a table that I not change, I read it from the buffer or from the data file ?

    EDIT: the answer I gave here was wrong. Please refer to the answers from GilaMonster for the correct explanation.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 17, 2011 at 3:06 am

    #1274155

    When you query SQL it reads from pages in memory, not pages on disk. Always.

    If the page is not in memory, it's fetched in by the storage engine, then the query processor reads it from memory.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2011 at 3:10 am

    #1274160

    GilaMonster (1/17/2011)

    When you query SQL it reads from pages in memory, not pages on disk. Always.

    If the page is not in memory, it's fetched in by the storage engine, then the query processor reads it from memory.

    Thanks for the explanation. What is the difference between pages in memory and query results in the cache?

    Is it actual physical location on your computer? For example:

    in memory --> RAM

    in cache --> L1 cache of the processor

    Or am I looking at this from an incorrect angle?

    Edit:

    I've done some research and apparently both the data buffer and the query cache reside in RAM:

    http://www.sqlteam.com/article/what-data-is-in-sql-server-memory

    http://www.sqlteam.com/article/what-query-plans-are-in-sql-server-memory

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 17, 2011 at 3:13 am

    #1274162

    Koen (da-zero) (1/17/2011)

    It depends. If someone else has made changes to the table and the transactions are not yet checkpointed in the log file, you'll probably read it from that internal buffer. If the transactions have been "committed" to the data file, you'll probably read it from the data file. If someone else has read the data with the same query, the data will probably come from the cache.

    It does not depend.

    The query processor reads and write to pages in the data cache always. The query processor does not know what a data file is, that's the storage engine's job. Data cache being part of the buffer pool, SQL's allocated paged memory.

    There's no 'internal buffer' for if someone else has made changes to a page. If a page is changed, it's changed in the data cache. When it's read for another query, it's read from the data cache, regardless of whether it's dirty (changed) or not. If the query processor needs a page and it's not in the data cache, the query processor requests the storage engine to fetch it into the data cache and the query processor will wait until that page arrives (PageIOLatch wait)

    p.s. this is a major simplification. There's lots I haven't gone into.

    For details check out some of the SQL IO whitepapers, Kalen Delaney's internals books, Ken Henderson's internals book and MSDN resources.

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

    http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx

    http://msdn.microsoft.com/en-us/library/ms187499%28v=SQL.100%29.aspx

    http://msdn.microsoft.com/en-us/library/aa337525%28v=SQL.100%29.aspx

    http://msdn.microsoft.com/en-us/library/ms191475%28v=SQL.100%29.aspx

    http://msdn.microsoft.com/en-us/library/aa337560%28v=SQL.100%29.aspx

    http://www.amazon.com/Microsoft-SQL-Server-2008-Internals/dp/0735626243

    http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=ntt_at_ep_dpt_3

    http://www.amazon.com/Gurus-Guide-Server-Architecture-Internals/dp/0201700476

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2011 at 3:19 am

    #1274166

    GilaMonster (1/17/2011)

    Koen (da-zero) (1/17/2011)

    It depends. If someone else has made changes to the table and the transactions are not yet checkpointed in the log file, you'll probably read it from that internal buffer. If the transactions have been "committed" to the data file, you'll probably read it from the data file. If someone else has read the data with the same query, the data will probably come from the cache.

    It does not depend.

    The query processor reads and write to pages in the data cache always. The query processor does not know what a data file is, that's the storage engine's job. Data cache being part of the buffer pool, SQL's allocated paged memory.

    There's no 'internal buffer' for if someone else has made changes to a page. If a page is changed, it's changed in the data cache. When it's read for another query, it's read from the data cache, regardless of whether it's dirty (changed) or not. If the query processor needs a page and it's not in the data cache, the query processor requests the storage engine to fetch it into the data cache and the query processor will wait until that page arrives (PageIOLatch wait)

    Thanks for the correction. I should've done more research :blush:

    I guess I was set on the wrong foot by this line:

    SQL Server keeps a buffer of all of the changes to data for performance reasons

    This comes from the following article:

    http://articles.techrepublic.com.com/5100-10878_11-5173108.html

    What is this buffer they speak of? Do they mean the data cache?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 17, 2011 at 3:33 am

    #1274174

    Koen (da-zero) (1/17/2011)

    This comes from the following article:

    http://articles.techrepublic.com.com/5100-10878_11-5173108.html

    What is this buffer they speak of? Do they mean the data cache?

    Considering there are some blatant inaccuracies in that article (shrink does NOT truncate logs), I wouldn't put much stock in it.

    The only 'buffer' SQL writes changed data pages to is the data cache, part of the buffer pool.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2011 at 3:37 am

    #1274176

    GilaMonster (1/17/2011)

    Koen (da-zero) (1/17/2011)

    This comes from the following article:

    http://articles.techrepublic.com.com/5100-10878_11-5173108.html

    What is this buffer they speak of? Do they mean the data cache?

    Considering there are some blatant inaccuracies in that article (shrink does NOT truncate logs), I wouldn't put much stock in it.

    The only 'buffer' SQL writes changed data pages to is the data cache, part of the buffer pool.

    Allright, thanks for the justification.

    (I should really select out the articles that I read more carefully. This is already the second time you pointed out that the article I've read contains errors)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply