transaction log

Question

transaction log

Aviad C.

SSChasing Mays

Points: 602
More actions
January 16, 2011 at 1:09 pm

#233296

how often transactions in the log file write to db file ?
thank's

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Aviad C. SSChasing Mays Points: 602 More actions · Answer 2

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

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Aviad C. SSChasing Mays Points: 602 More actions · Answer 4

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 ?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

Aviad C. SSChasing Mays Points: 602 More actions · Answer 7

thank's all.

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 12

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 14

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