It’s good to know how a MS SQL Server query is processed by SQL Server database engine.There are six steps by that a query is executed.It’ll help to get idea on few matters

  1. Dirty Pages
  2. Read SQL Server Query execution Plan
  3. Isolation and Locking

Following steps get idea of query execution internals :

Step 1 : Application send modification request

Step 2 :

SQL Server find out the location of the pages related to modified data. In a first preference it search to the buffer and after that disk.

Step 3 :

Identified pages are kept into the buffer cache. This pages are called clean pages because no change has made to those yet now.

Step 4 :

SQL Server lock those pages and execute required modification. Now changed perform to page those are called dirty pages.

Execution Plan

Step 5 :

Now the details log records has generated and stored into the log file (in disk) from buffer cache. It ensures that if any issues happen and the server suddenly shuts down, during database recovery, it reads the transaction log file and prepares the recovery process (UNDO, REDO).It also sends commit acknowledgment to the user. The changed page is still in the buffer cache.Using DMV sys.dm_os_buffer_descriptors to check the dirty pages in memory and use the column is_modified to see the dirty pages:

SELECT db_name(database_id) AS ‘Database’,count(page_id) AS

‘Dirty Pages’  FROM sys.dm_os_buffer_descriptors

WHERE is_modified =1

GROUP BY db_name(database_id)

ORDER BY count(page_id) DESC

Execution Plan02

Step 6 :

A Checkpoint process writes all dirty pages (available in the buffer cache) and transaction log records to the disk. It also logs checkpoint information in the transaction log. It performs the following tasks as shown in the following image.

  • It writes the log records from the buffer cache to the disk ( transaction log file)
  • It writes all dirty pages ( modified pages since the last checkpoint) to the data file ( MDF/NDF)