Database engine

               SQL Server database engine is the core service for processing data. It has two major components: the storage engine and the query processor. The storage engine writes data to and retrieves data from stable media (e.g., disks). The query processor accepts, parses, and executes SQL commands.

               When a user submits a SELECT statement, he or she is asking the query processor to retrieve data that resides in data files on the disk. In addition to the data, the data files contain indexes and other objects. The indexes are copied to a server's RAM. This memory has several names, including buffer cache, buffer pool, and bpool. If the indexes are already in the buffer cache, the database engine doesn't need to copy the indexes from the disk again.

               When users submits an INSERT, UPDATE, or DELETE statement to modify data, the transaction log gets involved because data modification statements are always enclosed in a transaction, even if they're not coded as such. The transaction log supports the A in ACID. That is, it helps transactions achieve atomicity, and Atomicity simply refers to the idea that a transaction needs to be processed completely or not at all. In other words, if a transaction fails at any point in the process, the entire transaction must be rolled back.

               When a user submits a data modification statement, SQL Server reads the data files affected by the modification into the buffer cache, updates the values in the buffer cache, and then records the modification in the transaction log. Data files in the buffer cache that have been modified but the modifications haven't yet been written to disk are called dirty data pages. Dirty data pages are later written, or flushed, to disk by an asynchronous write process. There are three types of asynchronous write processes:

• Eager writing process. This process writes dirty data pages associated with nonlogged (e.g., BULK INSERT) operations to disk.
• Lazy writing process. This process wakes up periodically to write dirty data pages to disk and remove infrequently used pages from the buffer cache.
• Checkpoint process. The checkpoint process writes dirty data pages to disk, including those pages whose transactions haven't been committed yet. Its primary purpose is to keep the number of dirty data pages to a minimum.