For a query that changes data, does the change get written to tlog prior to sending it to secondary (AG) and primary data file on disk?

Question

So there are 6 things involved when a transaction is made that writes data:

1. Tlog in memory
2. Data page in memory
3. Tlog on disk
4. Data page on disk
5. Send the tlog to secondary AG
6. Inform the client that transaction is committed

For a query that changes data, does the change get written to tlog (memory), then the tlog is hardened to disk, then the changes are made to data buffer (memory) and then client gets a committed response?

And then after a while the checkpoint will flush the data pages (memory) to data page on disk. Is that how it works?

In AG sync mode and async mode, when does primary send the tlog to secondary?

Answer 1

For a query that changes data, does the change get written to tlog (memory), then the tlog is hardened to disk, then the changes are made to data buffer (memory) and then client gets a committed response?

The high level synopsis is the log blocks are filled in memory from transactions. When required, the log blocks are flushed to disk, when the are attempting to be flushed to disk they are sent to any synchronous secondary replicas. The primary waits for all required replicas (depending on settings), including itself, to harden the log block and report back. Once all requirements have been met, any transactions waiting on that log block to be hardened will be free to move on and will generate a response to the client.

There are various other caches and items in play but don't generally get in the way of the above.

In AG sync mode and async mode, when does primary send the tlog to secondary?

Pedantically depends on various configurations, however the high level is that any synchronous commit replicas will be sent the log when the log block is attempted to be hardened (flushed to disk) on the primary.

Asynchronous secondary replicas have the log sent depending on various items, however in general when the log cache knows it has new data and/or progress messages sent by the secondary have been processed at the primary with new information, though depends on the performance abilities and current workload of both.

Answer 2

When a dirty page (i.e. one updated by a transaction) is written to disk is independent of when the tlog records for that transaction are written.

The buffer manager is free to evict a page whenever it needs, even one dirtied by one or more transactions.

To commit a transaction the system need not persist all the pages it dirtied. Ensuring the tlog records are persisted is sufficient.

Together these are known as a "steal /no force" policy. It has advantages over the alternatives and is widely adopted.

A database-wide checkpoint can occur at any time persisting all dirtied pages to disk. Those from committed transactions and those from open transactions both can be persisted.

Tlog records from all transactions are held in memory in a single buffer. When it is persisted the whole buffer is persisted, including records from open transactions. It is only after a transaction's commit record is acknowledged as having been persisted that the client can be informed.

This all is coreographed by the ARIES protocol. It dictates what metadata is captured, when, and the order in which processes occur. Is has more requirements than I have mentioned here.

As an aside it is possible that the target page of a write operation is never in memory during the life of a transaction. Consider an insert to a table with no constraints. There is no need to compare the new data to existing data to check for conflicts. So why read any pages into memory, even the one that will hold the new row? Persisting the log records and allowing lazy update of the data page would work. I don't believe SQL Server actually works this way but it's interesting to consider.