Under the covers: GAM, SGAM, and PFS pages

re: Under the covers: GAM, SGAM, and PFS pages

MikeWalsh — Mon, 17 Jul 2006 03:54:21 GMT

Thanks again. Keep up the good posts.

david_wei — Wed, 21 Mar 2007 21:38:37 GMT

Very good post, it answers questions which I could not find anywhere else!

However, in the PFS pages section, I think bit 4 and bit 5 are switched. should be like this:

bit 5 (0x20): is the page a mixed-page?

bit 4 (0x10): is the page an IAM page?

I did some practises on NON-IAM mixed-extent pages, the PFS returns 0X60 (allocated + Mixed)

for example:

DBCC page(AdventureWorks,1,16176,3) -- check the PFS to find a mixed extent

--return (1:20760) - (1:20767) = ALLOCATED 0_PCT_FULL Mixed Ext

DBCC page(AdventureWorks,1,20760,1)

--GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:16176) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL

And I have a question for the IAM pages,

I checked some of them, they alays return 0x70 (allocated + Mixed + IAM),

Does that mean IAM pages are always allocated in a mix extent ?

Also in the GAM, SGAM and IAM pages section, in the chart

the 3rd column is "Any IAM", What does "Any IAM" = 0 or "Any IAM" = 1 mean ?

Thanks.

David

Paul Randal - MSFT — Wed, 21 Mar 2007 21:50:55 GMT

Hey David - good catch - that's what happens when you try to do stuff from memory!

Yes, IAM pages are always from mixed extents. Any IAM means that one IAM page mut have a bit set corresponding to that extent.

Cheers

Tony Rogerson's ramblings on SQL Server — Thu, 28 Jun 2007 16:03:23 GMT

You’ve probably heard the term banded around but do you know what it means and what it means to the performance