SQL Server Magazine

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:

(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.

   Prev. page   [1] 2     next page