SQL Basic - Storage Basic

Pages
1. Pages are used by SQL Server to store everything in the database data, metadata, index everything.
2. When space is allocated to database data files, all of the space is divided into pages. During allocation, each page is created to use 8KB (8192 bytes) of space and they are numbered starting at 0 and incrementing 1 for every page allocated.
3. There are three primary components to a page: the page header, records, and offset array. All pages begin with the page header. The header is 96 bytes and contains meta-information about the page, such as the page number, the owning object, and type of page. At the end of the page is the offset array. The offset array is 36 bytes and provides pointers to the byte location of the start of rows on the page. Between these two areas are 8060 bytes where records are stored on the page. As rows are added to a page, the row is added to the first open position in the records area of the page. After this, the starting location of the page is stored in the last available position in the offset array, Every time the row inserted, row add at the end of the page and make an entry in the offset.


Extents
1. Pages are grouped together eight at a time into structures called extents. There are two types of extents use by SQL Server databases: mixed and uniform extents.
2. In mixed extents, the pages can be allocated to multiple objects. The table will use mixed extents as long as the total size of the table is less than eight pages, Once the number of pages in a table exceeds eight pages, it will begin using uniform extents. In a uniform extent, all pages in the extent are allocated to a single object in the database. Due to this, pages for an object will be contiguous, which increases the number of pages of an object that can be read in a single read.

SQL Server stores information in the database on 8 KB pages. In general, records in tables are limited to that size; if they are smaller than 8 KB, SQL Server stores more than one record per page. There are two way to store data in pages
Forwarded Records : The forwarded records approach applied, when the table is heap. In the heap table when the record is updated that is not fit with in a page (8 KB) then the record is deleted from the current page and add a new record at the end of available page or new page. For example if we have a table that has 4 record and each record consume 2 KB space, its means 1 page memory is totally consumed. And then later if we update one record out of 4 that increase the row size with 2.5 KB, so it will no longer to fit in the existing page, so in this case sql server write the records on next available page in the heap and set the pointer with the new page. One drawback of this sql server has not physically free the space from the old page and as the modified record inserted at the end of the available page, so it would be a big reason of fragmentation

Page Split : The page split approach is applied, when a table has either clustered index or non-clustered index. In this approach, if a row is updated to a size that will no longer fit on the data page, SQL Server will take half of the records on that page and place them on a new page or next available page, Then SQL Server will attempt to write the data for the row to the page again. If the data will then fit on the page, the page will be written. If not, then the process will be repeated until it fits on the page.

The main difference between page splits and forwarded records, page split moves the location of records to make room for the records within the logical index ordering, whereas forwarded record moves the location on updated record at end of next available space.

Although, Performance issue occur in both the approaches, the page that is being split needs to be locked exclusively while the records are split between two pages. After the page is split, the physical order of the data pages in the index are almost always not in their logical order within the index, this means the more pages that need to be read into memory for a query to execute