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)
No comments:
Post a Comment
Note: only a member of this blog may post a comment.