Microsoft SQL Server 2008 R2 Unleashed- P118:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1114 CHAPTER 34 Data Structures Indexes and Performance If SQL Server had to search throughout an entire database file to find free extents it wouldn t be efficient. Instead SQL Server uses two special types of pages to record which extents have been allocated to tables or indexes and whether it is a mixed or uniform extent Global allocation map pages GAMs Shared global allocation map pages SGAMs Global and Shared Global Allocation Map Pages The allocation map pages track whether extents have been allocated to objects and indexes and whether the allocation is for mixed extents or uniform extents. As mentioned in the preceding section there are two types of GAMs Global allocation map GAM The GAM keeps track of all allocated extents in a database regardless of what it s allocated to. The structure of the GAM is straightforward each bit in the page outside the page header represents one extent in the file where 1 means that the extent is not allocated and 0 means that the extent is allocated. Nearly 8 000 bytes 64 000 bits are available in a GAM page after the header and other overhead bytes are taken into account. Therefore a single GAM covers approximately 64 000 extents or 4GB 64 000 64KB of data. Shared global allocation map SGAM The SGAM keeps track of mixed extents that have free space available. An SGAM has a structure similar to a GAM with each bit representing an extent. A value of 1 means that the extent is a mixed extent and there is free space at least one unused page available on the extent. A value of 0 means that the extent is not currently allocated that the extent is a uniform extent or that the extent is a mixed extent with no free pages. Table summarizes the meaning of the bit in GAMs and SGAMs. TABLE Meaning of the GAM and SGAM Bits Extent Usage GAM Bit SGAM Bit Free not used 1 0 Uniform or mixed with no free pages 0 0 Mixed with free pages available 0 1 When SQL Server needs to allocate a uniform extent it simply searches the GAM for a .
