SQL Server databases - checkpoint, cache and I/O request - what you need to know?
Have you ever wondered about flushing databases?
Well not quite possible to flush it straight away and not a best practice too on the live server, so what it is about and you need to know about transactions state when they occur. Say when the changes occur in a database the changes are formed as transactions that are held in buffer pool until the transaction commits or aborts. If it goes for abort operation then obviously those changes are discarded and if not for commit process, its changes become visible to other users and transactions, but they might not be immediately written to the database. So in this case due to any issue on the server or database then even the transactions that have committed but whose changes have not been written to the database will be discarded, so this is where you need to consider having a thorough backup strategy for transaction log and WRITELOG operations.
Say to the extent of transactions that are written to database, in order. But still there may be some issues that can cause inconsistency on the database adn for instance which an application has committed transaction A, and then transaction B. If the application crashes or the device is reset, the database will be in one of three states, unchanged or changed by transaction from A or changed by both A and B! So always better to leave it to SQL Server to committ and write the order they are committed improves performance by reducing the number of times the database file must be written. Improved performance is particularly noticeable when there are many small transactions that have committed in a short time. In this case, all the transactions are written to the database file at the same time instead of each transaction causing a separate database write operation.
So more to the subject I have found this interesting blog post from PSS SQL Server Engineer's blog - CHeckpoint-flush-I/O by Bob Dorr, worth reading it.
