The change in space management conventions from SQL Server 6.5 to SQL Server 7.0 is significant for DBAs who create and manage database space. SQL Server 7.0 creates databases directly on files that contain data from only one database. In this month's column, I'll explain how SQL Server 7.0 keeps track of the space within a database file, how it determines what space is available, and how it keeps track of the pages that belong to each table or index within a database. Armed with an understanding of SQL Server's space management protocol, you can create and maintain high-performing databases.
That's the Extent of It
SQL Server allocates memory to an object (meaning either a table or an index in the context of this article) in extents—eight contiguous pages. Pages are 8KB in SQL Server 7.0, so all extents are 64KB in size. In SQL Server 7.0, extents can be either mixed or uniform. A mixed extent can contain individual pages from up to eight separate objects. A uniform extent uses all eight pages for the same object.
When you initially create a table, SQL Server allocates a page for the table's data from a mixed extent. In addition, SQL Server allocates a special page called an Index Allocation Map (IAM) for the table. (The IAM will also be allocated from a mixed extent. It may be the same mixed extent as the first data page allocated to the table, or it may be from another mixed extent.) So SQL Server allocates two pages, or 16KB, for this table. If you create a table that contains rows so large that only one will fit on a page, then for every new row, SQL Server will allocate another page. You can create such a table by running the following code in the Query Analyzer:
create table largerow (a int identity, b char(8000) )
To see how much space SQL Server allocated to the table, you can run the sp_spaceused stored procedure:
sp_spaceused largerow, @updateusage = true
Your results will look like this:
|
name -------- largerow |
rows ---- 0 |
reserved -------- 16 KB |
data ----- 16 KB |
index_size ---------- 0 KB |
unused ------ 0 KB |
(1 row(s) affected)
SQL Server reserves one 8KB page for data and one 8KB page for the first IAM. If you insert a row of data, the reserved and data size will stay the same, but you'll see the row value increase from 0 to 1. The following statements demonstrate this increase:
insert into largerow values('a')
go
sp_spaceused largerow, @updateusage = true
go
Because the first column is an identity column, you don't need to supply a value for it. The second column is a fixed-length character column of 8000 bytes, so even though this code specifies only a single 'a', SQL Server will pad the column with spaces to a length of 8000, which will fill the page. The first eight pages of every table in SQL Server 7.0 are allocated one at a time from mixed extents, so each of the first eight rows in the table will require allocation of another page.
You can run the previous insert statement seven more times to observe this. The final sp_spaceused report will look like this allocation:
|
name -------- largerow |
rows ---- 8 |
reserved -------- 72 KB |
data ----- 72 KB |
index_size ---------- 0 KB |
unused ------ 0 KB |
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
|
name -------- largerow |
rows ---- 9 |
reserved -------- 136 KB |
data ----- 80 KB |
index_size ---------- 0 KB |
unused ------ 56 KB |
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.
