Sql server Page is fundamental unit of data storage and allocated in data file. Data file is logically divided into pages starting from 0 to n, SQL Server reads and writes operations are performed at the page level.
The collection of eight contiguous pages are known as extents i.e. 1 extent = 8 pages. All pages are stored in extents
Log file does not contain pages, it contains only series of log records
What is a page in sql server?
SQL Server reads and writes pages in data file for any modification. Changes that are done at database side, either it will add new pages or modify existing pages in data file. The page size is 8kb in sql sever.
Each page has 96 bytes allocated for header that is used to store system information about the page. It includes page number, page type, page id and amount data space allocated on page.
Pages are further divided based on types
Data Page – Includes all data except Large objects like text, image, ntext, varchar(max), nvarchar(max) and xml data
Index Page – Index related entries
Text/Image – information about data which exceed more than 8KBlarge objects like image, text, varchar(max), nvarchar(max) and xml data.
Global Allocation Map, Shared Global Allocation Map – includes extents allocation information.
Page Free Space – As per name, it includes information about page allocation and free space available on pages.
Index Allocation Map – It includes information about index allocation and extents used by a table.
Bulk Changed Map – This will give you information about modified extents by bulk operations since the last BACKUP LOG statement per allocation unit.
Differential Changed Map – It includes information about data changed since the last BACKUP DATABASE.
How data rows get stored in page?
Pages are divided into sections starting with header which store page information, page id, page number etc. In the next section, data rows are stored one by one serial wise followed by free space to allow data modification in future.
Last section stores reverse count of the data row like d1, d2 and d3 are stored on the page then at the end it will get store in reverse sequence like 3, 2, and 1.
What is an Extent in Sql server?
The collection of eight contiguous pages is known as extents. All pages are get allocated in extents that why the size of each extent is equal to 64 KB. It manages space for pages in Sql server.
SQL Server has two types of extents to manage space allocation efficiently.
- Uniform extents are owned by a single object, all eight pages in the extent can only be used by the owning object.
- Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.
A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.