August 6, 2011 at 1:50 am
Hi Team,
Recently, am going through the some of storage engine concepts and allocation in sql server and am little bit confused about the below concepts.
Can anyone explain me in simple terms so that i can understand them better
When does a PFS page actually gets created?
What is an IAM page and what for it is used?
Does IAM page is used for Heap also or it only exist for clusterred index table?
What is a IAM chain?
What is Allocation unit?
Also, assume i have created a sample heap table and am INSERTing a row for the first time, what will happen in the background?
How does GAM, SGAM , PFS , IAM etc... work internally and finally allocated a data page to insert the row.
Am looking for simple explaination for my mapping purpose.
While reading the msdn documentaion, everything is on top of head.
Any help would be greatly appreciated.
Thanks in Advance,
August 6, 2011 at 3:18 am
Oracle_91 (8/6/2011)
When does a PFS page actually gets created?
They're regularly spaced through the file, so it'll either be when the file grows or the first time SQL need to allocate an extent within the interval that the PFS page covers
What is an IAM page and what for it is used?
Index Allocation Map. It tracks what extents in the current GAM interval are owned by this index
Does IAM page is used for Heap also or it only exist for clusterred index table?
Heap, clustered index, nonclustered index (probably some others too)
What is a IAM chain?
If an index covers more than one GAM interval, it'll have more than one IAM page (one page per index per GAM interval). If it has more than one IAM page, then those IAM pages form a chain (pointers to each other) hence IAM chain.
What is Allocation unit?
A table/index has one or more partitions. Each partition has up to 3 allocation units that comprise it - In Row Data, Row Overflow Data, LOB Data
Also, assume i have created a sample heap table and am INSERTing a row for the first time, what will happen in the background?
How does GAM, SGAM , PFS , IAM etc... work internally and finally allocated a data page to insert the row.
Am looking for simple explaination for my mapping purpose.
That's 500+ level internal stuff, I don't think there is such a thing as a simple explanation.
PFS to tell if there's a page with enough space. GAM/SGAM to locate either an available dedicated extent (GAM) or an available page in a mixed extent (SGAM) if it needs to allocate new
That's kinda the 100000m view.
There's a lot on Paul Randal's blog, also probably in SQL 2008 Internals
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2011 at 8:26 pm
Thanks Gail for sharing the information. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply