The SQL Server database engine processes a client request as outlined in the following steps:
A. The SQL language parser receives a standard SQL statement. It resolves, compiles, and optimizes the query, and stores the final query plan in the procedure cache for use. When the query execution is complete, the query plan is immediately freed from the procedure cache.
B. The stored procedure handler receives a request to run a stored procedure. It checks to see if a compiled version of the stored procedure is in the procedure cache and available for use. If so, it uses the compiled version already in the procedure cache. If the stored procedure is not in the procedure cache, the stored procedure is in the procedure cache but is currently being used by another connection, or the stored procedure must be recompiled for some reason (for example, if the stored procedure is created with or executed with the WITH RECOMPILE option), then the resolved version of the stored procedure is loaded from the sysprocedures table, compiled, and stored in the procedure cache for use. The page cache is used for reading from sysprocedures, like all table reads.
C. The sysprocedures table contains a resolved version of a stored procedure called a query tree. This intermediate form has already been parsed, but it has not been compiled or optimized. Views and other database objects are also stored in sysprocedures.
D. The procedure cache contains a compiled version of a query or stored procedure called a query plan. This final form has been optimized and contains complete details for executing the query, including join plans and index usage.
E. To process a SELECT query plan, data and index pages must be read from the database. SQL Server checks to see if the necessary pages are in the page cache. Any pages already in the page cache are used. Any pages not in the page cache are read from the appropriate database device(s) into the page cache for use.
F. To process an UPDATE, a DELETE, or an INSERT query plan, data, index and log pages must be read from and written to the database. Just as in the SELECT case, SQL Server checks to see if the necessary pages are in the page cache. Any pages already in the page cache are used. Any pages not in the page cache are read from the database device(s) into the page cache for use. Any pages in the page cache that are changed are marked as "dirty." This includes changed log pages used to store transaction log records or data and index pages changed to reflect actual data changes. Any pages that are completely unchanged remain "clean."
G. When a transaction ends (COMMIT TRAN, ROLLBACK TRAN, or transaction abort) all dirty (changed) log pages are immediately written to disk to ensure recoverability. Note that data and index pages are not written to disk by this process.
H. Data, index, or log pages are written to disk at various times. In all cases, the appropriate log pages are always written to disk before the corresponding data or index pages. This ensures recoverability. For this reason, the transaction log is known as a "write-ahead" log. Data and index pages can be written to disk before a commit if necessary because the transaction log contains all the information necessary to roll the changes back if the transaction does not commit. Also, if a power failure (or similar circumstance) causes a shutdown with dirty pages in the page cache, the appropriate changes can be rolled forward using the transaction log.
I. Data and index pages are written to disk at various times. When the lazy writer determines that the number of "free" or unused pages in the page cache is below a certain value (the free buffers server-configuration value), it begins to "flush" or write blocks of pages to disk until enough free pages are available in the page cache. It does this to avoid checkpoints and single-page writes, both of which are relatively expensive. When a checkpoint occurs, all dirty pages (log, data, and index) are written to disk. When a page is needed because a new page must be read from disk but no free pages are available in the page cache, a single page is taken from the least-recently-used (LRU) list, flushed, and made free for use.
The following illustration shows the processing and page flows for SELECT and UPDATE.