%3CLINGO-SUB%20id%3D%22lingo-sub-1637065%22%20slang%3D%22en-US%22%3ESQL%20Server%20IAM%20page%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637065%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%20hope%20you%20enjoy%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-support%2Fsql-server-extents-pfs-gam-sgam-and-iam-and-related-corruptions%2Fba-p%2F1606011%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Epost1%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-support%2Fsql-server-gam-corruption-samples%2Fba-p%2F1607111%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Epost2%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EToday%2C%20I%E2%80%99m%20going%20to%20show%20you%20the%20detail%20of%20IAM%20page.%3C%2FP%3E%0A%3CP%3EAn%26nbsp%3B%3CSTRONG%3EIndex%20Allocation%20Map%20(IAM)%3C%2FSTRONG%3E%26nbsp%3Bpage%20maps%20the%20extents%20in%20a%204-GB%20part%20of%20a%20database%20file%20used%20by%20an%20allocation%20unit.%20An%20allocation%20unit%20is%20one%20of%20three%20types%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIN_ROW_DATA%3CBR%20%2F%3EHolds%20a%20partition%20of%20a%20heap%20or%20index.%3C%2FLI%3E%0A%3CLI%3ELOB_DATA%3CBR%20%2F%3EHolds%20large%20object%20(LOB)%20data%20types%2C%20such%20as%20XML%2C%20VARBINARY(max)%2C%20and%20VARCHAR(max).%3C%2FLI%3E%0A%3CLI%3EROW_OVERFLOW_DATA%3CBR%20%2F%3EHolds%20variable%20length%20data%20stored%20in%20VARCHAR%2C%20NVARCHAR%2C%20VARBINARY%2C%20or%20SQL_VARIANT%20columns%20that%20exceed%20the%208%2C060%20byte%20row%20size%20limit.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EEach%20partition%20of%20a%20heap%20or%20index%20contains%20at%20least%20an%20IN_ROW_DATA%20allocation%20unit.%20It%20may%20also%20contain%20a%20LOB_DATA%20or%20ROW_OVERFLOW_DATA%20allocation%20unit%2C%20depending%20on%20the%20heap%20or%20index%20schema.%3C%2FP%3E%0A%3CP%3EAn%20IAM%20page%20covers%20a%204-GB%20range%20in%20a%20file%20and%20is%20the%20same%20coverage%20as%20a%20GAM%20or%20SGAM%20page.%20If%20the%20allocation%20unit%20contains%20extents%20from%20more%20than%20one%20file%2C%20or%20more%20than%20one%204-GB%20range%20of%20a%20file%2C%20there%20will%20be%20multiple%20IAM%20pages%20linked%20in%20an%20IAM%20chain.%20Therefore%2C%20each%20allocation%20unit%20has%20at%20least%20one%20IAM%20page%20for%20each%20file%20on%20which%20it%20has%20extents.%20There%20may%20also%20be%20more%20than%20one%20IAM%20page%20on%20a%20file%2C%20if%20the%20range%20of%20the%20extents%20on%20the%20file%20allocated%20to%20the%20allocation%20unit%20exceeds%20the%20range%20that%20a%20single%20IAM%20page%20can%20record.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_0-1599248408060.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216659i10B684067191A2F3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_0-1599248408060.png%22%20alt%3D%22Liwei_0-1599248408060.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_1-1599248408062.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216658i8BAAB688B95479B3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_1-1599248408062.png%22%20alt%3D%22Liwei_1-1599248408062.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIAM%20pages%20linked%20in%20a%20chain%20per%20allocation%20unit%20An%20IAM%20page%20has%20a%20header%20that%20indicates%20the%20starting%20extent%20of%20the%20range%20of%20extents%20mapped%20by%20the%20IAM%20page.%20The%20IAM%20page%20also%20has%20a%20large%20bitmap%20in%20which%20each%20bit%20represents%20one%20extent.%20The%20first%20bit%20in%20the%20map%20represents%20the%20first%20extent%20in%20the%20range%2C%20the%20second%20bit%20represents%20the%20second%20extent%2C%20and%20so%20on.%20If%20a%20bit%20is%200%2C%20the%20extent%20it%20represents%20is%20not%20allocated%20to%20the%20allocation%20unit%20owning%20the%20IAM.%20If%20the%20bit%20is%201%2C%20the%20extent%20it%20represents%20is%20allocated%20to%20the%20allocation%20unit%20owning%20the%20IAM%20page.%3C%2FP%3E%0A%3CP%3EWhen%20the%20SQL%20Server%20Database%20Engine%20has%20to%20insert%20a%20new%20row%20and%20no%20space%20is%20available%20in%20the%20current%20page%2C%20it%20uses%20the%20IAM%20and%20PFS%20pages%20to%20find%20a%20page%20to%20allocate%2C%20or%2C%20for%20a%20heap%20or%20a%20Text%2FImage%20page%2C%20a%20page%20with%20sufficient%20space%20to%20hold%20the%20row.%20The%20SQL%20Server%20Database%20Engine%20uses%20the%20IAM%20pages%20to%20find%20the%20extents%20allocated%20to%20the%20allocation%20unit.%20For%20each%20extent%2C%20the%20SQL%20Server%20Database%20Engine%20searches%20the%20PFS%20pages%20to%20see%20if%20there%20is%20a%20page%20that%20can%20be%20used.%20Each%20IAM%20and%20PFS%20page%20covers%20lots%20of%20data%20pages%2C%20so%20there%20are%20few%20IAM%20and%20PFS%20pages%20in%20a%20database.%20This%20means%20that%20the%20IAM%20and%20PFS%20pages%20are%20generally%20in%20memory%20in%20the%20SQL%20Server%20buffer%20pool%2C%20so%20they%20can%20be%20searched%20quickly.%20For%20indexes%2C%20the%20insertion%20point%20of%20a%20new%20row%20is%20set%20by%20the%20index%20key%2C%20but%20when%20a%20new%20page%20is%20needed%2C%20the%20previously%20described%20process%20occurs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-support%2Fsql-server-extents-pfs-gam-sgam-and-iam-and-related-corruptions%2Fba-p%2F1606011%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Epost%201%3C%2FA%3E%2C%20I%20created%20a%20table%20with%208%20pages%2C%20all%20these%208%20pages%20are%20in%20mixed%20extent.%20I%E2%80%99m%20going%20to%20insert%20more%20rows%20into%20the%20table%20to%20increase%20the%20pages.%20As%20you%20expected%2C%20these%20new%20rows%2Fpages%20will%20be%20allocated%20to%20uniform%20extents.%3C%2FP%3E%0A%3CP%3EI%E2%80%99m%20going%20to%20walk%20you%20through%20how%20SQL%20Server%20save%20the%20info%20in%20IAM%20pages.%3C%2FP%3E%0A%3CP%3EData%20manipulation%3A%3C%2FP%3E%0A%3CP%3ET-SQL%3A%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(15%2CREPLICATE('o'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%209%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(16%2CREPLICATE('p'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%2010%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(17%2CREPLICATE('q'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%2011%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(18%2CREPLICATE('r'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%2012%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(19%2CREPLICATE('s'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%2013%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(20%2CREPLICATE('t'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%2014%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(21%2CREPLICATE('u'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%2015%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(22%2CREPLICATE('v'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%2016%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Einsert%20heaptable1%20values(23%2CREPLICATE('w'%2C8000))%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--page%2017%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20table%20heaptable1%20has%2017%20pages%20in%20four%20extents%2C%20two%20mixed%20extents%20and%20two%20uniform%20extents.%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20allocated_page_file_id%20as%20%5BFileID%5D%2Callocated_page_page_id%20as%20%5BPageID%5D%2Cpage_type_desc%2Cextent_page_id%2F8%20as%20ExtentID%2C%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Eis_mixed_page_allocation%2Cextent_page_id%20as%20%5BFirst%20Page%20in%20Extent%5D%2Cextent_page_id%2B7%20as%20%5BLastPage%20in%20Extent%5D%2Cis_allocated%20From%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3Esys.dm_db_database_page_allocations(db_id()%2Cobject_id('dbo.heaptable1')%2Cnull%2Cnull%2C'detailed')%26nbsp%3B%20order%20by%20allocated_page_page_id%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_2-1599248408068.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216660i53C358FB3D17C327%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_2-1599248408068.png%22%20alt%3D%22Liwei_2-1599248408068.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPage%20246%20is%20the%20IAM%20page.%3C%2FP%3E%0A%3CP%3EPage%20245%2C247%2C328%2C329%2C330%2C331%2C332%20and%20333%20are%20in%20mixed%20extent.%3C%2FP%3E%0A%3CP%3EStarting%20from%20page%20(1%3A352)%2C%20all%20the%20pages%20are%20allocated%20in%20uniform%20extent.%3C%2FP%3E%0A%3CP%3EPage(1%3A352)~(1%3A359)%20are%20in%20extent%2044%2C%3C%2FP%3E%0A%3CP%3EPage(1%3A360)~(1%3A367)%20are%20in%20extent%2045.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20me%20run%20DBCC%20PAGE%20of%20IAM%20page%20to%20show%20you%20how%20the%20info%20recorded.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_3-1599248408078.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216662i20FE40A890F5FB31%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_3-1599248408078.png%22%20alt%3D%22Liwei_3-1599248408078.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20first%208%20pages%20are%20listed%20under%20%E2%80%98Single%20page%20allocations%E2%80%99%2C%20which%20means%20they%20are%20in%20mixed%20extent.%3C%2FP%3E%0A%3CP%3E(1%3A352)~(1%3A360)%20are%20in%20uniform%20extent%2C%20the%20result%20matches%20the%20DMF%20sys.dm_db_database_page_allocations%3C%2FP%3E%0A%3CP%3E(1%3A352)%20is%20the%20first%20page%20of%20extent%2044%3C%2FP%3E%0A%3CP%3E(1%3A360)%20is%20the%20first%20page%20of%20extent%2045%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%E2%80%99s%20dive%20into%20the%20IAM%20page%20with%20parameter%201%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_4-1599248408088.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216661i67CA0F3F1FAB697E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_4-1599248408088.png%22%20alt%3D%22Liwei_4-1599248408088.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.There%20are%20two%20slots%20in%20IAM%20page%2C%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20first%20slot(slot%200)%20are%20for%20pages%20in%20Mixed%20extent.%3C%2FLI%3E%0A%3CLI%3EThe%20second%20slot(slot%201)%20are%20for%20pages%20in%20Uniform%20extent.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E2.Let%20me%20interpret%20the%20slot%200.%3C%2FP%3E%0A%3CP%3E1)Each%20string%20in%20rectangle%20stands%20for%20one%20page%2C%20the%20format%20is%20PageId%3AFileId.%3C%2FP%3E%0A%3CP%3E2)The%20first%208%20characters%20are%20page%20id%2C%20the%20last%20four%20part%20is%20for%20Fileid.%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20interpretation%20table%3A%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3EOriginal%20string-%26gt%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3EPId%3AFileId-%26gt%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3EFId%3APId-%26gt%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3EFId%3APId-%26gt%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3EFId%3APId(Decimal)%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3Ef50000000100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3Ef5000000%3A0100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3E0100%3A%20f5000000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3E0001%3A000000f5%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3E1%3A245%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3Ef70000000100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3Ef7000000%3A0100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3E0100%3A%20f7000000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3E0001%3A000000f7%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3E1%3A247%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3E480100000100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3E48010000%3A0100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3E0100%3A%2048010000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3E0001%3A00000148%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3E1%3A328%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3E490100000100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3E49010000%3A0100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3E0100%3A%2049010000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3E0001%3A00000149%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3E1%3A329%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3E4a0100000100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3E4a010000%3A0100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3E0100%3A%204a010000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3E0001%3A0000014a%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3E1%3A330%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3E4b0100000100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3E4b010000%3A0100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3E0100%3A%204b010000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3E0001%3A0000014b%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3E1%3A331%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3E4c0100000100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3E4c010000%3A0100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3E0100%3A%204c010000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3E0001%3A0000014c%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3E1%3A332%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22120%22%3E%3CP%3E4d0100000100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22105%22%3E%3CP%3E4d010000%3A0100%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22123%22%3E%3CP%3E0100%3A%204d010000%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108%22%3E%3CP%3E0001%3A0000014d%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22168%22%3E%3CP%3E1%3A333%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E3.I%20have%20demonstrate%20how%20the%20IAM%20page%20stores%20the%20first%208%20pages%20in%20mixed%20extent%2C%20now%20let%E2%80%99s%20move%20on%20to%20the%20uniform%20extent.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.The%20uniform%20extents%20info%20is%20stored%20in%20the%20second%20slot(slot%201).%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_5-1599248408098.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216663iF76DAE8632AAFCBE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_5-1599248408098.png%22%20alt%3D%22Liwei_5-1599248408098.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%E2%80%990000381f%E2%80%99%20is%20reserved%2C%20we%20don%E2%80%99t%20need%20to%20worry%20about%20it.%3C%2FP%3E%0A%3CP%3E2)The%20string%20I%20cared%20about%20is%20%E2%80%9800000000%200030%E2%80%99%3C%2FP%3E%0A%3CP%3EFollowing%20table%20interprets%20the%20string.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_6-1599248408099.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216664i31CA9AC7FC68D37D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_6-1599248408099.png%22%20alt%3D%22Liwei_6-1599248408099.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3)Each%20bit%20in%20the%20table%20stands%20for%20one%20extent.%3C%2FP%3E%0A%3CP%3E4)0%20means%20the%20extent%20is%20not%20occupied%2Fallocated%20by%20this%20table%20of%20the%20IAM.%3C%2FP%3E%0A%3CP%3E5)1%20means%20the%20extent%20is%20occupied%2Fallocated%20by%20this%20table%20of%20IAM%2C%20and%20it%E2%80%99s%20uniform%20extent.%3C%2FP%3E%0A%3CP%3E6)Here%20is%20more%20detail%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_7-1599248408102.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216665i66E63D34C12EED28%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_7-1599248408102.png%22%20alt%3D%22Liwei_7-1599248408102.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20exactly%20as%20same%20as%20the%20result%20we%20got%20from%20DBCC%20Page%20with%20parameter%203%20before.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Liwei_8-1599248408110.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216666iE9A7679418D9531A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Liwei_8-1599248408110.png%22%20alt%3D%22Liwei_8-1599248408110.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20download%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2Fattachments%2Fgxcuf89792%2FSQLServerSupport%2F1035%2F1%2Fdbtest20200904.zip%22%20target%3D%22_self%22%3Edbtest20200904.zip%3C%2FA%3E%20and%20give%20it%20a%20try.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

Hi Guys, welcome to SQL Server Allocation Series, hope you enjoy the post1 and post2.

Today, I’m going to show you the detail of IAM page.

An Index Allocation Map (IAM) page maps the extents in a 4-GB part of a database file used by an allocation unit. An allocation unit is one of three types:

  • IN_ROW_DATA
    Holds a partition of a heap or index.
  • LOB_DATA
    Holds large object (LOB) data types, such as XML, VARBINARY(max), and VARCHAR(max).
  • ROW_OVERFLOW_DATA
    Holds variable length data stored in VARCHAR, NVARCHAR, VARBINARY, or SQL_VARIANT columns that exceed the 8,060 byte row size limit.

Each partition of a heap or index contains at least an IN_ROW_DATA allocation unit. It may also contain a LOB_DATA or ROW_OVERFLOW_DATA allocation unit, depending on the heap or index schema.

An IAM page covers a 4-GB range in a file and is the same coverage as a GAM or SGAM page. If the allocation unit contains extents from more than one file, or more than one 4-GB range of a file, there will be multiple IAM pages linked in an IAM chain. Therefore, each allocation unit has at least one IAM page for each file on which it has extents. There may also be more than one IAM page on a file, if the range of the extents on the file allocated to the allocation unit exceeds the range that a single IAM page can record.

Liwei_0-1599248408060.png

 

Liwei_1-1599248408062.png

 

IAM pages linked in a chain per allocation unit An IAM page has a header that indicates the starting extent of the range of extents mapped by the IAM page. The IAM page also has a large bitmap in which each bit represents one extent. The first bit in the map represents the first extent in the range, the second bit represents the second extent, and so on. If a bit is 0, the extent it represents is not allocated to the allocation unit owning the IAM. If the bit is 1, the extent it represents is allocated to the allocation unit owning the IAM page.

When the SQL Server Database Engine has to insert a new row and no space is available in the current page, it uses the IAM and PFS pages to find a page to allocate, or, for a heap or a Text/Image page, a page with sufficient space to hold the row. The SQL Server Database Engine uses the IAM pages to find the extents allocated to the allocation unit. For each extent, the SQL Server Database Engine searches the PFS pages to see if there is a page that can be used. Each IAM and PFS page covers lots of data pages, so there are few IAM and PFS pages in a database. This means that the IAM and PFS pages are generally in memory in the SQL Server buffer pool, so they can be searched quickly. For indexes, the insertion point of a new row is set by the index key, but when a new page is needed, the previously described process occurs.

 

 

In post 1, I created a table with 8 pages, all these 8 pages are in mixed extent. I’m going to insert more rows into the table to increase the pages. As you expected, these new rows/pages will be allocated to uniform extents.

I’m going to walk you through how SQL Server save the info in IAM pages.

Data manipulation:

T-SQL:

insert heaptable1 values(15,REPLICATE('o',8000))     --page 9

insert heaptable1 values(16,REPLICATE('p',8000))     --page 10

insert heaptable1 values(17,REPLICATE('q',8000))     --page 11

insert heaptable1 values(18,REPLICATE('r',8000))     --page 12

insert heaptable1 values(19,REPLICATE('s',8000))     --page 13

insert heaptable1 values(20,REPLICATE('t',8000))     --page 14

insert heaptable1 values(21,REPLICATE('u',8000))     --page 15

insert heaptable1 values(22,REPLICATE('v',8000))     --page 16

insert heaptable1 values(23,REPLICATE('w',8000))     --page 17

 

The table heaptable1 has 17 pages in four extents, two mixed extents and two uniform extents.

select allocated_page_file_id as [FileID],allocated_page_page_id as [PageID],page_type_desc,extent_page_id/8 as ExtentID,

is_mixed_page_allocation,extent_page_id as [First Page in Extent],extent_page_id+7 as [LastPage in Extent],is_allocated From 

sys.dm_db_database_page_allocations(db_id(),object_id('dbo.heaptable1'),null,null,'detailed')  order by allocated_page_page_id

Liwei_2-1599248408068.png

 

Page 246 is the IAM page.

Page 245,247,328,329,330,331,332 and 333 are in mixed extent.

Starting from page (1:352), all the pages are allocated in uniform extent.

Page(1:352)~(1:359) are in extent 44,

Page(1:360)~(1:367) are in extent 45.

 

Let me run DBCC PAGE of IAM page to show you how the info recorded.

Liwei_3-1599248408078.png

 

The first 8 pages are listed under ‘Single page allocations’, which means they are in mixed extent.

(1:352)~(1:360) are in uniform extent, the result matches the DMF sys.dm_db_database_page_allocations

(1:352) is the first page of extent 44

(1:360) is the first page of extent 45

 

Let’s dive deep into the IAM page with parameter 1:

Liwei_4-1599248408088.png

 

 

1.There are two slots in IAM page,

  • The first slot(slot 0) are for pages in Mixed extent.
  • The second slot(slot 1) are for pages in Uniform extent.

2.Let me interpret the slot 0.

1)Each string in rectangle stands for one page, the format is PageId:FileId.

2)The first 8 characters are page id, the last four part is for Fileid.

Here is the interpretation table:

Original string->

PId:FileId->

FId:PId->

FId:PId->

FId:PId(Decimal)

f50000000100

f5000000:0100

0100: f5000000

0001:000000f5

1:245

f70000000100

f7000000:0100

0100:f7000000

0001:000000f7

1:247

480100000100

48010000:0100

0100:48010000

0001:00000148

1:328

490100000100

49010000:0100

0100:49010000

0001:00000149

1:329

4a0100000100

4a010000:0100

0100:4a010000

0001:0000014a

1:330

4b0100000100

4b010000:0100

0100:4b010000

0001:0000014b

1:331

4c0100000100

4c010000:0100

0100:4c010000

0001:0000014c

1:332

4d0100000100

4d010000:0100

0100:4d010000

0001:0000014d

1:333

3.I have demonstrate how the IAM page stores the first 8 pages in mixed extent, now let’s move on to the uniform extent.

 

4.The uniform extents info is stored in the second slot(slot 1).

Liwei_5-1599248408098.png

 

 

1)’0000381f’ is reserved, we don’t need to worry about it.

2)The string I cared about is ‘00000000 0030’

Following table interprets the string.

Liwei_6-1599248408099.png

 

3)Each bit in the table stands for one extent.

4)0 means the extent is not occupied/allocated by this table of the IAM.

5)1 means the extent is occupied/allocated by this table of IAM, and it’s uniform extent.

6)Here is more detail

Liwei_7-1599248408102.png

 

Which exactly as same as the result we got from DBCC Page with parameter 3 before.

Liwei_8-1599248408110.png

 

 

You may download the dbtest20200904.zip and give it a try.