SQL Server Technical Article

Kimberly L. Tripp, Neal Graves

August 2006

Applies to:
   SQL Server 2005

Summary: Microsoft SQL Server 2005 provides nonlocking, nonblocking read consistency to your users through snapshot isolation and read committed isolation using row versioning. Find out when you can use these features to improve performance and reduce latency in your applications. (59 printed pages)

Download the associated sample code, SQLServer2005RVTI.exe [ http://download.microsoft.com/download/9/d/d/9dd98f1f-8434-4af6-a9d6-c2867986b20c/sqlserver2005rvti.exe ] .

Download the sample

Download the Word version of this article, RowVersioningBasedIsolation.doc [ http://download.microsoft.com/download/e/8/b/e8b42814-6a0c-40eb-911f-e7adec87f5d5/rowversioningbasedisolation.doc ] .

Contents

Introduction
Data Access Patterns and Usage
Usage Scenarios
Understanding Concurrency Control
Understanding Isolation
Definitions, Terminology, and Syntax for Row Versioning-Based Isolation
Development Best Practices
Administrative Best Practices
Conclusion
For More Information

Introduction

In many systems today, significant read activity is isolated from write activity in the form of a data warehouse or separated system. There are many advantages to this approach: read-intensive applications typically want more index structures, data redundancies, and even other views of data. Transaction processing systems want the best write throughput while incurring only the most minimal overhead. The access patterns of readers and writers typically differ: Readers are more prone to larger analysis types of queries, and writers are more prone to singleton inserts, updates, and deletes. When these activities are separated, the administrator can focus on recovery strategies for a smaller, more manageable transaction processing system. OLTP databases tend to be much smaller than data redundant decision-support or analysis-oriented databases. Having said this, however, this distinction cannot always be clearly made. After data is copied, transformed, and archived to an analysis-oriented database, the data must be maintained and/or rebuilt periodically. Users definitely benefit from looking at a transactionally consistent version of the database; however, that version of the database no longer has current data, may take many hours to build and index, and might not be what the user really wants. In this situation, snapshot isolation and read committed isolation using row versioning may be useful.

The primary focus of this paper is to discuss when using these isolation levels is appropriate, what the possible trade-offs are, and what are the best practices for using isolation levels.

Note   Before you read this document, we recommend that you read the topic Concurrency Effects [ http://msdn2.microsoft.com/en-us/library/ms190805.aspx ] in SQL Server Books Online.

Data Access Patterns and Usage

Production databases are quickly growing in size, and data-retention periods are increasing with changing business and regulatory requirements. Additionally, with drive capacity doubling every 12 to 18 months and storage costs falling, the amount of data users want to keep "online" is increasing. One solution is to separate analysis from transaction processing, and although that may have many benefits for complex detailed analysis and business intelligence probing, it does not always work with regards to disk space and manageability. With the demand for more data to be online with more active queries executing, the need for more current and real-time analysis contention for data exists.

In Microsoft SQL Server 2000, contention can be minimized under read committed transaction isolation when an active SELECT statement releases read locks after a resource is read. The default environment follows the standard SQL-92 definition in that only committed data is read and uncommitted changes are not visible. However, although only committed data can be read, the standard does not guarantee read consistency even within the life of a statement (for example, a subset of rows that are returned by a SELECT statement may include changes of a transaction that started after the SELECT but committed before the SELECT could end). The resource lock (a shared lock) is released immediately after processing the row, and that data row can be immediately modified, even while the read is still processing other rows. This modified row might reappear in the same SELECT statement if some concurrent transaction moves the row to the set of rows that have not yet been read (for example, an index key change).

Note   If data movement is not likely (that is splitting is reduced through thorough and appropriate index creation and maintenance), the chance of rereading a row within a single statement is so significantly low that it becomes hard to produce this anomaly.

In many situations, this is the correct and performant choice. Only committed changes are visible, and they are quickly visible with minimal resources locking. For example, if looking for the current total of sales from a continuously processing system, only an estimate is possible because the value will become "stale" only moments after being accessed when transactions continue to be processed. In fact, an even less restrictive transaction isolation level, called read uncommitted, is often specified with a lock hint by using either the WITH NOLOCK or WITH READUNCOMMITTED hints (these are synonymous). This environment allows for uncommitted data to be read; however, when the count of sales and/or total sales is only an estimate, seeing data that is "in progress" may be acceptable. When this is not acceptable, a change in isolation level—made by the programmer to ensure consistency through read repeatability of the data—must be used.

So where do you draw the line? Can statement-level or transaction-level read-consistent data be returned while a system is actively processing? Can you write a long-running query in a production environment, ask for consistency, and not block writers? You cannot address these questions in SQL Server 2000. However, in SQL Server 2005 you can through two optional database-level settings. For statement-level consistency, enabling READ_COMMITTED_SNAPSHOT automatically changes the behavior of read committed isolation. This new behavior offers nonlocking, nonblocking, statement-level read consistency. In this paper, the traditional read committed isolation will be referred to as read committed, and the optional new behavior as read committed using row versioning. For transaction-level consistency, the snapshot new isolation level has been added. Changing to this isolation level will make transaction-level consistency a controllable setting. When these new options are not set, SQL Server 2005 default database behavior works as in earlier versions. This default behavior will continue to be the preferred behavior in many systems in which transaction processing throughput and performance are the highest goals.

If you do not want a form of nonlocking snapshot (either statement level or transaction level), row versioning will be used to track row modifications. To enable this, data writers will pay the cost when an update or delete operation executes, even if there is no reader at the time. The version store in the tempdb database retains version records until all active transactions that require those row versions commit (assuming that the UPDATE or DELETE statement has already committed). Or more accurately, the version store must retain specific version records until the commit or rollback of transactions that are running under row versioning-based isolation that started before the commit of the transaction that made the change. Although this cost of taking a version is minimal, deciding to implement this should not be taken without careful consideration and many best practices in place.

Usage Scenarios

This section explores how the SQL Server 2005 snapshot isolation and read committed isolation using row versioning can help deliver improved performance, reduced latency, and greater developer and database administrator productivity in your organization. The following common business scenarios are discussed:

• Application in online transaction processing
• Ad hoc reporting against live data
• Ad hoc reporting against a copy-managed database
• Overnight reporting against live data
• Migration to a common database technology

Application in Online Transaction Processing

At first glance, the primary use of row versioning-based isolation might seem to be in read-intensive workloads, such as data warehousing and operational reporting systems. With these systems there might be a concurrency impact caused by table-level read locks of complex, long running queries (especially aggregations) against large tables that require a transactionally consistent view of the database that can effectively lock out transactions that need to update the data. However this is not the only application of row versioning-based isolations. The optional new behavior of the read committed isolation level, which works with a snapshot of the data at the statement level, can significantly improve the throughput of mixed-workload systems and still offer transactionally consistent data—for large joins and aggregations. Because the data snapshot guarantees the consistency of the read, long-running conflicts cannot occur. Additionally, in this environment, application changes are not required. The change is made at the database options level.

When pessimistic locking (the way most database vendors traditionally implement the full ANSI standard for levels of transaction isolation) is used, applications typically exhibit blocking. Simultaneous data-access requests from readers and writers within transactions request conflicting locks. This is entirely normal and is not a significant performance bottleneck, provided the blocking is short lived. However, reader/writer contention significantly reduces performance in systems under stress, because any increase in the time it takes to process a transaction (for example, delays that are caused by over-utilized system resources, such as disk I/O, RAM or CPU; and also delays that are caused by poorly written transactions, such as those that hold locks across user interaction) can have a disproportional affect on blocking. The longer a transaction takes to execute, the longer locks are held and the greater the likelihood of blocking.

An example of this might be a car rental company that uses both an internal and Web-based reservation application to book cars on behalf of its customers. Systems such as these have transactions that contend for the same data (that is cars). The system will offer short-running queries that allow the customer service representative to check availability of cars in certain locations before booking them for the customer—this is an area where programming techniques, such as disconnected datasets, are often used to provide optimistic concurrency control, specifically:

1. The application queries for all available cars of a certain class, in a specific date range, at a rental location. This query is probably a join of at least a few tables such as Car, Class, Reservation, and Location. Additionally, this query will run under the read committed isolation level to ensure that only committed data is returned to the user.
2. The recordset or dataset that is obtained by the query will be disconnected from the database so as to remove any locks held on the data while the data is displayed in the caller's application. This is often called batch optimistic because it emulates the optimistic forms of database concurrency control. It is optimistic in that although the data is active, the likelihood for conflict should be low. The use of row-level timestamps enables the programmer to identify data change and manage conflicting updates with appropriate messages to the user interface.
3. The caller will select a specific car, and the dataset will be edited to reflect the reservation.
4. The application will then reconnect and try to synchronize the change to the database, using the row-level SQL Server timestamp column to ensure that the data has not been changed by other callers while the data was disconnected.
5. The application then reports back to the caller to either report success (the reservation was taken) or to indicate a conflict (the car was taken by another caller) and to offer the chance to try to book another car.

Be aware that this technique is not truly optimistic. In this design pattern, a significant amount of contention can take place while the query in step 1 is running to find candidate cars. With SQL Server 2005 read committed isolation using row versioning, these requests are given a nonlocking, nonblocking, transactionally consistent version of the data—while the query runs. With this isolation, the locking/blocking load on the server can be reduced, and the live data is not blocked for other customers who want to reserve cars. While this isolation can improve the end-to-end performance for the transactions that are booking cars by eliminating lock waits, it does not necessarily improve the chances that a car that is viewed by the long running query will be available. However, this is an acceptable trade-off. The reservations occur faster and are not blocked by simultaneous requests for car rental data. This leads to increased throughput of transactions, especially under peak workloads, such as those caused by holiday bookings and business travel peak times.

After the new read committed isolation level using row versioning has been enabled by the database administrator at the database level, the programming logic that was used previously in steps 1 through 5 can take advantage of this new behavior without changing a line of code. In fact, after the database setting has been set, by default, all queries will use this form of statement-level read consistency.

Ad Hoc Reporting Against Live Data

All companies are continually striving to reduce costs while expanding the capabilities of their information systems. One of the guiding targets for SQL Server 2005 is the elimination of the latency between data being captured within a database and it being available for use for reporting by the organization—this reduction in latency enables developers to build systems that provide data outside of the traditional batch-reporting schedule.

Consider the scenario of a food retailer who is trying to balance the need to minimize the stock of fast moving consumer goods—such as sandwiches, milk, and other perishables that are held at each store—with the need to ensure that the shelves in the supermarket are stocked with items that customers want to buy. Many of these kinds of items are very sensitive to the weather. For example, barbeque items and ice cream sell more on sunny days; comfort foods sell more on rainy days.

Before the introduction of row versioning-based isolation levels, the developers of the supermarket application might have avoided long blocks on the live data by using the read uncommitted isolation level. This can be difficult to use, especially when joining across multiple tables, because the read uncommitted isolation level provides nonblocking access to a statement-level transactionally inconsistent view of the database. This is a view in which the data that is related to a business transaction may have only partially arrived in the database.

Furthermore, the practice of analyzing sales to look at the mix of other items sold together with the fastest selling items (also known as basket analysis) can be very data-intensive and, therefore, long running. This increases the chance of inconsistencies as data continues to arrive in the database.

For situations in which a transactionally consistent view of the data is a necessity, the system designers would typically design for these types of reports to run out of hours to avoid affecting the concurrency of the live system. For example, a long-running, read-only report that was started during peak usage could ultimately block all writers from updating the system, as shown in Figure 1.

Having an IT infrastructure that only provides for preplanned overnight reporting could hinder the ability of the supermarket manager to react to unexpected demands and review which products are at risk of selling out, and thus potentially miss an opportunity to order a second delivery to restock from the warehouse to meet demand, leading to loss of potential revenue, or even worse, loss of customers to competitors.

The new isolation levels provide applications with nonlocking access to a transaction-level or statement-level transactionally consistent view of the whole database; therefore, making the report writer's job much easier and also much more rewarding. In SQL Server 2005, the power of the SQL Server 2005 Database Engine has been even more tightly integrated with the advanced aggregation and analytical capabilities of the SQL Server 2005 Analysis Services component, which introduces the Universal Data Model and enables full analytical reporting without having to extract and transform data into star schema. Snapshot isolation technology has a major role to play in making data more accessible to this kind of application—being able to combine the power of the cross-selling reports with live data has the potential to change the way these business processes work.

Figure 1. Tills Blocked by a Reporting User

The new read committed isolation level using row versioning is best used for existing report systems (or systems that are purchased from third parties in which the isolation level cannot be changed) as it is likely that no application change will be required to take advantage of nonlocking reads, especially because most of these applications populate reports with the results of a single query. In this scenario, the row version (or the data snapshot) will be retained until the end of the transaction.

The new snapshot isolation level is suited to more complex requirements, such as running a series of reports that must run within the same transaction so that the same transactionally consistent view of the data can be seen. This solution is used more likely in complex financial reporting systems. In these systems, you do not want data changes to be picked up while the report suite is running, because this could easily cause anomalies in totals and checksums between reports. In this scenario the row version would be retained for the length of the transaction.

SQL Server 2005 makes it simple to enable these new isolation levels for a database. When the new form of read committed isolation level is configured, SQL Server automatically uses it without requiring any application or transactional code changes. To use transaction-level snapshot isolation, you will have to change the application to use it.

After either of these capabilities is enabled, it is then safe to provide the supermarket manager with a series of parameterized reports that can be run when unexpected demand takes place in the store. This is done without blocking the data coming in from the store's tills, thus helping the manager ensure that the needs of the store's customers are anticipated and met, leading to high customer satisfaction.

Enabling row versioning-based isolation levels makes additional demands on the database server. In the scenario described earlier, it is assumed that the back office server that was used to collect data arriving from the supermarket's tills had enough spare capacity to support the occasional requirement to run ad hoc reports against the live data. Row versioning, which supports row versioning-based isolation, imposes added load on a server running update transactions, both for data writers and data readers. For data writers, their changes must be versioned. For data readers, their reads must traverse the version chain to obtain the appropriate row version.

The additional load applies to tempdb, the database in which SQL Server stores the version store that is used to provide a transactionally consistent view of the changing data. Before deploying this new technology on production systems, we recommend that the database administrator test it on preproduction systems under simulated load, especially including long-running transactions or queries.

Be aware that simple measures such as providing more I/O bandwidth for tempdb, together with the scalability improvements made to tempdb in SQL Server 2005, should offset the effect of enabling row versioning-based isolation levels. However, if the system is already heavily loaded with a mixed update-and-read workload, the configurations that are discussed in the following scenarios might be more appropriate. This can be especially true when snapshot isolation is required instead of statement-level read committed using row versioning.

Ad Hoc Reporting Against a Copy-Managed Database

In systems that have a high percentage of data changing, enabling the use of the new snapshot isolation level may have a negative affect on overall performance, because the overhead of creating and managing the previous versions of a row can slow down transactions, particularly when tempdb or the disk subsystem is already close to being a system bottleneck. In this situation, the performance cost of enabling the new infrastructure may not be worth the value of reporting against the real-time data, especially because any reporting will likely add even more load to an already busy system.

This scenario is typical of reservation systems (such as airline and hotel reservation systems) and also order entry systems, including online systems such as Web shopping sites. The performance of updates during peak periods of load is critical. A slow update can cause a consumer to give up his or her purchase and go to another site. Conversely, the customer service departments and demand-forecasting staff must access reports that contain live data to help interact with customers, and to plan.

These conflicting requirements might best be served by creating a copy-managed database that is close to a near real-time replica of the data that lags behind the live system, but is "live enough" for reporting to take place. The goal of this replica is to offload the reporting users to another server (or even a set of servers) so that they do not add to the workload of the live system.

SQL Server 2005 provides two options for automating the maintenance of a replica database, both of which operate within the transaction logging mechanism and, therefore, on committed data: database mirroring and transactional replication.

Database Mirroring

Database mirroring is primarily designed to provide a database replica to be used as a hot standby of the production database, known as the principal database. The server instance on which the principal database resides is known as the principal server. The hot standby database is known as the mirror database, and the server instance on which it resides is known as the mirror server instance. Database mirroring is extremely easy to set up and manage, and once established all data is transferred from the principal server to the mirror server without the database administrator having to select specific tables. In fact, changes made to the principal database automatically occur on the mirror database.

SQL Server 2005 database snapshots on the mirror database can support transactionally consistent point-in-time reporting. However, database snapshots must be created manually, and it may be unrealistic to maintain one for each report (assuming each report requires access to the most current data). Furthermore, having more than a few database snapshots at a time can slow performance of the mirror server.

Other disadvantages include the fact that it is not possible to make changes solely to the mirror database; for example, filtering a subset of the data, adding reporting-only users with read-only privileges, or adding additional table indexes and indexed views designed to aid reporting performance. These changes can only be made to the principal, which may experience degradation in update performance as a result.

During each transaction commit process, the principal server sends log records to a mirror server. A database mirroring session can be configured for either synchronous or asynchronous operation:

• With synchronous operation, the commit does not complete until the log records are written to disk on both the principal and mirror servers. Performance on the principal server is thus sensitive to the ability of the mirror server to write the log to disk. For this reason, database mirroring is not suited to offloading reporting workload as any spikes in reporting workload can directly affect production system performance. For systems that must sustain continuous high rates of update transactions, database mirroring should be seen more as an availability feature than as a tool for creating reporting databases.
• With asynchronous operation, after the principal server writes its log to disk, it sends a confirmation to the client; and transactions commit without waiting for the mirror server to write the log to disk. Therefore, the performance impact on the principal server is typically less than for the synchronous mode. However, using a mirror server for reporting might cause it to lag behind the principal server during periods of heavy activity.

Transactional Replication

Transactional replication imposes only light overhead on the live system. This overhead can be mitigated by improving database log file I/O bandwidth. Committed transactions are read asynchronously from the publication database transaction log file, and the data associated with these transactions is copied to the distribution database. From the distribution database, the data can be fanned out to multiple Subscribers. Figure 2 illustrates a typical transactional replication setup across two servers.

Figure 2. Replication Data

This technology can be more difficult to manage than database mirroring, but it is often well known by database administrators because it has been a core component of SQL Server for many years. The data that is replicated can be a subset of the live system. You publish only the table you require, and you can apply row filters and column filters to these tables. Transactional replication also has the advantage of allowing different users, indexes, and views to be present in the subscription (reporting) database.

Disadvantages are few. There is always a lag, or latency, between a transaction that is being committed at the publication database and the corresponding transaction that will be committed at the subscription database (but the latency can be as low as server seconds). Also, an additional copy of each transaction must be maintained in the distribution database until the transaction is propagated to all Subscribers.

In SQL Server 2005, schema changes on replicated objects rarely require the sort of reworking that is required in earlier versions, and no longer require the use of replication-specific stored procedures to add or remove columns from tables. In earlier versions of SQL Server, the limited support for schema changes often makes it difficult to use replication with third-party applications; upgrades to those applications often require schema changes to the underlying database.

The main issue with the use of transactional replication in earlier versions is that the movement of data from the distribution database to the subscription database can experience the same issues that occur in systems that try to support OLTP and reporting on the same server: Long-running reports in the subscription database can block the replicated data that is arriving from the distribution database. This blocking can cause the subscription database to become increasingly out of synch and behind the publication database. This could frustrate call center employees who are trying to help customers that have recent purchases or reservations that have not yet arrived in their system. The blocking problem can be lessened by using the new SQL Server 2005 behavior for the read committed isolation level: read committed using row versioning.

The subscription database or databases can be enabled for READ_COMMITTED_SNAPSHOT so that shared locks are not required for read transactions against the subscription database, for example:

ALTER DATABASE AdventureWorks
    SET READ_COMMITTED_SNAPSHOT ON;

This setting helps remove blocking that is caused by database readers and, therefore, prevents the mostly read database from getting significantly behind the live system. Incoming data is not blocked behind long-running read transactions, and queries will execute against a transactionally consistent view of the database. Additionally, replication can maintain better transactional fidelity as it moves data around the system.

Transactional replication provides a very scalable solution. As the reporting workload grows, you can add more subscription databases on new servers. The system can cope with additional loads without any further affect on the live system, because data is fanned out from the distribution database instead of the live system. Now a customer service representative should be able to see transactions that are made by the customer in the live system while accessing the Subscriber to assist the customer as necessary.

Overnight Reporting Against Live Data

This scenario considers the classic data-processing model of an Online Day and an Overnight Batch. The Online Day matches a defined set of typical in-office "business hours" where data is entered into the system with a workload exclusively composed of short transactions. Overnight batch is where long-running processes move and report against the data that arrived during the day. This scenario is very typical in mainframe applications, with transaction processing (TP) monitors running in the day, and batch jobs running at night.

The growth of customer-facing, Internet-aware applications as well as increasing globalization of companies (with some offices coming online as others go offline) means that this model is less relevant to modern datacenters. However, there are still some lessons that can be learned by looking at this old technology:

• User-centric workloads tend to have peaks and troughs.
• Reports tend to be run at specific times to allow comparison with reports that were run previously at that time.
• The workload of most databases has peaks of updates (such as data loading) and peaks of reads (such as reporting).

Consider Litwareinc.com, a fictitious Internet-facing company that supports and sells personal audio technology. It has a datacenter in New York that services its U.S. business and also several smaller offices across seven countries. Like most companies, its online systems have definite usage patterns. Here, the peak load coincides with U.S. patterns with the arrival of staff in offices and the arrival of their primary customers at the Web site.

Transaction Processing System Workload

Table 1

For this scenario, snapshot isolation is preferred to read committed isolation using row versioning. The READ_COMMITTED_SNAPSHOT database option cannot be disabled during peak usage without disrupting user connections. When READ_COMMITTED_SNAPSHOT is disabled or enabled, only the user connection that sets this option is allowed in the database. The datacenter can manage the state of snapshot isolation while the database is online; there is no need to restart the database to pick up different settings. By only activating snapshot isolation in a narrow window, Litwareinc.com continues to offer service to online and global office users, but also ensures that long-running reports that require a transactionally consistent view of the database across queries will not block those users. By disabling snapshot isolation during peak usage, Litwareinc.com also ensures that maximum throughput is available to its primary users and customers.

Litwareinc.com also runs a complex data warehouse that is used to provide information about customer and stock trends, and to run larger reports that look for other patterns in the data over time. This system is primarily read-only; however there are a limited number of users who must have update access to the database to perform accounting-style journal adjustments as well as stock adjustments following audits. This does not present a problem for Litwareinc.com because the company has adapted its strategy for using row versioning-based isolation to the requirements of this system.

Data Warehousing System Workload

Litwareinc.com runs its data warehouse system 24 hours a day, seven days a week (24x7). Litwareinc.com uses snapshot isolation to provide its report consumers with high-performance access to transactionally consistent data. If transactional consistency is not needed across multiple queries, read committed isolation using row versioning would also apply to this scenario, because, unlike the previous scenario, the READ_COMMITTED_SNAPSHOT database option is continuously enabled.

Table 2

Litwareinc.com decided to continually operate this database with snapshot isolation enabled. In addition, it looked at the amount of data it had to take into the system each day and decided to maximize the performance of the load by using a mix of the full and bulk-logged recovery models. The full recovery model is used to protect the ad hoc adjustments that are made to data by the warehouse administrators. The bulk-logged recovery model is used to reduce the logging when loading data.

In the data-loading case, snapshot isolation settings did not have to be disabled because row insertion does not generate a version-chain entry (there is no older data to version). By running with 24x7 snapshot isolation, Litwareinc.com can enjoy both fast data loading and allow for ad hoc data adjustments to continue without being affected by long-running reports, which could otherwise block the data-load process. The only operational adjustment that was made was to switch the recovery models to reduce logging and improve the data load:

• Bulk-logged recovery model during data load because the system is fully recoverable by using the previous full backup, its associated logs and the incoming data extract files. The load is followed by changing the recovery model to full and then performing a log backup.
• Full recovery model the rest of the time. This allows for log backups to be taken so that accounting adjustments are not lost by hardware failure or media corruption.

The previous scenarios show how snapshot isolation can be deployed in a system that has variable transaction workloads: both online transaction processing and data warehousing. Snapshot isolation can be left active so that its benefits can be realized without significant impact on any of the key activities of the systems that underline the utility of this technology.

Migration to a Common Database Technology

Before SQL Server 2005, there were two camps in the commercial relational database management system world. The first were the systems that implemented a pessimistic concurrency model based on locking schemes that enable support for the four ANSI-standard isolation levels as defined in the SQL-99 standard (ANSI X3.135-1992, American National Standard for Information Systems—Database Language—SQL, November, 1992). These systems include Microsoft SQL Server, IBM DB2 (all of its many code bases/platforms and variants) as well as Sybase Adaptive Server. The second camp implemented a non-standard transaction isolation model with optimistic concurrency based on retaining a view of the data as of the start of the transaction—the only commercial system in this camp was Oracle. This division has led to three types of software developer:

• Develops on Oracle, ports to Microsoft SQL Server
• Develops on Microsoft SQL Server, ports to Oracle
• Develops and optimizes for both camps

Generally only the largest software companies can afford to be "type 3"—companies such as SAP, Siebel and PeopleSoft. Most developers must pick between type 1 or type 2, their choice normally being predicated by the degree to which the datacenter Unix market matters to their sales.

With SQL Server 2005 and the introduction of optimistic concurrency control using the row versioning-based snapshot isolation level, it is now much easier for type 1 application vendors to make a direct port to SQL Server and extend their market beyond the Oracle/Unix platform. Also, both row versioning-based isolation levels make it easier for IT Departments reduce the complexities that are associated with supporting multiple database platforms and to avoid costs, such as:

• Multiple database teams
• Increased training costs
• Reduced-volume software licensing costs
• Management time spent interacting with multiple vendors
• Matching differing supplier service levels

SQL Server 2005 offers the opportunity for customers to eliminate these additional costs, without having to change application vendor or experience a drop in optimal performance that is caused by the paradigm shift in the transaction isolation model.

The implementation of optimistic concurrency in SQL Server 2005 is different from Oracle. The SQL Server implementation is designed to be more controllable by the database administrator. Optimistic concurrency can be enabled and disabled on command, as shown in the scenario presented earlier in this paper. The SQL Server implementation is also more manageable. To help detect and decipher what is happening with the database, there are many Microsoft Windows System Monitor performance counters as well as SQL Server 2005 virtual tables that can be accessed through dynamic management views.

To assist the database administrator, the following table describes some differences between the SQL Server 2005 implementation of snapshot and that of Oracle.

SQL Server and Oracle Differences in Snapshot

Table 3

¹ From: http://www.oracle.com/technology/pub/articles/10gdba/week20_10gdba.html [ http://www.oracle.com/technology/pub/articles/10gdba/week20_10gdba.html ]

To assist the database developer in porting an application from Oracle to Microsoft SQL Server 2005, the following table describes some similarities between the implementation of snapshot in SQL Server 2005 and that in Oracle.

SQL Server and Oracle Similarities in Snapshot

Table 4

Based on these similarities to Oracle compared with earlier releases of SQL Server, SQL Server 2005 significantly eases the porting of applications that are built to run against databases that support optimistic concurrency. Additionally, SQL Server 2005 introduces a programming model that allows for the choice between pessimistic and optimistic concurrency control, and also numerous mechanisms for implementation. Database administrator tasks are eased by having a simple, easily configured version store that is online and enabled at the database level. Also, the process for porting code by a developer is simpler because of the close functional match between the Oracle and the SQL Server 2005 schemes. However, SQL Server 2005 exhibits more granular consistency behavior in that it manages versions at the row level instead of at the data-block level.

Understanding Concurrency Control

As seen within the usage scenarios provided earlier in this paper, there are two primary models that are used in controlling concurrency: pessimistic concurrency and optimistic concurrency.

In a pessimistic concurrency control-based system, locks are used to prevent users from modifying data in a way that affects other users. After a lock has been applied, other users cannot perform actions that would conflict with the lock until the owner releases it. This level of control is used in environments where there is high contention for data, and where the cost of protecting the data by using locks is less than the cost of rolling back transactions if or when concurrency conflicts occur.

Conversely, in an optimistic concurrency control-based system, users do not lock data when they read it. When an update is performed, the system checks to see whether another user has changed the data after it was read. If another user updated the data, an error is raised. Typically, the user that receives the error rolls back the transaction, and then resubmits the transaction. This is called optimistic concurrency because it is mainly used in environments where there is low contention for data, and where the cost of occasionally rolling back a transaction outweighs the costs of locking data when it is read.

Read committed isolation using row versioning is somewhere in between pessimistic and optimistic concurrency. Under this isolation level, read operations do not acquire locks against the live data. However, with update operations the process is the same for this isolation level as it is for the default read committed isolation level: The selection of rows to update is done by using a blocking scan where an update lock is taken on the data row as data values are read.

Snapshot isolation, on the other hand, is truly optimistic because data that is to be modified is not actually locked in advance, but the data is locked when it is selected for modification. When a data row meets the update criteria, the snapshot transaction verifies that the data has not been modified by another transaction after the snapshot transaction started. If the data has not been modified by another transaction, the snapshot transaction locks the data, updates the data, releases the lock, and moves on. If the data has been modified by another transaction, an update conflict occurs and the snapshot transaction rolls back.

Before SQL Server 2005, transactions are always controlled in a pessimistic manner; this means all transactions acquire locks. Although locking can be the best concurrency-control choice for applications that require data consistency and inter-transaction protection, it can cause writers to block readers. If a transaction changes a row, another transaction cannot read the row until the writer commits. There are cases where waiting for the change to complete is the correct response; however, there are cases where the previous transactionally consistent state of the row is enough.

Row versioning-based isolation levels allow for the reader to obtain the previously committed value of the row at the cost of having to keep this version when the row is modified—even if no one is "currently" accessing the data. This means that all SELECT, UPDATE and DELETE statements (but not INSERT statements, unless reinserting over a recently deleted record) might have to pay the cost of versioning with additional I/O into or from the versioning store. You must decide to trade improved concurrency at the cost of overhead (and therefore performance). It is important to state that although each query might cost more to execute because of versioning, the result might be that you can support more throughput because of reduced contention. For this reason, it is important that row versioning-based isolation levels be enabled where contention was costing you throughput. If you use this as a solution to a performance problem that is not caused by contention, you might be solving the wrong problem and, in fact, degrading your system throughput. This is similar to throwing hardware at a problem in which the performance issue is caused by poor application design and locking conflicts.

Generally, application programming is easier when the database automatically controls the view of the data through versioning-based isolation. In this environment, you worry less about deadlocks and blocking and pay a slight additional cost in administrative management overhead and performance. In many cases, paying a cost in administrative overhead and in providing more disk throughput for tempdb can be an easier choice—this is often known as "killing it with iron" and has the benefit that programmers do not have to worry about complex programming logic. If all queries that are running under row versioning-based isolation are solely for read consistency and not the basis for later modifications, no application retry logic is necessary. However, you might end up with conflicts in transactions that use the snapshot isolation level and later perform updates. If the version is "stale," it is likely you will have to use transaction retry logic for updates. This is exactly what we described earlier with optimistic concurrency control. However, the updates that are performed under read committed isolation using row versioning do not conflict and, therefore, will never incur the cost of rollback.

In situations where blocking is required for the correctness of the application (such as queues implemented in tables), if you have enabled read committed using row versioning, you must use the WITH (READCOMMITTEDLOCK) locking hint to get the expected "classic" behavior, because the row versioning-based read will never block.

The programmer now has the option of using SQL Server 2005 conflict resolution together with application error handling or Transact-SQL error handling in place of previous timestamp management techniques. Additionally, when a workload consists of batch-style updates in which many rows are modified, we do not recommend using snapshot isolation or read committed isolation using row versioning because the chance of conflict can be significantly larger. In that case, you should choose a lock-based isolation level (READ COMMITTED, REPEATABLE READ, OR SERIALIZABLE), keep your transactions short and carefully design your transactions to minimize resource conflicts so that you minimize deadlocks.

Understanding Isolation

Because transaction isolation levels are completely controllable in SQL Server 2005, it is important to understand the most appropriate isolation for your application—for both concurrency and performance and still maintaining the appropriate level of accuracy. The concept of isolation level is not new. Information about the ANSI specifications for isolation can be found at http://www.ansi.org [ http://www.ansi.org/ ] , and the current specification to review is ANSI INCITS 135-1999 (R1998). However, the standard is intended to be implementation-independent and being so makes it somewhat ambiguous in what the exact trade-offs are in consistency and performance as well as how to achieve these goals and standards. Therefore, numerous papers have been written to further clarify the standards, such as Generalized Isolation Level Definitions, or to even critique them, such as The Critique of ANSI Isolation Levels. Based on the philosophies that these works represent and the ambiguity in the ANSI standard, SQL Server 2005 offers many of the possible combinations that are typically wanted.

Isolation Levels Offered in SQL Server 2005

Table 5

	Possible phenomena (as defined in ANSI SQL Standard)
Isolation level	Dirty read	Nonrepeatable read	Phantom	Concurrency control
Read uncommitted	Yes	Yes	Yes	(None)
Read committed	No	Yes	Yes	Pessimistic
Read committed using row versioning	No	Yes	Yes	Optimistic reads Pessimistic updates
Repeatable read	No	No	Yes	Pessimistic
Snapshot	No	No	No	Optimistic
Serializable	No	No	No	Pessimistic

The application usage for each of the above varies based on the required level of "correctness" and the trade-off chosen in performance and administrative overhead.

Isolation Level and Best-Suited Application

Table 6

Isolation level	Best suited for an application when:
Read uncommitted	The application does not require absolute accuracy of data (and could get a larger or smaller number than the final value) and wants performance of OLTP operations above all else. There is no version store to maintain, and no locks are acquired or honored when reading data. Data accuracy of queries in this isolation may see uncommitted changes.
Read committed	The application does not require point-in-time consistency for long-running aggregations or long-running queries, yet wants data values that are read to be only transactionally consistent. The application does not want the overhead of the version store when reading data with the trade-off of potential incorrectness for long-running queries because of nonrepeatable reads. This isolation level is ideally suited to transactions that rely upon the blocking behavior of locks to implement queuing applications or other ordered access to data.
Read committed Using Row Versioning	The application requires absolute point-in-time consistency for long-running aggregations or queries. All data values must be transactionally consistent at the point in time where the query begins. The database administrator chooses the overhead of the version store for the benefit of increased throughput because of reduced lock contention. Additionally, the application wants transactional consistency for large queries, not transactions.
Repeatable read	The application requires absolute accuracy for long-running multistatement transactions and must hold all requested data from other modifications until the transaction completes. The application requires consistency for all data that is read repeatedly within this transaction and requires that no other modifications be allowed. This can affect concurrency in a multiuser system if other transactions are trying to update data that has been locked by the reader. This is best when the application is relying on consistent data and plans to modify it later within the same transaction.
Snapshot	The application requires absolute accuracy for long-running multistatement transactions but does not plan to modify the data. The application requires consistency for all data that is read repeatedly within this transaction but plans to only read data. Shared locks are not necessary to prevent modifications by other transactions because the changes will not be seen until after the data modification transactions commit or roll back, and the snapshot transaction finishes. Data can be modified within this transaction level at the risk of conflicts with transactions that have updated the same data after the snapshot transaction started. This conflict must be handled by each updating transaction. A system that has multiple readers and a single writer (such as the replicated reporting system in the scenario section earlier in this paper) will not encounter conflicts.
Serializable	The application requires absolute accuracy for long-running multistatement transactions and must hold all requested data from other modifications until the transaction finishes. Additionally, the transactions are requesting sets of data and not just singleton rows. Each set must produce the same output at each request within the transaction, and other users cannot modify the data that has been read and cannot insert new rows into the set. This is best when the application is relying on consistent data, plans to modify it later within the same transaction, and requires absolute accuracy and data consistency up to the end of the transaction.

Considerations for Row Versioning-Based Isolations

Although the change to read committed using row versioning does not require application changes (unless the application depends on the underlying locking behavior), it does require administration changes—the option must be activated per database. Enabling a database to allow snapshot isolation requires administrative planning, and possibly application planning. However, to use snapshot isolation requires application changes. In both cases, the option to enable row versioning-based isolation levels occurs at the database level, and row-versioning data is stored within memory (for short-lived versions) and tempdb.

Be aware that, like other database-level settings, both row versioning-based isolation levels settings can be made on the model system database. These settings are then propagated when databases are created. model acts as a template that is applied at create time. This is useful if you want to create a set of standard database settings, because it avoids the administration task of connecting and updating each database in turn.

Note   You cannot enable the READ_COMMITTED_SNAPSHOT database option for tempdb and for master. The master database is always enabled for snapshot isolation.

Definitions, Terminology, and Syntax for Row Versioning-Based Isolation

To implement row versioning–based isolation in SQL Server 2005, you must be familiar with a few new concepts, terms, and syntax. In earlier versions of SQL Server, isolation level was controlled only by a session setting (SET TRANSACTION ISOLATION LEVEL or the equivalent settings on the ADO or ADO.NET call) or by a query hint (FROM table name WITH (isolation hint)).

In SQL Server 2005, to use row versioning–based isolation, one of the following database options must already be set (and not pending):

• Read committed isolation using row versioning for statement-level read consistency
• Snapshot isolation for transaction-level read consistency

If snapshot isolation is requested and the database is not ready to handle snapshots (for example, still pending), the statement that is requesting snapshot will fail. It is important to make changes at the appropriate time when you are enabling and disabling snapshot isolation, and to understand the state of the database and client requests at the time of the change.

Read Committed Isolation Using Row Versioning (Statement-Level Read Consistency)

When set, statement-level read consistency guarantees that each statement under read committed isolation sees only committed changes that occurred before the start of the statement. Each new statement within the transaction picks up the most recent committed changes. The version "refresh" occurs at the beginning of each SELECT statement. In other words, this version of read committed is semantically similar to traditional read committed in that only committed changes are visible, but the timing of when those changes committed differs. Each statement sees the changes that were committed before the statement began instead of when the resource is read. This is a completely new behavior of read committed that is nonlocking, non-blocking, and creates a solid point in time in which the data is accurate at the start of the statement.

Statement-level snapshot is enabled by setting the READ_COMMITTED_SNAPSHOT database option to ON. After it is set to ON, no other application changes are necessary.

ALTER DATABASE <database name>
   SET READ_COMMITTED_SNAPSHOT ON
   WITH <termination>;

Executing this statement requires that only one user connection, the one that is enabling the READ_COMMITTED_SNAPSHOT database option, to exist on the database. Be aware that this does not mean that the database must be in single-user mode. Use the ALTER DATABASE WITH <termination> statement to end other user sessions in the database and to roll back their incomplete transactions. Ideally, this change should be made off hours, and it is likely that this will be a permanent change. To see whether a database has this option set, use the sys.databases catalog view.

SELECT sd.is_read_committed_snapshot_on
FROM sys.databases AS sd
WHERE sd.[name] = '<database name>';

The value that is returned for is_read_committed_snapshot_on will be either true (1) or false (0). When the READ_COMMITTED_SNAPSHOT option is set to ON, read operations under the read committed isolation level are based on row versions and are executed in a nonlocking mode. When READ_COMMITTED_SNAPSHOT is set to OFF, the scans under read committed isolation are executed in a short-term locking mode where locks are only held for the duration of the read request.

Snapshot Isolation (Transaction-Level Read Consistency)

When snapshot isolation is set, it guarantees transaction-level read consistency where every statement within a snapshot isolation transaction sees only committed changes that occurred before the start of the transaction. Effectively, each statement in the transaction sees the same set of data while the data is available for modification outside of this transaction. Concurrent modifications are not prevented and this "snapshot" transaction is unaware of the changes that are made by other transactions. The version "refresh" occurs only in the start of each transaction as long as you run under snapshot isolation Concurrent updates can potentially cause the transaction that is running under snapshot isolation to quit in case of conflicts.

To achieve transaction-level snapshots, there are two changes that are required:

• First, the database must allow for it by setting the ALLOW_SNAPSHOT_ISOLATION database option to ON.
• Second, the application or user, or both, must explicitly request a snapshot transaction.

Allowing Snapshot Isolation

Administrators must set a database option to allow snapshot isolation. This database option might not take effect immediately; however, the option can be changed while users are connected to the database. If users are currently processing transactions when the state change is made, all transactions must finish before snapshot transactions can occur. This is because row versions have not been maintained for those transactions that are currently executing.

If changing the state is taking a lot of time and transactions try a snapshot transaction while the database is still "pending," the transactions will receive an error. If there are long-running transactions executing at the time of the change, the change to a versioning state can take a long time to finish. The database administrator can cancel the request; and, if the request is canceled, the versioning state is rolled back to the previous versioned (or nonversioned) state. To request snapshot isolation for the database, change the database state by using the following ALTER DATABASE statement:

ALTER DATABASE <database name>
   SET ALLOW_SNAPSHOT_ISOLATION ON;

To see whether the option has taken effect, you can examine the sys.databases catalog view. The two columns to look at are: snapshot_isolation_state and snapshot_isolation_state_desc.

snapshot_isolation_state returns a tinyint value between 0 and 3:

• 0 = Snapshot isolation is OFF.
• 1 = Snapshot isolation is ON.
• 2 = Snapshot isolation state is in transition to OFF state.
• 3 = Snapshot isolation state is in transition to ON state.

snapshot_isolation_state_desc returns an nvarchar(60), which is a character description of the following pending state:

• OFF = Snapshot isolation is OFF.
• ON = Snapshot isolation is ON.
• IN_TRANSITION_TO_OFF = Snapshot isolation state is in transition to OFF state.
• IN_TRANSITION_TO_ON = Snapshot isolation state is in transition to ON state.

SELECT sd.snapshot_isolation_state
    , sd.snapshot_isolation_state_desc
FROM sys.databases AS sd
WHERE sd.[name] = '<database name>';

Table 7

If the database option is taking a long time to move out of the pending state, you can use the following statement to return a virtual table that shows the transactions that are active and blocking the database state change.

SELECT stx.session_id
    , atx.[name]
    , stx.transaction_sequence_num  
    , stx.first_snapshot_sequence_num
    , stx.commit_sequence_num
FROM sys.dm_tran_active_transactions AS atx
INNER JOIN sys.dm_tran_active_snapshot_database_transactions AS stx
ON atx.transaction_id = stx.transaction_id

Requesting Snapshot Transactions

As mentioned previously in this paper, after the database has been enabled for snapshot isolation, developers and users must then request that their transactions be run in this snapshot mode. This must be done before starting a transaction, either by a client-side directive on the ADO.NET transaction object or within their Transact-SQL query by using the following statement:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

If users execute this session setting change before the database has completed this change, the user's transaction will fail with error 3956: "Snapshot isolation transaction failed to start in database '%.*ls' because the ALTER DATABASE command which enables snapshot isolation for this database has not finished yet. The database is in transition to pending ON state. You must wait until the ALTER DATABASE Command completes successfully."

Understanding the "Beginning" of a Transaction

Versioning is performed for all updates when the database allows snapshot; however, the version that a transaction will use is based on the first statement that accesses data, and not the BEGIN TRAN that creates the transaction. However, if the transaction isolation level is being set to use snapshot isolation, this must be done before any transaction is created (with a BEGIN TRAN or ADO.NET equivalent). Otherwise, the following is an example of the error message that is returned:

Msg 3951, Level 16, State 1, Line 7
The transaction fails in the database because the statement was run
under snapshot isolation but the transaction did not start in snapshot
isolation. You cannot change the isolation level of the transaction to
snapshot after the transaction has started unless the transaction was
originally started under snapshot isolation level.

To avoid this error message, relocate the isolation specification before the transaction is created.

For example:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT getdate(); -- (T1) transaction has not "officially begun"
SELECT * FROM <tablename>; -- (T2) transaction t has begun
SELECT... -- will see all committed changes as of (t)
SELECT... -- will see all committed changes as of (t)
COMMIT TRAN;

Understanding Row Versioning

Versioning effectively starts with a copy-on-write mechanism that is invoked when a row is modified or deleted. This requires that while the transaction is running the old version of the row must be available for transactions that require an earlier transactionally consistent state. Row versioning–based transactions can effectively "view" the consistent version of the data from these previous row versions. Row versions are stored within the version store that is housed within the tempdb database.

More specifically, when a record in a table or index is modified, the new record is stamped with the "sequence_number" of the transaction that is performing the modification. The old version of the record is copied to the version store, and the new record contains a pointer to the old record in the version store. If multiple long-running transactions exist and multiple "versions" are required, records in the version store might contain pointers to even earlier versions of the row. All the earlier versions of a particular record are chained in a linked list; and in the case of long-running row versioning–based transactions, the link will have to be traversed on each access to reach the transactionally consistent version of the row. Version records must be kept in the version store only as long as there are row versioning–based queries that might be interested in them.

Row Versioning in Read Committed Using Row Versioning

For queries that are running under read committed using row versioning, row versions are necessary only for the duration of each SELECT statement in the transaction. However, row versions are maintained until the end of the transaction. This is an important point to consider for tempdb storage. The storage impact will be the same as it would be for snapshot isolation.

Row Versioning in Snapshot Isolation

For queries that are running under snapshot isolation, the row versions must be kept until the end of the transaction. Because a transaction might span multiple statements and a potentially longer period of time, the version store will have to potentially accommodate multiple versions of a row for a potentially longer period of time.

In the following figure, the current version of the record is generated by transaction T₃, and it is stored in the usual data page. The previous versions of the record that were generated by transaction T₂ and transaction T₁ are stored in pages in the version store because there are still transactions running under snapshot isolation that are accessing the previous state of the data.

Figure 3. Versions of a Row

Row versioning will slow the update performance because of the extra work that is involved in keeping old versions; however, in cases where contention is costly, you might see improved performance in the reduction in contention. Additionally, row versioning–based statements and transactions (also known as version readers) have the extra cost of traversing the version link pointers. If many row versioning–based transactions exist and are long-running transactions, a larger and faster tempdb may be necessary, and performance may degrade if tempdb is not configured properly.

DDL Statements Within Snapshot Isolation

Certain DDL statements that modify the structure of an object will be disallowed because their changes cannot be seen through row versioning. For example, consider a snapshot transaction that has read table1 and found 6 rows:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
SELECT count(*) FROM table1; -- (Returns 6 rows) 
...

A second transaction adds rows to this table that are not visible to this transaction because the transaction is using row versioning to maintain transaction-level read consistency. If this transaction were allowed to execute a CREATE INDEX statement, how would this work? Would the index be created on the snapshot view of the data, or would it include all rows as CREATE INDEX typically would? Considering the first part of the question, how would the Database Engine reconcile the changes that are occurring simultaneously with DDL operations that are executed by other transactions? Considering the second part of the question, what if multiple-row versions were creating additional indexes? Instead, CREATE INDEX is disallowed within a snapshot transaction. In fact, numerous DDL statements are disallowed because they violate the concept of "snapshot" and must be invoked against the actual base object, not a "version" of it.

Be aware that these restrictions do not apply to read committed using row versioning. DDL statements (or data access queries) will queue and not fail.

DDL Statements That Are Not Allowed Within Snapshot Isolation

The following statements are not allowed within a transaction that is running under snapshot isolation because of their disruptive potential on the snapshot copies of the data:

• CREATE INDEX
• CREATE XML INDEX
• ALTER INDEX
• ALTER TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER PARTITION SCHEME
• DROP INDEX
• Common language runtime (CLR) DDL

An attempt to run one of these statements will cause a severity level 16 message to be returned, such as:

Msg 3964, Level 16, State 1, Line 1
Transaction failed because this DDL statement is not allowed
inside asnapshot isolation transaction. Since metadata is not
versioned,a metadata change can lead to inconsistency 
if mixed within snapshot isolation.

Other DDL statements that are not listed here, for example CREATE TABLE, are allowed because other transactions cannot view earlier versions of the data because it is a new object. This does not violate the rules that are listed previously.

Other DDL Statement Changes After Snapshot Isolation Started

In most production databases the schema is relatively stable. However, changes might have to be made. If the system is highly available and user activity will occur concurrently with schema changes, programmers should be prepared for errors being raised from these changes. Because row versioning only exists for data rows, not metadata, one way of keeping the view consistent for the databases is to block all DDL operations in the server instance while a snapshot transaction is running. This would be too restrictive because a long-running snapshot transaction would prevent a database administrator from performing any DDL operation in a database for potentially a long time. Instead, DDL operations are supported while snapshot transactions are running; however, the snapshot transaction might encounter failure if the operations try to access the objects that have changed since the start of the snapshot transaction. The following shows a timeline (time is from left to right):

T₁|--- Snapshot transaction ------------------------ Use object (fail) -----------|

T₂ |---DDL, change the object ---commit --|

The application programmer should put in retry logic for snapshot transactions to deal with this kind of error, and administrators should try to minimize DDL changes during highly active times of day.

Snapshot Transaction Failure Due to DDL Changes Outside of the Transaction

Not all DDL changes will cause a snapshot transaction to fail. Use the following table to see the likely impact of DDL changes taking place while snapshot transactions are running. It is important to realize that stable schemas will avoid transaction failure when using snapshot isolation.

Be aware that this behavior is expected because system objects are not covered by the row versioning. For example, if they were covered, the system would potentially have to maintain multiple copies of an index (and the accompanying data). This would incur large overhead, perhaps inadvertently.

Table 8

Development Best Practices

The database administrator for a system is responsible for evaluating the impact on the system and applications of either of the row versioning–based transaction isolation schemes. The developer is responsible for understanding how to exploit the new isolation-level behaviors to build better applications. The scenarios provided earlier in this paper show some configurations for which the new isolation levels would make sense. This section provides for detailed information about how the developer can use this new functionality.

Read Committed Using Row Versioning

SQL Server 2005 provides a nonblocking read committed transaction isolation level that is based on statement-level views of data. This option must be enabled by the database administrator and does not require any application-level changes to use. If this option is enabled, transactions that are running under the read committed isolation level use row versioning and do not acquire read locks because they read data. Instead, the version store is used to isolate the transaction from changes that are taking place as the read operation executes. This protection is at the statement level. If the application runs two SELECT statements within the same read committed transaction, the results can differ if data changes have been committed between the two statements, as they can with the traditional read committed isolation level.

This behavior is a powerful tool for developers because it enables the application to consume more data without leading to increased blocking as readers compete with writers for data. It is common to see statements such as:

SELECT COUNT(Orders) FROM sales.dbo.orders WITH(NOLOCK)

In this query the NOLOCK (same as READUNCOMMITTED) hint is required to stop the read locks that would typically be taken by this query from blocking the taking of new orders—however, it has the side-effect of returning uncommitted orders, possibly leading to an inaccurate total (because the uncommitted orders might never be committed to the database). Using the new behavior of read committed using row versioning, the query can be run without the lock hint, and an accurate view of committed data can be obtained without blocking online updaters. This is even more advantageous when you are performing a more complex query that involves several joins because a stable view of the database is provided to the statement. This avoids anomalies in the results that can be caused by late arrival of parent or child records, which could be picked up by the NOLOCK hint.

Some applications, especially those that implement queues in tables, might require the former blocking behavior—in this case the locking hint READCOMMITTEDLOCK should be used:

SELECT TOP 1 NextOrderID FROM sales.dbo.orders 
WITH(READCOMMITTEDLOCK) WHERE OrderStatus = N'Unprocessed';

In this query, the SELECT statement will be blocked until UPDATE statements commit or roll back; therefore, the order will not be picked up until it is committed.

As stated earlier in this section, the new read committed using row versioning behavior functions only at the statement level, even when it is used within a multistep transaction. This has another advantage in that the update conflicts that are possible with the snapshot isolation level cannot happen with read committed using row versioning. Therefore, as a developer, you do not have to add additional logic to handle potential conflicts, which allows the new behavior to be activated without application change.

Read committed using row versioning should be the first choice when you are implementing a transaction that includes multiple SELECT statements that do not require (as a group) a uniform, consistent view of the database.

Snapshot Isolation

Sometimes having a uniform, consistent view of a database over multiple SELECT statements within a transaction is important. Examples are available in read-only applications, such as financial and human resources reporting in which it is important that totals, subtotals, and checksums be consistent—despite the fact that they can be calculated over several SELECT statements, and sometimes over several minutes. If the system dictates a uniform view for a longer period, the database snapshot is another tool, which is not discussed in this paper. If the data changes while the transaction is running, spurious data quality issues might arise as minor discrepancies creep into reports.

Developers who are building read-only systems that run multiple data aggregation and sorting reports against a database that is constantly changing should consider snapshot isolation if a consistent view of the data is required and if their database administrator has determined that database server capacity allows for the slight increase in database I/O. This class of application can be built either against the primary system or against a replica system that is built by using SQL Server 2005 transactional replication. Snapshot isolation will prevent the data writers (either other users or the replication distributor process) from being blocked by long-running read locks that are taken by the reporting application.

In addition to reports that require consistent data across queries in the same transaction, there are other examples where the developer might want to use snapshot isolation, such as:

• Filling data-driven dialog elements that are interrelated, again to avoid inconsistencies between pull-down lists, and other array controls
• In database administrator-centered live system status dialogs where system statistics are being correlated from data stored across the database.

Snapshot isolation development becomes interesting when the application must perform updates against the data that is read within the transaction. This is because the transactionally consistent view of the database, as of the start of the transaction, necessarily masks any conflicting updates. These updates are only discovered when the update is sent to the database and a conflict error is raised.

This side-effect of optimistic concurrency control (the application was optimistic that no other application users would update the same data) means that the developer must do a little extra work to ensure that the user's data is not lost. Much of this logic will already exist in systems that "disconnect" their data from the database, probably based on the row level incrementing timestamp value, to provide optimistic concurrency control. If this logic already exists in your application, read committed using row versioning might be a better choice to eliminate the blocking in the data-population phase and to maintain conflict detection without the requirement to maintain an active transaction in the database. It remains best practice to get in and out of the database as quickly as you can to avoid tying up resources, in this case the version store.

Snapshot isolation provides an automatic mechanism for detecting conflicts within a transaction that avoids the requirement to add timestamp columns or to make other schema changes. If a conflict is detected when the update is sent to the database, a SqlException is thrown and the current transaction is canceled.

Consider the following Microsoft Visual C# 2005 code fragments. Be aware that best practice dictates that try/catch logic would ordinarily be wrapped around the (missing) connection Open and around the Fill command, which is where the transaction is initiated:

// (Definition of a SqlConnection object skipped)
// Define a transaction object using the Snapshot Isolation Level.
SqlTransaction DT = sqlCon.BeginTransaction(IsolationLevel.Snapshot);
// Hook up Select and Update command handlers to the dataadapter
// Use the Snapshot transaction "DT"
SqlCommand selectCMD = new SqlCommand();
selectCMD.Connection = sqlCon;
selectCMD.Transaction = DT;
selectCMD.CommandText = "select MessageNo, MessageText " +
                        " from dbo.DialogText";
sqlDataAdapter1.SelectCommand = selectCMD;
SqlCommand updateCMD = new SqlCommand();
updateCMD.Connection = sqlCon;
updateCMD.Transaction = DT;
updateCMD.CommandText = "update dbo.DialogText " +
                        "set MessageText = @MessageText " +
                        "where MessageNo = @MessageNo";
updateCMD.Parameters.Add("@MessageText", 
                         SqlDbType.NVarChar, 
                         15, "MessageText");
updateCMD.Parameters.Add("@MessageNo", 
                         SqlDbType.SmallInt, 
                         2, "MessageNo");
sqlDataAdapter1.UpdateCommand = updateCMD;
// Now get the data
sqlDataAdapter1.Fill(dataSet1, "DialogText");

The previous Visual C# code uses ADO.NET and the SqlClient to populate a dataset with data from a SQL Server 2005 database in which the database administrator has enabled snapshot isolation. A typical Windows Forms application would now commit the transaction, disconnect from the database and present the data to the user. In the previous code, the transaction is left open to allow another transaction to change the data read into the dataset1 dataset.

The following code shows the return of the data to the database:

// Bind the data to the form's grid control
dataGridView1.DataSource = dataSet1;
dataGridView1.DataMember = "DialogText";
dataGridView1.AutoGenerateColumns = true;
// ...Time passes, conflicting changes take place
// User presses "update now" button:
try
{
sqlDataAdapter1.Update(dataSet1, "DialogText");
   dialogTrans.Commit();
   dataSet1.AcceptChanges();
}
catch (SqlException h)
{
   string errorMessages = "";
   for (int i = 0; i < h.Errors.Count; i++)
   {
      errorMessages += "Index #" + i + "\n" +
      "Message: " + h.Errors[i].Message + "\n" +
      "ErrorNumber: " + h.Errors[i].Number + "\n" +
      "LineNumber: " + h.Errors[i].LineNumber + "\n" +
      "Source: " + h.Errors[i].Source + "\n" +
      "Procedure: " + h.Errors[i].Procedure + "\n";
   }
   if (dialogTrans.Connection != null)
   {
      dialogTrans.Rollback();
   }
   MessageBox.Show(errorMessages, "Conflict Errors");
}
catch (Exception i)
{
// for general exceptions make sure the transaction is rolled back
   dialogTrans.Rollback();
}

In the second snippet, the dataset object is bound to a grid control on the form, where the user is free to make multiple updates to the data—in parallel other transactions have made conflicting changes. When users request that their changes be stored to the database, the dataset's changes are sent through the sqlDataAdapter as a series of database update statements—the first update statement that detects a conflict will cause an exception to be fired that rolls back the work, if no exception is fired then the transaction is explicitly committed by the dialogTrans.Commit() statement.

The exception handler above catches the SqlException thrown and formats an error message that could be sent to an application log—the conflict can be explicitly tested as SqlException.Errors[i].Number, where 3960 is the error number. Be aware that best practice dictates the whole error collection be checked in case of other, more severe errors.

Also be aware that the SqlException handler tests the dialogTrans SqlTransaction object to see whether it is still active (that is it has a connection to the database). If it is active, it is rolled back to ensure transactional consistency. If you try to commit or roll back an inactive object, you will see a SystemException that indicates a COM+ exception code of 0xE0434F49 (-532459699) with the text: "This SqlTransaction has completed; it is no longer usable."

When the conflict is detected, the application should inform the user that their changes have been rejected and offer them the opportunity to resubmit their changes under a new transaction.

Conflict detection, resultant transaction rollback, and then the requirement to resubmit the work show a decision is facing the developer: If the optimistic concurrency control mechanism is too optimistic, and data conflicts occur frequently, then pessimistic concurrency control might be a better choice. When you decide which method of transaction isolation to deploy within your application, you must balance the blocking caused by lock contention versus the additional work that is caused by a transaction rollback.

Minimizing Update Conflicts

Applications that are designed to work under optimistic concurrency provided by snapshot isolation must invest in conflict avoidance techniques. Although conflicts can be handled, it is best to avoid the cost of transaction rollback and retry if you can.

There are two main methods to reduce the risk of a conflict:

• Application design. When you are gating access to shared resources, you can "reserve" them for the updating application. The concurrency of the individual data item is reduced (others cannot access it while it is reserved), but the overall system concurrency is not affected. Examples of this technique are seen in online ticket-booking applications.
• Index design. Modified rows are uniquely identifiable by ensuring appropriate indexes are used in the access path of update queries.

Illustrating Concurrency Behavior

The following tables use the simple Customers/Orders/Items schema to show the behavior of the three isolation levels:

• New optimistic concurrency with snapshot isolation.
• Default read committed and read committed using row versioning.
• Read uncommitted.

Two client sessions are required:

• updater: Sets up the sample schema and inserts and updates data.
• reader: Runs under the four isolation levels to show their behaviors.

The simple schema is re-created at the start of each run by deletion and re-creation of the database. To drop a database requires that no sessions can be active in the database that is being dropped (including that of the dropping session).

READ COMMITTED

Preparation session:

1. Connect to an instance of SQL Server 2005 by using SQL Server Management Studio.
2. Open a query window.
3. Load the Setup_WithoutRowVersioning.sql script.
4. Execute the Setup_WithoutRowVersioning.sql script to create the [WithoutRowVersioning] database and populate a small sample schema.

Table 9

The previous sessions show the following positive behaviors of read committed:

• Committed updates are seen immediately.
• Locks can be used to serialize access to data; short lock waits (known as blocking) are usually acceptable to most systems.

The sessions also show the potential negative behaviors:

• Long lock waits can cause command time-out or lock time-out errors and increase the risk of deadlocking (mutually exclusive lock requests).
• Data can change while running a suite of queries that expect consistent data.

The following techniques are available for reducing the negative behavior of read committed:

• Make a read-only replica of the data for reporting purposes. Before SQL Server 2005, this can be done with a one-off backup/restore and continuous, periodic log shipping or replication (various techniques).
• To avoid lock waits, use read uncommitted. See the table in the following section.
• To avoid data changing in between queries, use repeatable-read or serializable transaction isolation levels in a single transaction that spans the report queries.

In SQL Server 2005, the previous techniques apply. SQL Server 2005 also provides new options, such as:

• Optimistic concurrency with snapshot isolation (across several queries.)
• Read committed using row versioning (for a single query.)
• Database mirroring.
• Database snapshots.

READ UNCOMMITTED

Preparation session:

1. Connect to an instance of SQL Server 2005 by using SQL Server Management Studio.
2. Open a query window.
3. Load the Setup_WithoutRowVersioning.sql script.
4. Execute the Setup_WithoutRowVersioning.sql script to create the [WithoutRowVersioning] database and populate a small sample schema.

Table 10

Using read uncommitted isolation is an effective way to obtain quick results from the database without blocking or being blocked by other users. We recommend this technique when data accuracy is not paramount and the use of transient data is acceptable.

Where nonblocking access to accurate data within a single query or across several queries is required, and offloading the query to a replica database is not possible or desirable, SQL Server 2005 introduces two transaction isolation schemes whose behavior is explored in the following section.

READ COMMITTED Using Row Versioning

Preparation session:

1. Connect to an instance of SQL Server 2005 by using SQL Server Management Studio.
2. Open a query window.
3. Load the Setup_UsingRowVersioning.sql script.
4. Execute the Setup_UsingRowVersioning.sql script to create the [UsingRowVersioning] database and populate a small sample schema.

Table 11

The previous sessions show how the new read committed using row versioning behavior can help when the system design requires a mix of update or long-running read activity. By obtaining the original data for changes that are uncommitted, the report writer can obtain data that is consistent within transaction boundaries without blocking or being locked by other users. This is different from read uncommitted behavior that will return data that might never be committed to the database, and that can give inconsistent views of the database.

The one negative behavior is that data is subject to change while the reader session runs. This can affect a set of related queries that require a transactionally consistent view of the database across statements. In versions of SQL Server earlier than SQL Server 2005, there were two isolation levels that delivered the consistent view:

• Repeatable read—locks the data read within the transaction.
• Serializable—locks the sets read within the transaction.

Both of these isolation levels shape application concurrency, and hence are usually unsuitable for scenarios in which a mix of multiple, random data changes must be coupled with long-running read transactions, especially when the impact of lock escalation (the term given to the run time escalation of multiple granular locks into fewer, less granular locks to conserve lock space) is taken into consideration.

Before SQL Server 2005, application designers would usually deliver the required consistency and concurrency by taking occasional snapshots of the data for reporting, or by implementing some form of row timestamp/datetime versioning within the application. This design pattern can be avoided by using the new snapshot isolation level that is shown in the following section.

SNAPSHOT

Preparation session:

1. Connect to an instance of SQL Server 2005 by using SQL Server Management Studio.
2. Open a query window.
3. Load the Setup_UsingRowVersioning.sql script.
4. Execute the Setup_UsingRowVersioning.sql script to create the [UsingRowVersioning] database and populate a small sample schema.

Table 12

This final session shows the multistatement behavior of a transaction that is running under snapshot isolation. The results were consistent across SELECT statements, although the updater session had successfully committed new data. This consistency is achieved without the potential negative impact of repeatable read and serializable.

Notice that the same script was used to demonstrate the new behavior of the read committed isolation using row versioning, and included the same BEGIN TRANSACTION and ROLLBACK TRANSACTION statements. However, because read committed applies at the statement level, there is no "memory" across statements.

The snapshot isolation level must be explicitly requested by using a SET TRANSACTION ISOLATION LEVEL statement, and then activated by both starting a transaction and accessing data.

Administrative Best Practices

From an administrative perspective, enabling row versioning–based isolation should be decided carefully, because the affect on performance might be negative when it is used to solve the wrong problems. If performance problems exist due to lack of proper indexing and query performance suffers, changing to row versioning probably would not solve this problem. If query performance suffers due to significant blocking caused by a mixed workload of readers and writers, read committed isolation using row versioning may be all that is necessary. If transactional consistency is required for long-running transactions, snapshot isolation might be necessary. However, each of these solutions incrementally puts a heavier load on tempdb.

Database-Level Settings

Because snapshot isolation is configured at the database level, administrators must enable snapshot isolation for each database that requires it. If cross-database transactions are tried with snapshot isolation and not all databases are configured for them, the snapshot transactions will fail unless a locking hint is used to override the default.

If all databases are configured for snapshot isolation, cross-database transactions will use a consistent snapshot across databases within one server instance. For example, assume there are two tables in two databases that are enabled for snapshot in the same server, and update transactions make the same changes to these two tables. The transaction under snapshot isolation never returns different values for the two tables.

Upgrade Issues

Although upgrading to SQL Server 2005 is dynamic and requires only internal changes to support row versioning, an extra 14 bytes per data row is required to store versioning data, irrespective of snapshot or read committed using row versioning being enabled. This data is added when the row is updated and, therefore, can lead to page splits (for tables that have clustered indexes) or row forwarding for heaps. Also changes will have to be made to all text/image data to allow row versioning.

None of these changes are made during upgrade but, instead, are made during later data row and text/image data modifications. It is important that database administrators who manage systems that have large binary object (LOB) data columns are aware that, for upgraded databases, the text/image columns will be modified dynamically to include versioning changes when any part of the LOB data is changed. All the text/image pages that belong to that particular text/image value will be changed. This operation can potentially be very expensive for large values that extend over many pages (due to page allocations, copying, and logging). You will only pay this overhead when you modify the text/image column value; there is no overhead if you only modify the parent data row.

Because text/image data modifications can be run in a minimally logged mode, database administrator should determine whether performing a separate and manual step as part of the upgrade to SQL Server 2005 would be beneficial. The change of fragment size can cause a lot of fragmentation to existing LOBs when there are lots of random, small updates to only pieces of the text/image values. Although random, small, partial updates to LOBs are not the common type of text/image manipulation that is performed, this overhead could be potentially expensive (both in terms of time and logging) in a live system. Before going live with SQL Server 2005, database administrators must consider adding a step (during upgrade) that will modify all text/image data to have this new format.

To perform this modification, complete the following steps:

1. Upgrade the database to SQL Server 2005.
2. In place upgrade—this is the easiest to perform because all components are updated in one upgrade process.
3. Install SQL Server 2005 on a new server and then use backup/restore to upgrade. SQL Server 2005 supports restoring SQL Server 2000 databases. For more complete information about how to successfully upgrade from SQL Server 2000, see the topic Upgrading to SQL Server 2005 [ http://msdn2.microsoft.com/en-us/library/ms144245.aspx ] in the SQL Server Books Online.
4. Verify or change the recovery model to either simple or bulk logged. We recommend the simple recovery model because a full database backup will be performed after you successfully complete this modification process.

   To perform an update to all text/image data values, executing a statement such as UPDATE ... SET a=a will not actually update the text data. Instead, use a statement sequence such as the following:

   DECLARE @ptrval binary(16)
   DECLARE @dataval CHAR(1)
   SELECT @ptrval = TEXTPTR(anytab.a) ,
              @dataval = SUBSTRING(anytab.a,1,1)
     FROM dbo.anytable AS anytab
     WHERE anytab.primarykey = 'unique value'
   UPDATETEXT anytab.a @ptrval 0 0 @dataval
   GO
   

   To do this for all records in a table, wrap the previous set of statements in a cursor loop that iterates through all values in the table.

5. Change the recovery model back to the recovery model you want, such as full recovery model.

Usage of Version Store in tempdb

The version store is maintained in tempdb. Because of this, tempdb is critical to the overall performance of the system and whether row versioning will even be possible for some long-running transactions. For example, if tempdb runs low on space, performance will degrade while the version store tries to clean up. The regular cleanup function is performed every minute in the background, trying to reclaim all reusable space from the version store. When tempdb runs out of free space, the regular cleanup function is called before auto-growth occurs. When the disk is full and auto-grow cannot increase the file sizes, the version store is first truncated to return space, and then if space pressure continues row versioning is stopped. If a query that is using row versions later encounters a record and wants to read an older version of the record that was not generated because of space constraints, the query fails. Updates and deletes do not fail; only queries that are requesting row versions fail, because after the version store fills, updates and deletes no longer generate row versions.

Note   Versions that are associated with short-running transactions, such as those found in OLTP workloads defined by the TPC-C benchmarks, might be written to memory and never to disk.

One alternative is to detect a long-running query or transaction and terminate it. By terminating the query you can help reduce the size of the version store. This operation can be automated by associating a script with the event (error number 3958) in tempdb. This is preferred error behavior for most applications. Otherwise, users might have many more transactions that fail because of out-of-space issues in the version store.

To ensure smooth running of a production system that is using row versioning-based isolation, the database administrator must allocate enough disk space for tempdb to make sure that there is always approximately 10 percent free space. When free space falls below 10 percent, system throughput will degrade because the version cleanup process will spend more time trying to reclaim space in the version store.

If I/O performance in tempdb becomes an issue, we recommend that the database administrator create more than one file for tempdb on different disks to increase I/O bandwidth. In fact, on multiprocessor computers, increasing the number of files to match the number of processors can often yield even greater gains. For more information, see the Knowledge Base article Q328551: Concurrency Enhancements for the tempdb Database [ http://support.microsoft.com/default.aspx?scid=kb;en-us;328551 ] .

If any application on a server creates unexpectedly large numbers of version store entries, the application can affect other applications by physically filling the shared tempdb database. Many versions or long-running transactions (not necessarily running under snapshot isolation) that prevent version cleanup can cause out-of-space related problems.

Sizing tempdb

If only read committed isolation using row versioning is required, sizing tempdb is not as important because the row versions are not likely to be held for long periods of time. However, long-running transactions (readers and also any writers) can cause problems when the transactions are excessively long. However, if you are running in snapshot isolation mode, the space requirement in tempdb increases. We recommend that you use the following formula to estimate the amount of space required in tempdb for running snapshot isolation queries.

To estimate how much space you need to have in tempdb, you must first consider that an active transaction must keep all its changes in the version store, so that a row versioning-based transaction that starts later will be able to get to the old versions. In addition, if there is an active snapshot transaction, all the version store data that was generated by previous transactions that are active when the snapshot starts must also be maintained until the last row versioning–based transaction that is using them finishes.

Size of Version Store = 2(Version store data generated per minute *

longest running time, in minutes, of your transaction)

Notice that the 2* multiplier reflects the possibility of two long-running transactions with a slight overlap, therefore, leading to twice the longest running transaction time before the versioned records can be released.)

Version store data that generated per minute for the system on behalf of active transactions can be compared to the log rows that are generated per minute. When you are sizing tempdb, remember that a log record will contain data changes and the snapshot of the whole row. By using Performance Monitor counters, you can see the amount of version store data that is generated per second. In a production system, you should consider monitoring these counters to fine-tune the size of tempdb.

Be aware that SQL Server 2005 online index build transactions are excluded from this calculation. These transactions do not directly affect the overall version store cleanup because their processing is handled differently from user transaction version management.

If you have enough disk space, always allocate more than your estimate to prevent potential space problems. When you are estimating size of tempdb, you must also consider the space requirements of DBCC CHECKDB, DBCC CHECKTABLE, index building, query, and other activities.

Other features that affect the size of the version store are triggers and MARS. In SQL Server 2005, triggers use row versions to generate inserted and deleted rows, instead of using scanning the log records.

Monitoring Version Store Activity

There are several ways to monitor version store activity, which include dynamic management views, Performance Monitor counters, and SQL Profiler Events. Each one offers a different perspective on the activity that is currently occurring on the system.

Dynamic Management Views

sys.dm_tran_active_snapshot_database_transactions

This dynamic management view returns a virtual table for all active transactions with a row-version–related sequence number. Only transactions that are running under snapshot isolation will include a sequence number. Read-only transactions in auto-commit mode and system transactions will not appear in this virtual table.

sys.dm_tran_active_snapshot_database_transactions returns the following columns.

Table 13

The table outputs data in the sequence of the transaction_sequence_number column. This shows transactions based on start time and, therefore, also elapsed_time_seconds to help you determine the transactions that are long-running.

To find the 10 longest (that is. earliest) transactions

SELECT TOP 10 atx.transaction_id, atxs.[name]
FROM sys.dm_tran_active_snapshot_database_transactions AS atx 
INNER JOIN sys.dm_tran_active_transactions as atxs
  ON atx.transaction_id = atxs.tran_id

To determine the transaction that has traversed the longest version chains

SELECT TOP 1 atx.* 
FROM sys.dm_tran_active_snapshot_database_transactions AS atx
ORDER BY atx.max_version_chain_traversed

sys.dm_tran_transactions_snapshot

This dynamic management view returns a virtual table for all active transactions with a row-version–related sequence number. This view returns a virtual table for the sequence number of transactions that are active when each snapshot transaction starts.

sys.dm_tran_transactions_snapshot returns the following columns.

Table 14

Performance Monitor Counters

The Windows 2003 Performance tool (System Monitor in Windows 2000) enables a database administrator to monitor a variety of system and SQL Server counters in a graphic interface, log the performance counters in a performance log, analyze the performance log, and define actions based on these events. There is also an API that allows for a database administrator to develop his or her own programs to access these counters and take proper actions.

The following table lists and describes the various counters.

Table 15

¹ From counters 3 and 4, the database administrator can predict the size requirement of tempdb and make space for it.

² From counters 5, 6 and 7, the database administrator would know from the active count and creation count when a system has reached steady state. A high truncation rate can indicate that tempdb is or was under space stress from other applications that are also using tempdb, and could be a cue to the database administrator to increase the size of tempdb.

³ From counters 11 and 12, the database administrator can determine the number of snapshot transactions that are read-only.

⁴ From counters 12 and 13, the database administrator can determine the total number of transactions that cause version generation, because all snapshot transactions that do updates result in version generation.

From these counters, the database administrator knows to what extent the versioning feature is being used and how it is being used. All the previously described counters are server-wide and are grouped in a new Performance Monitor Object called SQLServer: Transactions.

Conclusion

Because snapshot isolation influences both administration and development aspects of a system, it is important to make sure that all aspects are understood. If database administrators unnecessarily allow snapshot isolation where long-running transactions occur and modifications are constant, users can experience problems when they commit changes if tempdb is not sized appropriately. Additionally, if developers expect read committed isolation to be set and it's not, data inconsistencies might occur undetected. Make sure to review all of the associated resources and participate in the beta newsgroups for more information.

For More Information

http://msdn.microsoft.com/sql/ [ http://msdn.microsoft.com/sql/ ]

SQL Server Books Online

For more information, see the following topics in SQL Server Books Online:

• Understanding Row Versioning-Based Isolation Levels [ http://msdn2.microsoft.com/en-us/library/ms189050.aspx ]
• Adjusting Transaction Isolation Levels [ http://msdn2.microsoft.com/en-us/library/ms189542.aspx ]
• Using Row Versioning-based Isolation Levels [ http://msdn2.microsoft.com/en-us/library/ms179599.aspx ]
• Upgrading to SQL Server 2005 [ http://msdn2.microsoft.com/en-us/library/ms144245.aspx ]

Knowledge Base Articles

Q328551: Concurrency Enhancements for the tempdb Database [ http://support.microsoft.com/default.aspx?scid=kb;en-us;328551 ]

Additional Reading

Generalized Isolation Level Definitions [ http://research.microsoft.com/~adya/pubs/icde00.pdf ]

A Critique of ANSI SQL Isolation Levels [ http://research.microsoft.com/research/pubs/view.aspx?tr_id=5 ]

Newsgroups and Forums

Discussion groups for SQL Server 2005 can be found at the following locations:

• Forums: http://forums.microsoft.com/MSDN/default.aspx?forumgroupid=19&siteid=1 [ http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1 ]
• Newsgroups: Register the msnews.microsoft.com news server in Microsoft Outlook Express.