Databases Under the Hood
A database consists of user-defined space for the permanent storage of user objects such as tables and indexes. This space is allocated in one or more operating system files.
Databases are divided into logical pages (of 8 KB each), and within each file the pages are numbered contiguously from 0 to x, with the upper value x being defined by the size of the file. You can then refer to any page by specifying a database ID, a file ID, and a page number. When you use the ALTER DATABASE command to enlarge a file, the new space is added to the end of the file. That is, the first page of the newly allocated space is page x + 1 on the file you're enlarging. When you shrink a database by using either the DBCC SHRINKDATABASE or DBCC SHRINKFILE command, pages are removed starting at the highest page in the database (at the end) and moving toward lower-numbered pages. This ensures that page numbers within a file are always contiguous.
The master database contains 49 system tables: the same 18 tables found in user databases, plus 31 tables that are found only in master . Ten of these tablesincluding syslockinfo , sysperfinfo , syscurconfigs , and sysprocesses don't physically exist in the master database; rather, they're built dynamically each time a user queries them. Twelve of these tables contain information about remote servers; they won't contain any data until you define other servers with which you'll be communicating. The master database also contains 23 system views: the same 20 found in all user databases, plus 3 additional views. All these system objects, in both the master and user databases, have names that begin with spt_ . To see a list of the system tables and views that are found in only the master database, you can execute the following query:
SELECT type, name FROM master..sysobjects WHERE type IN ('s', 'v') AND name NOT IN (SELECT name FROM model..sysobjects) GO |
The master database also contains 10 tables that can be referred to as pseudo-system tables . These table names begin with spt_ and are used as storage areas for various system procedures, but they aren't true system tables. You should never modify them directly because that could break some of the system procedures. However, deleting them wouldn't invalidate the basic integrity of the database, which is the case if true system tables are altered .
When you create a new database using the CREATE DATABASE statement, it's given a unique database ID, or dbid, and a new row is inserted in the master..sysdatabases table for that database. Only the master database contains a sysdatabases table. Figure 5-4 depicts a sample sysdatabases table, and Table 5-2 on the following page shows its columns .
The rows in sysdatabases are updated when a database's ownership or name is changed or when database options are changed. (Later in the chapter, we'll discuss this in more detail.)
Figure 5-4. Partial listing of a sysdatabases table.
Table 5-2. Columns of the sysdatabases table.
| Column | Information |
|---|---|
| name | Name of the database. |
| dbid | Unique database ID; can be reused when the database is dropped. |
| sid | System ID of the database creator. |
| mode | Locking mode, used internally while a database is being created. |
| status | Bit mask that shows whether a database is read only, off line, designated for use by a single user only, and so on. Some of the settings can be selected by the user with the sp_dboption stored procedure; others are internally set. (The SQL Server product documentation shows all the bit-mask values.) |
| status2 | Another bit-mask_like status, with bits indicating additional database options. (Not shown in Figure 5-4.) |
| crdate | For user databases, this is the date when the database was created. For tempdb , this is the date and time SQL Server was last started. For other system databases, this date is not really useful. Depending on decisions made during installation, this could be the date Microsoft originally created the database prior to shipping the code, or it could be the date that you installed SQL Server. |
| reserved | Reserved for future use. |
| category | Another bit-mask_like status. Contains information about whether the database is involved with replication. (Not shown in Figure 5-4.) |
| cmptlevel | Compatibility level for the database. This concept is briefly discussed at the end of this chapter. |
| filename | Operating system path and name for the primary file. |
| suid | ID of SQL Server login that owns the database. (Note that the sid column (above) contains the ID of the creator of the database, which might or might not be the owner.) The column suid always refers to the current owner. |
| version | Internal version of SQL Server with which the database was created. (Not shown in Figure 5-4.) |
Space Allocation
The space in a database is used for storing tables and indexes. As mentioned earlier in the chapter, space is managed in units called extents . An extent is made up of eight logically contiguous pages (or 64 KB of space). To make its space allocation more efficient, SQL Server 7 doesn't allocate entire extents to tables with small amounts of data. SQL Server 7 has two types of extents:
- Uniform extents These extents are owned by a single object; all eight pages in the extent can be used by the owning object only.
- Mixed extents These are shared by up to eight objects.
A new table or index is allocated pages from mixed extents. When the table or index grows to eight pages, all future allocations use uniform extents.
When a table or index needs more space, SQL Server needs to find some space that's available to be allocated. If the table or index is still less than eight pages total, SQL Server needs to find a mixed extent with space available. If the table or index is already eight pages or larger, SQL Server needs to find a free uniform extent. SQL Server uses two special types of pages to record which extents have been allocated and which type of use (mixed or uniform) the extent is available for:
- Global Allocation Map (GAM) pages These pages record which extents have been allocated for any type of use. Each GAM covers 64,000 extents, or almost 4 GB of data. This means one GAM page exists for every 4 GB of size. A GAM has one bit for each extent in the interval it covers. If the bit is 1, the corresponding extent is in use; if the bit is 0, the extent is free.
- Secondary Global Allocation Map (SGAM) pages These pages record which extents are currently used as mixed extents and have at least one unused page. Just like a GAM, each SGAM covers 64,000 extents, or almost 4 GB of data. The SGAM has 1 bit for each extent in the interval it covers. If the bit is 1, the extent is being used as a mixed extent and has free pages; if the bit is 0, the extent isn't being used as a mixed extent, or it's a mixed extent whose pages are all in use.
The following page shows the bit patterns each extent will have set in the GAM and SGAM based on its current use.
| Current Use of Extent | GAM Bit Setting | SGAM Bit Setting |
|---|---|---|
| Free, not in use | ||
| Uniform extent, or full mixed extent | 1 | |
| Mixed extent with free pages | 1 | 1 |
If SQL Server needs to find a new, completely unused extent, it can use any extent with a corresponding bit value of 1 in the GAM page. If SQL Server needs to find a mixed extent with available space (one or more free pages), it finds an extent with a value in the GAM of 0 and a value in the SGAM of 1.
SQL Server can quickly locate the GAMs in a file, because a GAM is always the third page in any database file (page 2). And an SGAM is the fourth page (page 3). Another GAM appears every 64,000 extents (512,000 pages) after the first GAM on page 2, and another SGAM every 64,000 extents (512,000 pages) after the first SGAM on page 3. Page 0 in any file is the File Header page, and only one exists per file. Page 1 is a Page Free Space (PFS) page, which will be discussed shortly.
In Chapter 6, we'll continue to explore how individual pages within a table look, but because we're talking about space allocation here, we'll examine how to keep track of which pages belong to which tables. Index Allocation Map (IAM) pages map the extents in a database file used by a heap or index. Remember from Chapter 3 that a heap is a table without a clustered index. Each heap or index has one or more IAM pages recording all the extents allocated to the object. A heap or index has at least one IAM for each file on which it has extents. A heap or index can have more than one IAM on a file if the range of the extents exceeds the range that an IAM can record.
An IAM contains a small header, eight page-pointer slots, and a set of bits mapping a range of extents onto a file. The header has the address of the first extent in the range mapped by the IAM. The eight page-pointer slots might contain pointers to pages belonging to the relevant objects that are contained in mixed extents; only the first IAM for an object will have values in these pointers. Once an object takes up more than eight pages, all its extents are uniform extentswhich means an object will never need more than eight pointers to pages in mixed extents. If rows have been deleted from a table, it can actually use fewer than eight of these pointers. Each bit of the bitmap represents an extent in the range, regardless of whether the extent is allocated to the object owning the IAM. If a bit is on, the relative extent in the range is allocated to the ob-ject owning the IAM; if a bit is off, the relative extent isn't allocated to the object owning the IAM.
For example, if the bit pattern in the first byte of the IAM is 1100 0000, it means that the first and second extents in the range covered by the IAM are allocated to the object owning the IAM and that extents 3 through 8 aren't allocated to the object owning the IAM.
IAM pages are allocated as needed for each object and are located ran-domly in the database file. Each IAM covers a possible range of 512,000 pages. Sysindexes.FirstIAM points to the first IAM page for an object. All the IAM pages for that object are linked in a chain.
NOTE
In a heap, the data pages and the rows within them aren't in any specific order and aren't linked together. The only logical connection between data pages is recorded in the IAM pages.
Once extents have been allocated to an object, SQL Server can use pages in those extents to insert new data. If the data is to be inserted into a B-tree, the location of the new data is based on the ordering within the B-tree. If the data is to be inserted into a heap, the data can be inserted into any available space. PFS pages within a file record whether an individual page has been allocated and the amount of space free on each page. Each PFS page covers 8000 contiguous pages (almost 64 MB). For each page, the PFS has 1 byte recording whether the page is empty, 1_25 percent full, 26_50 percent full, 51_75 percent full, or more than 75 percent full. This information is used when SQL Server needs to find a page with free space available to hold a newly inserted row. The second page (page 1) of a file is a PFS page, as is every 8000 th page thereafter.
EAN: 2147483647
Pages: 144