SQL Server Magazine

You can run the previous insert statement seven more times to observe this. The final sp_spaceused report will look like this allocation:

This output shows eight pages for the data and one page for an IAM. After you populate the eighth page of the table, the allocation mechanism changes. SQL Server starts allocating uniform extents of eight whole pages for the table. So if you execute one more insert and then check sp_spaceused, you'll see

You'll notice that although the data space increased by only 8KB (one page), the reserved space increased by 64KB, which means that SQL Server reserved an extent of eight pages for this table. Also, note that the "unused" column now has a non-zero value, which happens only when SQL Server allocates more space to an object than the object uses. As you add rows, you won't see the reserved space increase until you add another eight pages of used data space, which would fill the uniform extent. You will, however, see the used space increase and the unused space decrease. After the unused space has decreased to 0 (meaning the entire extent is used), SQL Server will allocate another entire uniform extent for the table.

SQL Server allocates the first eight pages of any table from mixed extents, and up to eight objects share the extent. SQL Server allocates all additional pages from uniform extents that belong to only one table. Figure 1 shows four extents in a database file: three uniform and one mixed. Two of the uniform extents belong to table T1, and one belongs to table T2. Tables T3 and T4 share the mixed extent, but no table is using its pages yet.

What Great GAMs
SQL Server uses a special type of page called an allocation map to determine which extents are available in a file. SQL Server 7.0 contains two kinds of allocation map pages: Global Allocation Maps (GAMs) and Secondary Global Allocation Maps (SGAMs). These pages use each bit in 8000 bytes to represent an extent. So 64,000 bits can represent 64,000 extents in a database file, or 512,000 pages. SQL Server stores a GAM on the third page of every database file and on every 512,000th page thereafter. SQL Server stores an SGAM on the fourth page of every file and on every 512,000th page thereafter. Each bit on a GAM page represents one extent in the 512,000-page section it covers. If the bit is 0, the extent is free. If the bit is 1, the extent is in use. SQL Server uses the SGAM to determine what the page is used for. Like a GAM, an SGAM has one bit for each extent in the 512,000-page section it covers. If the bit is 1, it means the extent is a mixed extent, and free pages are available. If the bit is 0, the extent is completely free, a uniform extent already in use, or a full mixed extent.

When SQL Server needs to allocate space to an object, it examines the GAMs and SGAMs. If SQL Server needs to find a free extent to use as a uniform extent, it looks for an extent with a GAM bit of 0. If SQL Server needs to find a single page, it looks for an extent with an SGAM bit of 1.

IAMs Too
To track which extents belong to a particular table, SQL Server uses IAMs. Like GAMs and SGAMs, each IAM covers a 512,000-page section. A bit represents each page in the section. Each object has one IAM for each 512,000-page section in a file in which pages of the object exist. For example, if the first page of a table is on page 777,777, the table won't have an IAM in the first 512,000-page interval. If an IAM has a 1 in the bit for a particular extent, that extent belongs to the object owning the IAM. If the bit is 0, the IAM's object doesn't own the extent.

Unlike GAMs and SGAMs, IAMs don't occur at a fixed location. Each 512,000-page section contains IAMs for numerous objects. To track the location of IAMs for a particular object, SQL Server stores the address of the first IAM in the sysindexes row for that object. The first IAM then has a pointer to the next IAM for the object, so IAMs for each object exist in a linked list.

If you know how SQL Server uses both mixed and uniform extents, you can interpret the output of the sp_spaceused command and understand how SQL Server uses the space in your database files. If you understand SQL Server 7.0's efficient mechanism for determining which extents are available for use, you'll appreciate its increased performance compared to earlier versions. Finally, if you know that SQL Server keeps track of pages with IAMs, you'll understand why the order in which SQL Server returns rows from a table may differ from what you expected. In SQL Server 6.5, if you stored a table with no clustered index, a query such as select * from table_name would always return the rows in the order they were inserted. In SQL Server 7.0, SQL Server scans the IAMs to determine which pages to return, and the order may not be at all what you expected nor the order in which the rows were inserted. You can use ORDER BY if you want SQL Server 7.0 to return rows in a particular order.

End of Article

Prev. page     1 [2]     next page -->