Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
| 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 value x being defined by the size of the file. You can 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 the DBCC SHRINKDATABASE or DBCC SHRINKFILE command, pages are removed starting at the highest-numbered page in the database (at the end) and moving toward lower-numbered pages. This ensures that page numbers within a file are always contiguous. When you create a new database using the CREATE DATABASE command, it is given a unique database ID, or DBID, and you can see a row for the new database in the sys.databases view. The rows returned in sys.databases include basic information about each database, such as its name, DBID, and creation date, as well as the value for each database option that can be set with the ALTER DATABASE command. I'll discuss database options in more detail later in the chapter. Space Allocation
The space in a database is used for storing tables and indexes. The space is managed in units called extents. An extent is made up of eight logically contiguous pages (or 64 KB of space). To make space allocation more efficient, SQL Server 2005 doesn't allocate entire extents to tables with small amounts of data. SQL Server 2005 has two types of extents:
SQL Server allocates pages for a new table or index 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 space that's available to be allocated. If the table or index is still less than eight pages total, SQL Server must find a mixed extent with space available. If the table or index is eight pages or larger, SQL Server must 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:
Table 4-2 shows the bit patterns that each extent has set in the GAM and SGAM, based on its current use.
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 it 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. If there are no mixed extents with available space, it uses the GAM page to find a whole new extent to allocate as a mixed extent, and uses one page from that. If there are no free extents at all, the file is full. SQL Server can quickly locate the GAMs in a file because a GAM is always the third page in any database file (page 2). An SGAM is the fourth page (page 3). Another GAM appears every 511,230 pages after the first GAM on page 2, and another SGAM appears every 511,230 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 I'll discuss shortly). In Chapter 6, I'll say more about how individual pages within a table look. For now, because I'm talking about space allocation, I'll examine how to keep track of which pages belong to which tables. Index Allocation Map (IAM) pages keep track of the extents in a 4-GB section of a database file used by an allocation unit. An allocation unit is a set of pages belonging to a single partition in a table or index and comprises pages of one of three types: pages holding regular in-row data, pages holding Large Object (LOB) data, or pages holding row-overflow data. I'll discuss these three types of pages, and when each kind is used, in Chapter 6. For example, a table on four partitions that has all three types of data (in-row, LOB, and row-overflow) will have at least 12 IAM pages. Again, a single IAM covers only a 4-GB section of a single file, so if the partition spans files, there will be multiple IAM pages, and if the file is more than 4 GB in size and the partition uses pages in more than one 4-GB section, there will be additional IAM pages. An IAM page contains a page header; an IAM page header, which contains eight page-pointer slots; and a set of bits that map a range of extents onto a file, which doesn't necessarily have to be the same file that the IAM page is in. 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 object contained in mixed extents; only the first IAM for an object has values in these pointers. Once an object takes up more than eight pages, all its extents are uniform extentswhich means that an object will never need more than eight pointers to pages in mixed extents. If rows have been deleted from a table, the table 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 object 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, the first and second extents in the range covered by the IAM are allocated to the object owning the IAM and 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 randomly in the database file. Each IAM covers a possible range of about 512,000 pages. The internal system view called sys.system_internals_allocation_units has a column called first_iam_page that points to the first IAM page for an allocation unit. All the IAM pages for that allocation unit are linked in a chain, with each IAM page containing a pointer to the next in the chain. I'll discuss allocation units in more detail in Chapter 6 when I discuss object data storage. In addition to GAMs, SGAMs, and IAMs, a database file has three other types of special allocation pages. Page Free Space (PFS) pages keep track of how each particular page in a file is used. The second page (page 1) of a file is a PFS page, as is every 8088th page thereafter. I'll talk about them more in Chapter 6. The seventh page (page 6) is called a Differential Changed Map (DCM) page. It keeps track of which extents in a file have been modified since the last full database backup. The eighth page (page 7) is called a Bulk Changed Map (BCM) page and is used when an extent in the file is used in a minimally or bulk-logged operation. I'll tell you more about these two kinds of pages when I talk about the internals of backup and restore operations in Chapter 5. Like GAM and SGAM pages, DCM and BCM pages have 1 bit for each extent in the section of the file they represent. They occur at regular intervalsevery 511,230 pages. Checking Database Consistency
DBCC stood for Database Consistency Checker in versions of SQL Server prior to SQL Server 2000. However, since Microsoft acquired the code base for the product from Sybase, DBCC began to take on more and more functionality, and eventually went way beyond mere consistency checking. For example, DBCC is used to shrink a database or a data file and to clear out the data or plan cache. Starting in SQL Server 2000, Microsoft finally acknowledged this evolution, and the glossary in Books Online for both SQL Server 2000 and SQL Server 2005 actually defines DBCC as Database Console Command and divides the commands into four categories: validation, maintenance, informational, and miscellaneous. In this section, I will discuss the DBCC commands that actually do consistency checking of the database, that is, the validation commands. These commands are the CHECK commands: DBCC CHECKTABLE, DBCC CHECKDB, DBCC CHECKALLOC, DBCC CHECKFILEGROUP, and DBCC CHECKCATALOG. Two others, DBCC CHECKCONSTRAINT and DBCC CHECKIDENT, will be described in Chapter 7, where I'll also discuss some of the table and index maintenance DBCC commands, such as DBCC CLEANTABLE and DBCC UPDATEUSAGE. I will cover DBCC INDEXDEFRAG and its SQL Server 2005 replacement when I cover indexes in Chapter 7. The most comprehensive of the DBCC validation commands is DBCC CHECKDB. Here is the complete syntax: DBCC CHECKDB [ [ ( 'database_name' | database_id | 0 [ , NOINDEX | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) ] [ WITH { [ ALL_ERRORMSGS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [ , ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ] } ] ]
I'll discuss most of these options to the DBCC CHECKDB command shortly. As part of its operation, DBCC CHECKDB runs all of the other DBCC validation commands in this order:
Because they are included as part of DBCC CHECKDB, the DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG commands do not have to be run separately if DBCC CHECKDB is run regularly. If you choose to run any of these commands individually, you can refer to Books Online for the complete syntax. On an upgraded database with no 2005 features or indexed views, DBCC CHECKDB will actually run slightly faster than its SQL Server 2000 counterpart. However, on a new SQL Server 2005 database, some of the logical checks added to complement new features in SQL Server 2005 are necessarily complex and do add to the runtime when invoked, so you may find that DBCC CHECKDB takes longer to run than you might have expected. Performing Validation Checks
In SQL Server 2005, all of the DBCC validation commands use database snapshot technology to keep the validation operation from interfering with ongoing database operations and to allow the validation operation to see a quiescent, consistent view of the data, no matter how many changes were made to the underlying data while the operation was under way. I'll discuss database snapshots in more detail later in this chapter. A snapshot of the database is created at the beginning of the CHECK command, and no locks are acquired on any of the objects being checked. The actual check operation is executed against the snapshot. As you'll see when we discuss database snapshots, the original version of a page is copied into the snapshot database when updates occur in the source, so the snapshot always reflects the original version of the data. Unlike regular database snapshots, the "snapshot file" that DBCC CHECKDB creates with the original page images is not visible to the end user and its location cannot be configured; it always uses space on the same volume as the database being checked. This capability is available only when your data directory is on an NTFS partition. If you aren't using NTFS, or if you don't want to use the space necessary for the snapshot, you can avoid creating the snapshot by using the WITH TABLOCK option with the DBCC command. In addition, if you are using one of the REPAIR options to DBCC, a snapshot is not created because the database is in single-user mode, so no other transactions can be altering data. Without the TABLOCK option, the DBCC validation commands are considered online operations because they don't interfere with other work taking place in a database. With the TABLOCK option, however, a Shared Table lock is acquired for each table as it processed, so concurrent modification operations will be blocked. Similarly, if modification operations are in progress on one or more tables, a DBCC validation command being run with TABLOCK will block until the transaction performing the modifications is completed. The DBCC validation checks can require a significant amount of space because SQL Server needs to temporarily store information about pages and structures that have been observed during the check operation, for cross-checking against pages and structures that are observed later during the DBCC scan. To determine the tempdb needs in advance, you can run a DBCC validation check with the ESTIMATEONLY option. For example, if I want to see how much tempdb space I might need to run DBCC CHECKDB on the AdventureWorks database, I can run the following: SET NOCOUNT ON; DBCC CHECKDB ('AdventureWorks') WITH ESTIMATEONLY; Here is the output I receive: Estimated TEMPDB space needed for CHECKALLOC (KB) ------------------------------------------------- 72 Estimated TEMPDB space needed for CHECKTABLES (KB) -------------------------------------------------- 198542 Note that even though AdventureWorks is considered just a sample database, it can require up to193 MB of tempdb space to run to completion. There are several large indexes in tempdb that contribute to this large space requirement, and in addition, this value is computed as a worst-case estimate and assumes there will not be room in memory for any of the sort operations required. SQL Server keeps track of the last error-free run of DBCC CHECKDB in the bootpage for every database, and it reports the date and time of the operation in the error log when SQL Server is started. Here is what the message might look like for the AdventureWorks database: Date1/24/2006 2:15:52 PM Message DBCC CHECKDB (AdventureWorks) executed by TENAR\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 5 minutes 8 seconds.
Validation Checks
SQL Server 2005 includes a set of logical validation checks to verify that data is appropriate for the column's datatype. These checks can be expensive and can affect the server's performance, so you can choose to disable this, along with all the other non-core logical validations by using the PHYSICAL_ONLY option. All new databases created in SQL Server 2005 have the DATA_PURITY logical validations enabled by default. For databases that have been upgraded from previous SQL Server versions, you must run DBCC CHECKDB with the DATA_PURITY option once, preferably immediately after the upgrade, as follows: DBCC CHECKDB (<db_name>) WITH DATA_PURITY
After the purity check completes without any errors for a database, performing the logical validations is the default behavior in all future executions of DBCC CHECKDB, and there is no way to change this default. You can, of course, override the default with the PHYSICAL_ONLY option. This option not only skips the data purity checks, but it also skips any checks that actually have to analyze the contents of individual rows of data and basically limits the checks that DBCC performs to the integrity of the physical structure of the page and the row headers. If the CHECKSUM option is enabled for a database, which is the default in all new SQL Server 2005 databases, a checksum will be performed on each allocated page as it is read by the DBCC CHECK commands. As I will mention again in the upcoming section on database options, when the CHECKSUM option is on, a page checksum is calculated and written on each page as it is written to disk, so only pages that have been written since CHECKSUM was enabled will have this check done. The page checksum value is checked during the read and compared with the original checksum value stored on the page. If they do not match, an error is generated. In addition, pages with errors are recorded in the suspect_pages table in the msdb database. DBCC Repair Options
The validation commands DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKALLOC allow you to indicate whether you want SQL Server to attempt to repair any errors that might be found. The syntax for the DBCC validation commands (except for DBCC CHECKCATALOG) allows you to specify either REPAIR_ALLOW_DATA_LOSS or REPAIR_REBUILD. Syntactically, you can also specify REPAIR_FAST, but that option is maintained only for backward compatibility, and no repair actions are performed. Almost all the possible errors that the DBCC command can detect can be repaired. The exceptions are errors found through DBCC CHECKCATALOG and data purity errors found through DBCC CHECKTABLE. When you run DBCC CHECKDB with one of the REPAIR options, SQL Server first runs DBCC CHECKALLOC and repairs what it can, and then it runs DBCC CHECKTABLE on all tables and makes the appropriate repairs on all the tables. The possible repairs for each table are ranked as SQL Server compiles the list of what needs repairing, to make the entire DBCC operation as efficient as possible. In this way, you won't end up, for example, in a situation where an index is being rebuilt, and then a page from table has to be removed, invalidating the work of rebuilding the index. If you're running a DBCC command with REPAIR_ALLOW_DATA_LOSS, SQL Server tries to repair almost all detected errors, even at the risk of losing some data. Keep in mind that for almost any severe error, some data will be lost when the repair is run. During the repair, rows might be deleted if they are found to be inconsistent, such as when a computed column value is incorrect. Whole pages can be deleted if checksum errors are discovered. During the repair, no attempt is made to maintain any constraints on the tables, or between tables. Some errors SQL Server won't even try to repairparticularly if the GAM or SGAM pages themselves are corrupted and unreadable. If you use the REPAIR_REBUILD option, SQL Server performs both minor, relatively fast repair actions such as repairing extra keys in nonclustered indexes and time-consuming repairs such as rebuilding indexes. These types of repairs can be performed without risk of data loss. After the successful completion of the DBCC command, the database is physically consistent and online but might not be in a logically consistent state in terms of constraints and your business rules. For this reason, you should use the REPAIR options only as a last resort. A much better solution in the case of non-fixable errors is to restore a database from a backup or restore a smaller unit of the database, such as a single filegroup. If you are going to use the REPAIR_ALLOW_DATA_LOSS option, you should back up the database before you run the DBCC command. You can run the REPAIR options for DBCC inside a user-defined transaction, which means you can perform a ROLLBACK to undo the repairs that have been made. The exception is when you are running the REPAIR options on a database in EMERGENCY mode, which I discuss later in the section on database options. (If a repair in EMERGENCY mode fails, there are no further options except to restore the database from a backup.) Each individual repair in the DBCC operation runs in its own system transaction, which means that if a repair is not possible, it will not affect any of the other repairs, unless subsequent repairs depended on an earlier success repair. If you do run one of the REPAIR options, you can provide a partial safeguard by creating a database snapshot before the repair is initiated, starting a transaction, and then running DBCC with the REPAIR option. Before committing or rolling back, you can compare the repaired database with the original in the snapshot. If you are not satisfied with the changes made as part of the repair, you can roll back the repair operation. Progress Reporting
Many of the DBCC commands in SQL Server 2005 provide progress reporting in the dynamic management view called sys.dm_exec_requests. Take a look at the following columns:
Progress reporting is available for DBCC CHECKDB, DBCC CHECKTABLE, and DBCC CHECKFILEGROUP. DBCC CHECKALLOC is not included in this list because it is such a fast command there would be no need (and usually no time) to check the progress. The command would be done before you had a chance to select from sys.dm_exec_requests. Progress reporting is also available for some of the maintenance commands, such as DBCC SHRINKFILE and DBCC SHRINKDATABASE. SQL Server will also populate the progress report columns when defragmenting an index using ALTER INDEX with the REORG option, because this command is equivalent to DBCC INDEXDEFRAG, which also supports progress reporting. DBCC Best Practices
Consider the following guidelines when planning how and when to use the DBCC validation commands:
Warning
|