UPDATE (April 14 2016):
From SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default on the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support:
Indirect checkpoint is the recommended configuration, especially on systems with large memory footprints and default for databases created in SQL Server 2016.
Does indirect checkpoint takes into considerations number of dirty buffers instead of number of transaction which usually automatic checkpoint was using?
Once we have set the target_recovery_time, SQL Server internally calculates the Target Dirty Buffer threshold. As the transactions are logged in the transaction log, a Dirty Page List keeps track of the LSNs and dirty buffers which are modified by the transaction. So for indirect checkpoints, the dirty buffers from each transaction along with the LSN are being tracked.
The Recovery Writer (a new background process in 2012) periodically polls the Dirty Page List and if it finds a number of Dirty Pages in the Buffer Pool greater than the Target Dirty Buffer threshold it flushes the Dirty Buffers and moves the minLSN forward.
Does indirect checkpoint setting is more suitable for Data Warehouse vs OLTP type workload?
From BOL, an online transactional workload on a database that is configured for indirect checkpoints could experience performance degradation. This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.
What scenarios you consider for SQL database in question before you start leveraging indirect checkpoint?
This depends on many factors as each environment is different interms of hardware, memory, transactions occuring, etc . I would recommend you to TEST and double TEST this particuliar feature as it can be helpful to you or else can cause detrimental effect on your workload and possibly jeopardise your databases as well. By default, target Recovery time is set to ZERO meaning, change it if you have tested it thorougly and its behaviour is acceptable to you ...
Advantages :
It may improve database recovery time
It may reduce checkpoint I/O as it writes continuously pages to the disk in the background
Disadvantages :
In OLTP workload it can increase overall writes on server by writing continuously pages to the disk in the background which may reduce the performance.
Ref:
Setting indirect checkpoints by database in SQL Server 2012
How do checkpoints work and what gets logged ?
Unfortunately there are a number of errors in the answers so far with regard to how COMMIT works, so I'll add another one. See How It Works: Bob Dorr's SQL Server I/O Presentation for details and SQL Server 2000 I/O Basics. Here is how it works:
All fully logged data writes (changes) occur in the exactly following sequence (see Understanding How SQL Server executes a Query: Writing Data):
- The data page is latched exclusively
- A log record describing the change is added to to log, in memory. New log record generates a new LSN, see What is an LSN: Log Sequence Number.
- The data page is modified (both data record and last_update_lsn on the page). This is now modified ('dirty') page.
- The data page latch is released
- nothing gets written to disk directly as the result of the update
A COMMIT does the following
- adds a new log record describing the COMMIT to the log, in memory
- all log records not flushed to disk, up to and including the one generated above, are flushed (written to disk)
- thread blocks waits until the OS reports the above write as durable (IO completes)
- COMMIT statement (or DML statement with implicit commit) completes
A CHECKPOINT does the following (simplified), see How do checkpoints work and what gets logged:
- All dirty pages in memory are written to disk
- For each dirty page, before starting to write to disk, the log up to and including the LSN that is the last_update_lsn on that page is flushed (written to disk). Note that flushin any LSN implies all previous LSNs are also flushed, so for the most dirty pages this is a no-op since it's own last_update is likely already flushed.
- log record describing the checkpoint is written to the log and flushed
- the database boot page is update with the LSN of the record generated above
Writes work differently for minimally logged operations, see Operations That Can Be Minimally Logged. Roughly the minimally logged operations act as following (simplified):
- Before inserting rows in a page as part of a minimally logged operation, a log record describing the fact that the page participates in minimally logged operations is being generated and appended to the log (in memory)
- The minimally logged page is being updated, as many inserts are being written on it is it fit. Nothing is logged, nothign is written to disk.
- When a minimally logged operation commits, before it commit it is required that all pages that participated in minimally logged operations in that transaction are being written to disk. Onyl after this writes completes, the COMMIT log record can be appended to the log (in memory) and and the log, up to and including this newly added commit log record, is flushed (written) to disk.
Best Answer
To answer your specific questions about this scenario:
No.
It will read the new, committed value regardless of the
CHECKPOINT.No, issuing a manual
CHECKPOINTis not necessary.When a query needs to read a particular data page, it first tries to read that page from the buffer pool (RAM). This is called a "logical read."
If that page is not in the buffer pool, it will be read from disk, placed into the buffer pool, and then that in-memory value will be used by the query. This is called a "physical read."
The point being that rows / data pages that have been updated in RAM (so-called "dirty pages") can be read from memory by multiple queries, and by multiple connections, without first needing to be written to the data file on disk.
In fact, a value in the buffer pool can be updated multiple times before being written to disk.
The
CHECKPOINTprocess is not really related to isolation levels, locking, or correctness of query results. It's mainly a performance optimization (to prevent lots of small writes from happening all the time) and a recoverability feature (to reduce the time it takes to recover the database to a consistent state from the log).You can read a lot of in-depth information about these topics in the following Microsoft Docs articles: