How SQL Server writes data to disk

The transaction log is a serial record of all modifications that have occurred in the database. SQL Server writes changes to the log before it writes changes to the actual data file.

SQL Server Arch Diagram

SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

 At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

When users change data, SQL Server doesn't write that change directly to the data. Rather, SQL Server locates the appropriate data and then loads it into a special area of RAM called the data cache. Changes are made in RAM. Then, SQL Server copies changes waiting in RAM to the transaction log. Only then does SQL Server write changes to the actual data file.

This is called a write-ahead log because SQL Server writes changes to the log before it writes changes to the actual data file.

SQL Server Architecture:

1.Physical database architecture:

2.Relational Database Engine Architecture

1.Physical database architecture Contains:

•Page & Extents

•Physical Database Files & File Groups

•Space Allocation & Reuse

•Table & Index Architecture

•Transaction Log Architecture

Physical Database Architecture
Page & Extents

•Page fundamental unit of storage.

•Page size is 8 KB (128 Pages per MB).

•96 byes for Page Header,8060 for Actual Data & Offset &  rest 36 for SLOT ARRAY.

•For VARCHAR, NVARCHAR, VARBINARY or SQL_VARIANT data types when total row size of all fixed & variable columns in a table exceeds 8060 bytes (Insert or Update) , SQL server dynamically move one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit. 

•When a column is moved to ROW_OVERFLOW_DATA allocation unit, a 24 byte pointer on the Original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operations reduce the row size, SQL Server dynamically move the column back to original data page.

Types of Pages:

EXtents:

•Extents, basic unit in which space is managed.

•Extent is of 8 Physical pages (8 KB page x 8 pages=64 KB). Total 16 Extents per MB.

•To efficiently utilize space, SQL Server does not allocate whole extent to a table. SQL has 2 type of extents:

•Uniform Extents, owned by single object, all 8 pages in the extent can only be used by owning object.

•Mixed Extents, are shared by up to 8 objects. Each of 8 pages in the extent can be owned by different objects.

An new table or index is generally allocated pages from Mixed Extents. When the table or index grows to the point that it has 8 pages, it then switches to use Uniform Extents for subsequent allocations.If we create an index on existing table that has enough rows to generate 8 pages in the index,all allocations to the index are in Uniform Extents.