There are a number of pages resident in a primary or secondary database file that are used to manage space in the file. These special pages are as follows:
• Secondary Global Allocation Map (SGAM) pages
• Index Allocation Map (IAM) pages
• Page Free Space (PFS) pages
To understand how GAM and SGAM pages fit into the picture we need to remind ourselves that there are two types of extent in SQL Server 2000. Uniform extents are eight pages in length and are allocated exclusively to one object when it requires space. For example, if a uniform extent is allocated to the Accounts table in the BankingDB database, then only rows from that table can use space on the eight pages.
Mixed extents are eight pages in length also but are allocated one page at a time to many objects when they require space. For example, a mixed extent may provide space for the Accounts table in the BankingDB database plus another seven tables. As we discussed earlier, mixed extents exist to save space, and, as such, the first eight pages of a table (or index) are allocated from mixed extents.
GAM pages hold information concerning which extents are currently allocated—that is, are not free. A single GAM page can manage 64,000 extents, which equates to nearly 4 GB of space. If more than 64,000 extents are present in the file, additional GAM pages are used. A GAM page uses a single bit to represent each extent out of the 64,000 extent range. If the bit is set (1), the extent is free; if it is not set (0), it is allocated.
SGAM pages hold information concerning which extents are currently being used as mixed extents and have one or more unused page—that is, have space that can still be allocated to objects. A single SGAM page can also manage 64,000 extents. If more than 64,000 extents are present in the file, additional SGAM pages are used. An SGAM page uses a single bit to represent each extent out of the 64,000 extent range. If the bit is set (1), the extent is being used as a mixed extent and has at least one unused page; if it is not set (0), it is not being used as a mixed extent, or, alternatively, it is a mixed extent but all the pages are in use. These settings are shown in Table 2.5.
Table 2.5: GAM and SGAM Page Settings
Extent Status GAM Bit Setting SGAM Bit Setting
Free, not being used 1 0 Uniform or full mixed extent 0 0 Mixed extent with free pages 0 1
To find a free extent to allocate as a uniform extent, the GAM is scanned for a bit that is set (1)—that is, an extent not currently in use. The bit is then set to 0 (allocated). To find a mixed extent having at least one free page that can be allocated, SQL Server searches the SGAM for a bit that is set (1). To find a free extent to allocate as a mixed extent, the GAM is scanned for a bit that is set (1)—that is, an extent that is not currently in use. The bit is then set to 0 (allocated). The equivalent bit in the SGAM is set to 1.
To free an extent, the GAM bit is set to 1 and the SGAM bit is set to 0.
Note When allocating extents to a table, SQL Server 2000 "round-robins" the allocation from each file if there is more than one file in the filegroup to which the table belongs. This
ensures that space is allocated proportionately from each file in the filegroup.
How does SQL Server 2000 keep track of which pages belong to a table or index? In previous versions of SQL Server (prior to SQL Server 7.0), data pages in a table were always chained together in a doubly linked list. This behavior changed in SQL Server 7.0 and so in SQL Server 2000 this is true only if the table has a clustered index (much more about clustered indexes in Chapter 3).
In SQL Server 2000 the extents used by a table or index are managed by IAM pages. A table or index has at least one IAM page, and, if the table or index is spread across more than one file, it will have an IAM page for each file. An IAM page can manage 512,000 pages, and, if the table size exceeds this within a file, another IAM is used. The IAM pages for a file or index are chained together. An IAM page must not only cater to uniform extents allocated to the table or index, but must also cater to single pages allocated from mixed extents.
To do this the first IAM page in the chain of IAM pages holds eight slots which can contain pointers to the eight pages that may be allocated from mixed extents. Other IAM pages in the IAM chain will not hold pointers in these slots. All IAM pages, though, will contain a bitmap with each bit presenting an extent in the range of extents held by the IAM. If the bit is set (1), the extent represented by that bit is allocated to the table or index; if it is not set (0), the extent represented by that bit is not allocated to the table or index.
To find the page ID of the first IAM page for a table or index, use the FirstIAM column in the sysindexes system table (the sysindexes system table will be discussed in Chapter 3). To do this use the following example:
SELECT object_name(id) AS Tablename , Name, FirstIAM FROM sysindexes
Tablename Name FirstIAM
--- --- --- Authors aunmind 0x7C0000000100 Publishers UPKCL_pubind 0x650000000100 Titles UPKCL_titleidind 0x690000000100
The Page ID is a hexadecimal number, which can be decoded as described previously in this chapter.
Note The SQL Server documentation refers to a heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Finally, our last special page is a PFS page. A PFS page holds the information that shows whether an individual page has been allocated to table, index, or some other structure. It also documents how free an allocated page is. For each page, the PFS has a bitmap recording whether the page is empty, 1 percent to 50 percent full, 51 percent to 80 percent full, 81 percent to 95 percent full, or 96 percent to 100 percent full. Each PFS page covers an 8,000- page range. When a search is made to look for free space, the PFS page is consulted to see which page in an extent belonging to the table or index may have enough free space.
This results in a fundamental difference between SQL Server 2000 and versions prior to SQL Server 7.0. In these previous versions, if there were no clustered index on the table, new rows
were always added at the end—that is, inserted into the last page. Now, rows can be inserted on any page in the table that has free space.
So, where in the database file do we find these special pages? The first page (0) contains a file header. The second page (1) is the first PFS page. The next PFS page will be found after another 8,000 pages. The third page (2) is the first GAM, and the fourth page (3) is the first SGAM. IAM pages are located in arbitrary positions throughout the file. This is shown in
Figure 2.19.
Figure 2.19: The PFS, GAM, and SGAM pages
This chapter has provided an overview of the SQL Server storage structures. In the next chapter we will look at tables and indexes in much more detail. But first of all, now that we have discussed databases, it is time to introduce the BankingDB database used in this book.