This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous articles, I explained basic about the architecture of Data Files and Data Pages of the SQL Server.
The SQL Server Extent is one type of storage unit, and it stores eight physical contiguous 8KB pages.
Each extent size is 64KB and it can store 16 extents per megabyte.
The eight data pages are logically grouped into extents and extents is responsible to manage storage space for data pages.
As per my previous discussion, a single data page can never merge data from multiple tables.
But SQL Server does not allocate whole extents to the particular table.
There are two types of Extent, Uniform Extent and Mixed Extent.
Uniform Extent: This Extent basically for single user object and it stores all 8 data pages for a single user object. If our table has a large set of records, internally, it stores all 8 data pages into one Extent.
Mixed Extent: This Extent owned by multiple user object and it stores 8 data pages may be for multiple user object. It can also combine different type of data pages into one extent.
When you first create a table SQL Server starts by allocating a data page to it from a Mixed Extent. Once the table has enough data to warrant a full extent, SQL Server will allocate a Uniform Extent to it.
Let me explain with the simple example:
First, create a table with sample records:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_TestPages( Name CHAR(8000) ) GO -- INSERT 50 Records INSERT INTO tbl_TestPages VALUES('dbrnd.com') GO 50 |
DBCC IND to check the information about allocated pages:
|
1 |
DBCC IND('database_name','tbl_TestPages',1) |
The Result:
You can find first 8 pages don’t have sequential PagePID because the first time it stored in a Mixed Extent mode. The second 8 pages have a sequential PagePID because the same table requires more 8KB pages.
