Page allocation Process in Sql Server 2016

Core Concept of Page Allocation and Extent in Sql Server 

There are two types of extents for memory management in Sql Server:

Uniform Extent: An extent whose all 8 pages are filled or reserved with one Table data is referred as uniform extent.

Mixed Extent: An extent whose 8 pages are shared by multiple objects is referred as a mixed extent.

Data is stored on an 8KB data pages and a set of 8KB sharing data pages form an extent of 64KB in Sql Server database object like Table and Index.

Page allocation Process in Sql Server 2016

Following is the page allocation process in the User Databases and TempDb of Sql Server 2016

·         In user Database or An database that is created by use that is by default will get the pages from the Uniform Extent in Sql server 2016. We can change this property by using MIXED_PAGE_ALLOCATION with ALLTER database.

·         TempDb database will get the pages from the Uniform Extent by default and there is no option to changes or override this paging allocation behaviour.

Example:-

Here I am create new table “PageAllocation_DemoTable” in this table I will insert records. Following is simple script to create table.

CREATE TABLE PageAllocation_DemoTable

(

    pageAllocationColumn VARCHAR(500)

)

Here I am inserting the records in above table so that we can analysis page allocation and spacing

DECLARE @loop INT =1

WHILE(@loop<=10)

       BEGIN

              INSERT INTO PageAllocation_DemoTable VALUES ('alok')

              EXECUTE SP_SPACEUSED PageAllocation_DemoTable

              set @loop=@loop+1;

       END

GO

  

Here we also can check the indexes details by using below query 

DBCC IND ('SqlManager','PageAllocation_DemoTable',-1)