Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 27, 2004

SQL Server 2005 Part 4 - High Availability and Scalability Enhancements - Online Indexing, Fast Recovery, Database Snapshots, and Snapshot Isolation

By Marcin Policht

We are continuing our series which covers new and enhanced functionality implemented in SQL Server 2005 (based on its Beta 2 release), focusing on high availability and scalability area. We have presented so far database mirroring and failover clustering - now it is time for other features falling into the same category. In this article, we will describe improvements in online indexing and recovery procedures, as well as newly introduced database snapshots and snapshot transaction isolation level:

  • online indexing - allows execution of index data definition language statements in parallel with modifications and queries against underlying tables or views. In previous versions of SQL Server, such operations were not possible or limited at best. Creating, dropping, or rebuilding (which effectively drops and recreates) an index (typically performed as part of maintenance tasks geared towards optimizing query performance caused by index fragmentation) were precluding (or restricting to SELECT statements only) any type of access to indexed data (because of exclusive or shared locks that have been placed on it). Such behavior resulted, for example, from running DBCC DBREINDEX or dropping and recreating indexes. You could sometime improve the situation by running DBCC INDEXDEFRAG, which held locks for a much shorter time than the other two methods (although efficiency of this approach was limited when dealing with higher levels of fragmentation). SQL Server 2005 eliminates the need for exclusive locks completely by maintaining two copies of an index. While one of them is being rebuilt, the other can be used for supporting current activities (this one is subsequently dropped once the rebuild of the first one is completed).

    To rebuild an index, you can either execute ALTER INDEX T-SQL statement with the REBUILD clause (with serves as a replacement for DBCC DBREINDEX) or invoke the CREATE INDEX T-SQL statement with the DROP_EXISTING clause. (You can also split the process into two separate steps - first dropping and then recreating the index with DROP INDEX and CREATE INDEX statements, respectively, but this approach is least efficient and therefore not recommended). All of these activities can be performed on-line by adding the WITH (ONLINE=ON) clause to each statement (the clause can be combined with CREATE INDEX, DROP INDEX, ALTER INDEX REBUILD, ALTER INDEX DISABLE, ALTER TABLE ADD CONSTRAINT, and ALTER TABLE DROP CONSTRAINT).

    Note that online indexing has negative performance implications (due to the need of maintaining two copies of indexes), hence it is still advisable to perform it offline whenever possible. In addition, you should first evaluate, by determining the degree of fragmentation, whether there is a need for rebuilding an index or whether reorganizing the index instead, will suffice. This can be accomplished with sys.dm_db_index_physical_stats system function, which returns (among other values) the percentage of logical fragmentation for a particular (or all, if desired) indexes on a selected table or view. Index reorganization (recommended if fragmentation is no higher than 30%), which always performs as on-line an operation, sorts existing index pages in place (by physically reordering leaf level pages to match the logical sequence of leaf nodes) without dropping an index and creating a new one (and functions as an equivalent of SQL Server 2000 DBCC INDEXDEFRAG). You should resort to rebuilding an index if fragmentation percentage exceeds a 30% threshold (as explained, whether this operation is performed off- or on-line depends on whether the WITH ONLINE clause is applied to the associated T-SQL statement).

  • early restore access - allows connecting to a database during restore or recovery (invoked automatically whenever a database is brought online, for example, as the result of restarting SQL Server) as soon as committed entries in a transaction log are rolled forward. During the recovery process, entries in the transaction log designating transactions committed since the most recent checkpoint are reapplied to the database (during so-called "redo" phase), which is followed by rollback of all incomplete transactions (known as the "undo" phase). Both types of actions are necessary in order to maintain database consistency. Prior to SQL Server 2005, the database remained inaccessible for applications until the whole procedure was completed. With the new recovery model (available at this point only in SQL Server 2005 Enterprise Edition) this changes - the database becomes available as soon as the first "redo" part (involving replaying transactions committed after the most recent checkpoint) is finished. This does not mean, however, that access to all pages is granted - in order to prevent data consistency problems, data pages affected by uncommitted transactions are locked for the duration of the second part (requests for access to these pages need to wait until uncommited transactions are rolled back).

  • database snapshots - provides a read-only, "virtual" copy of a database, representing its state at a particular time. This functionality can be very useful in a number of scenarios, ranging from simplifying creation of historical reports reflecting content of the database at a specific moment in time to ensuring rapid recovery from risky system upgrades or accidental user errors.

    Snapshot is different from a traditional backup due to the way it is implemented (which also affects its size). Initiation of a database snapshot marks a point after which the first change to any data page triggers creation of its copy, which retains its original content. Snapshot consists of all such copies, in addition to pages that have not been modified yet (including those that would be affected by transactions not committed yet at the time of snapshot creation). Creation of duplicate pages is accomplished using copy-on-write approach, where a copy of each database page, which is supposed to be modified, is saved as part of the snapshot before modification is applied. Since, initially, the snapshot refers to the same pages as its source, space requirements as well as the amount of time and processing resources needed to create it are minimal. However, following its creation, first-time modification to original pages increase the amount of related I/O operations and space occupied by it.

    Snapshots work in different server configurations, including database mirroring and failover clustering. As a matter of fact, as we mentioned in our article on database mirroring, snapshots can be used to create a read-only copy of a mirror (the mirror can not be accessed directly since it is in recovery mode as long as the principal is operational), which in turn provides the ability to perform data reporting activities against it (to lower utilization of the principal).

    At the same time, however, snapshots have some important limitations. Among the most apparent ones is their read-only status and reliance on underlying database, which have a number of other implications (for example, their properties, such as permissions or online/offline status are inherited and can not be independently changed). Snapshots also cannot be created for any of the system databases (model, master, and temp). It is not possible to back them up and they should never be viewed as a substitute for backup - if the source database fails, its snapshot is no longer accessible. While their dependency on NTFS (because copied pages are stored in "sparse files," which are NTFS-specific) is unlikely to constitute a major issue in most implementations, it will prevent you from using RAW partitions for database storage.

    To create a database snapshot, use the standard CREATE DATABASE T-SQL statement including the unique name of the snapshot, logical name(s) of underlying database file(s), name of a sparse file where the snapshot will be stored, and the AS SNAPSHOT OF clause followed by the database name. (As mentioned before, you can create a snapshot of a mirror, in which case you use its name instead). For example, to create a snapshot of the pubs database, you could execute the following:

    CREATE DATABASE [pubs_ss120604] ON 
    	(NAME = pubs, 
    	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_ss120604.ss')
    AS SNAPSHOT OF pubs;

    A list of existing snapshots appears in the Database Snapshots folder of SQL Server Management Studio. Once snapshots are created, you can take advantage of them by either reading their content (users can access both the copy and the source, with the latter exhibiting the same characteristics as any standard database) or by reverting to them, overwriting effectively current data in the original database.

    The procedure of reverting has several prerequisites. First, you need to ensure that only a single snapshot for the target database exists, which means that you need to delete all of its remaining snapshots (to delete a snapshot, use the DROP DATABASE T-SQL statement including the snapshot name). You also need to remove all full-text catalogs from the database. After reverting is completed, the state of data reflects data retained in the snapshot, which means that all database changes following snapshot creation are lost. This includes entries in the transaction log, since reverting also clears its content. Therefore, it is recommended to perform full database backup immediately afterwards (no incremental or differential backups are possible at that point). To revert to a database snapshot, you need to run the RESTORE DATABASE T-SQL statement with the FROM DATABASE_SNAPSHOT clause, followed by its name.

  • snapshot isolation - a new type of transaction isolation level. Transactions can be viewed as a unit of work that satisfies four characteristics of the ACID model:

    • atomicity (stating that the transaction can either succeed in its entirety or fail, without leaving any changes)
    • consistency (conveying the notion that each transaction leaves a database in consistent state)
    • isolation (indicating that multiple transactions scheduled to take place in parallel execute independently)
    • durability (ensuring that the effect of each committed transaction is recorded and stored in the database).

    Transaction isolation levels protect data from consistency and integrity problems in a multi-user environment and impact primarily locking mechanisms used by the database engine to manage simultaneous requests for read access to the same rows or tables by multiple transactions. Raising the isolation level increases the potential for blocking but decreases the likelihood of negative concurrency side effects (such as dirty reads or lost updates). Analogically, decreasing isolation level makes it possible for multiple transactions to operate on the same data, but at the cost of possible update or read anomalies.

    SQL Server 2005 still supports levels available in earlier versions of SQL Server, such as Serializable, Repeatable Read, Read Committed, and Read Uncommitted (listed in the decreasing sequence of concurrency capabilities), however with the snapshot isolation it introduces a different approach to managing concurrent data access. Snapshot isolation does not lock rows of data involved in the transaction, but instead it creates their copies in tempdb database, assigning a version number to each. The copy is maintained until either transaction modifying the data is committed (at which point, it is deleted) or rolled back (resulting in restoring the original value). Snapshot isolation level is intended primarily for frequently repeating read operations on relatively dynamic data (which, with other isolation levels, would likely cause contention issues) and for databases where long-running transactions are common.

    You can apply snapshot isolation on the session level, using the SET TRANSACTION ISOLATION LEVEL SNAPSHOT T-SQL statement, as long as the ALLOW_SNAPSHOT_ISOLATION database option has been turned ON (note that this can be further modified for individual SELECT statements with table-level locking hints). In addition, if the READ_COMMITTED_SNAPSHOT database option is ON, then all transactions operating with read-committed isolation level also use row versioning instead of locking.

In our next article, we will discuss the remaining scalability and high-availability features in SQL Server 2005 Beta 2.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM