Manager
You can consider the row operations manager and the index manager as components of the access methods manager because they carry out the actual method of access. Each is responsible for manipulating and maintaining its respective on-disk data structures, namely rows of data or B-tree indexes. They understand and manipulate information on data and index pages.
The row operations manager
The row operations manager retrieves, modifies, and performs operations on individual rows. (For more information about on-disk structures, see Chapter 5; for more on data page and row format, see Chapter 6.) It performs an operation within a row, such as "retrieve column 2" or "write this value to column 3." As a result of the work performed by the access methods manager, as well as by the lock manager and transaction manager, which will be discussed shortly, the row will have been found and will be appropriately locked and part of a transaction. After formatting or modifying a row in memory, the row operations manager inserts or deletes a row.
The row operations manager also handles updates. SQL Server 2000 offers three methods for handling updates. All three are direct, which means that there's no need for two passes through the transaction log, as was the case with deferred updates in versions of SQL Server prior to SQL Server 7. SQL Server 2000 has no concept of a deferred data modification operation.
SQL Server 2000 has three update modes:
In-place mode This mode is used to update a heap or clustered index when none of the clustering keys
change. The update can be done in place, and the new data is written to the same slot on the data page.
Split mode This mode is used to update nonunique indexes when the index keys change. The update is split
into two operations—a delete followed by an insert—and these operations are performed independently of each other.
Split with collapse mode This mode is used to update a unique index when the index keys change. After the
update is rewritten as delete and insert operations, if the same index key is both deleted and then reinserted with a new value, the delete and insert are "collapsed" into a single update operation.
In Chapter 9, we'll look at examples of each type of update.
The index manager
The index manager maintains and supports searches on B-trees, which are used for SQL Server indexes. An index is structured as a tree, with a root page and intermediate-level and lower-level pages (or branches). A B-tree groups records that have similar index keys, thereby allowing fast access to data by searching on a key value. The B-tree's core feature is its ability to balance the index tree. (B stands for balanced.) Branches of the index tree are spliced together or split apart as necessary so that the search for any given record always traverses the same number of levels and thus requires the same number of page accesses.
The traversal begins at the root page, progresses to intermediate index levels, and finally moves to bottom-level pages called leaf pages. The index is used to find the correct leaf page. On a qualified retrieval or delete, the correct leaf page is the lowest page of the tree at which one or more rows with the specified key or keys reside. SQL Server supports both clustered and nonclustered indexes. In a nonclustered index, shown in Figure 3-3, the leaf level of the
tree (the leaf pages of the index) contains every key value in the index along with a bookmark for each key value. The bookmark indicates where to find the referenced data and can have one of two forms, depending on whether the base table has a clustered index. If the base table has no clustered index, the table is referred to as a heap. The bookmarks in nonclustered index leaf pages for a heap are pointers to the actual records in which the data can be found, and these pointers consist of a row ID (RID), which is a file number, a page number, and a row number on the page. If the base table has a clustered index, the bookmark in any nonclustered index leaf page contains the clustered index key value for the row.
After reaching the leaf level in a nonclustered index, you can find the exact location of the data, although you still must separately retrieve the page on which that data resides. Because you can access the data directly, you don't need to scan all the data pages to find a qualifying row. Better yet, in a clustered index, shown in Figure 3-4, the leaf level actually contains the full data rows, not simply the index keys. A clustered index keeps the data in a table logically ordered around the key of the clustered index, and the leaf pages of a clustered index are in fact the data pages of the table. All the data pages of a table with a clustered index are linked together in a doubly linked list. Following the pages, and the rows on those pages, from the first page to the last page provides the logical order to the data. Because data can be ordered in only one way, only one clustered index can exist per table. This makes the selection of the appropriate key value on which to cluster data an important performance consideration.
You can also use indexes to ensure the uniqueness of a particular key value. In fact, the PRIMARY KEY and UNIQUE constraints on a column work by creating a unique index on the column's values. The optimizer can use the knowledge that an index is unique in formulating an effective query plan. Internally, SQL Server always ensures that clustered indexes are unique by adding a 4-byte uniqueifier to clustered index key values that occur more than once. This uniqueifier becomes part of the key and is used in all levels of the clustered index and in references to the
clustered index key through all nonclustered indexes.
Figure 3-3. A nonclustered index with the leaf level containing bookmarks—either a RID or a clustered index
key value.
Since SQL Server maintains ordering in index leaf levels, you do not need to unload and reload data to maintain clustering properties as data is added and moved. SQL Server always inserts rows into the correct page in clustered sequence. For a clustered index, the correct leaf page is the data page in which a row is inserted. For a nonclustered index, the correct leaf page is the one into which SQL Server inserts a row containing the key value (and bookmark) for the newly inserted row. If data is updated and the key values of an index change, or if the row is moved to a different page, SQL Server's transaction control ensures that all affected indexes are modified to reflect these
changes. With transaction control, index operations are performed as atomic operations. The operations are logged and fully recovered in the event of a system failure.
For more details on index structures and maintenance, see Chapter 7.
Figure 3-4. A clustered index with the data located at the leaf level. Locking and Index Pages
As you'll see later, in the section on the lock manager, pages of an index use a slightly different locking mechanism than regular data pages. A lightweight lock called a latch is used to lock upper levels of indexes. Latches are not involved in deadlock detection because SQL Server 2000 uses "deadlock-proof" algorithms for index maintenance. You can customize the locking strategy for indexes on a table basis or index basis. You can use the system stored procedure sp_indexoption to enable or disable page or row locks with any particular index or, by specifying a table name, for every index on that table. The settable options are DisAllowPageLocks and DisAllowRowLocks. If both of these options are set to TRUE for a particular index, only table level locks are applied.