Saturday, November 12, 2011

SQL Server Architecture - Fundamentals Of Pages and Extents In SQL Server

SQL Server Pages:
The Data Storage unit of the SQL Server is the Page. Microsoft SQL Server data is physically organized and stored in data pages. All SQL Server pages are 8KB in size. When you create primary or secondary database files, SQL Server allocates pages and extents for data storage. Each page has a number, starting from 0 to ..n; the number of the last page in the database is determined by the database file size.
When you shrink a database, then its data pages are removed, starting from the end of the file and slowly moving to the beginning. 

You want to know more about different types of pages in SQL Server then check this article for Different Types Of Pages In SQL Server.
 
Storage structure of rows in the pages...
Data rows are put on the page serially in a order like row1, row2.......n, starting immediately after the Header. 
A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. 
Picture #1
Note: The entries in the row offset table are in reverse order from the sequence of the rows on the page.

SQL Server Extents:
Extents are the basic unit in which space is managed. Eight data pages (8KB each) make up a single extent. Therefore, each extent is 64KB. Therefore SQL Server have 16 extents per megabyte.

SQL Server has two types of Extents:

Uniform Extent: Uniform extents are owned by a single object; all 8 pages in the extent can only be used by the owning object.

Mixed Extent:  Mixed Extent are shared by up to 8 objects. Each of the 8 pages in the extent can be owned by a different object.
Picture #2
SQL Server uses types of allocation maps to record the allocation of Extents: 

Global Allocation Map (GAM) 
This type of extent contains data from one table. Each GAM covers 64,000 extents, or almost 4 GB of data. 
See below table...
If the bit is 1, the extent is free.
if the bit is 0, the extent is allocated.

Shared Global Allocation Map (SGAM) 

This type of extent contains data from two to eight different tables. SGAM pages records which extents are currently being used as mixed extents and also have at least one unused page. Each SGAM covers 64,000 extents, or almost 4 GB of data. 
See below table...
if the bit is 1, the extent is being used as a mixed extent and has a free page. 
If the bit is 0, the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used.

Picture #3
Understanding Pages and Extents. Stairway to SQL Server Indexes. Data Pages & Extents. How SQL Server stores data. SQL Server Data Structure. SQL Server Interview Questions. SQL Server. Pages Extents. storage Mixed Data Heaps. pages and extents in sql server 2005. pages and extents in sql server 2008. pages and extents in sql server 2012. SQL Server Architecture. What are pages and Extents in sql server. Pages and Extents Architecture. how extent and pages behave when query works. Managing Extent Allocations and Free Space. extents on sql server database. What are different types of pages and extents. SQL Server 2008 Pages And Extents. SQL Server 2012 Pages And Extents.

1 comments:

Post a Comment