Have several questions about internal structure of SQL Server.
Lets assume that we have a table in a database.
-
SGAM & GAM pages keep track of shared & uniform extents within a single GAM interval(~4GB), and help us find appropriate type of extent when allocating pages/extents(initially first 8 pages are allocated from mixed extents & positions of those pages are recorder to IAM pages, and after that from GAM extents(uniform extents) and this information is stored in next chained IAM page).
Let's say, pages for the table were allocated, and table size is more than 8 pages, and sometime later, rows were deleted, leaving some re-usable space. How SQL Server knows that extents have free space ?
Pages have information about the size of free space, but it is too time consuming to check every page. Bits in SGAM & GAM pages don't tell us anything beside that extend is allocated or has some free pages(entire page is free, not partially).
-
File header & SGAM & GAM & IAM pages are among the first pages within a file. What data-structure actually points to them ?
Best Answer
SQL Server tracks free space on the page, in heap tables as well as in LOB pages using PSF page internally.
Also, you can use
sys.dm_db_file_space_usage
DMV to find outtotal_page_count
,allocated_extent_page_count
as well asunallocated_extent_page_count
within a database.Click here to enlarge - output using DBCC PAGE
Refer to : Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps
I do not get this question. The smallest unit of storage is a page. Eight 8K Pages form an extent. Below is what comprises of a data page:
I would highly recommend to read SQLSkills.com blog wherein Paul and his team blogs a lot about SQL Server internals.