Using RCSI with Kronos

  • Article

Author:

This article is written by our contributing author Ken Lassesen. His bio can be found here .

Recently, I observed the impact that turning on RCSI had on a Kronos installation, and I was impressed by the reduction in blockages that were created by super-users' ad hoc queries.

RCSI stands for read-committed snapshot isolation. RCSI causes SQL Server to maintain a copy of any record being modified in tempdb that is used for reads if the record has a lock on it. This means that the read query does not have to wait for the release of the lock.

If you are doing reporting off a table that is being updated, the reading of the table has to wait until any updates happen. Then, a read lock is placed on the table, the table is read, and then further updates happen. The problem is simple: With a busy application like time reporting and a big table, you may get a spike of pending transactions that are waiting for the read lock to be released.

Typically, you can solve this type of problem by building out a data warehouse, a data mart, or some other reporting database. This technique will not work for same-hour reporting needs, because building out a reporting database can take hours or days.

If consistent near-real-time data is sufficient for your reporting requirements, RCSI is the way to go (assuming that you have the right resources available). This is especially helpful when you have lots of recent MBA graduates trying to do ad hoc data mining on an operational data store to impress their managers.

RCSI does its magic by putting a copy of the records that are being modified in tempdb before an update happens. When your read starts, SQL Server sees that there is a lock on rows, and instead of waiting for the lock, SQL Server reads the copy in tempdb into a version store. The reading is a rolling read of the extents. This can also be described as row versioning, because the rows are stamped with a transaction sequence number (XSN), which is not a log sequence number (LSN). No read locks are placed on the data. Writing threads do not block reading threads. What is the cost? Lots of space in tempdb may be used (size delta depends on the intensity of the changes).

Technically, RCSI is the only optimistic isolation level that is available with SQL Server and is a Microsoft extension of the SQL-92 isolation levels. It has the following characteristics:

 

  • It prevents dirty reads (i.e., it will not read data that has not been committed).
  • Phantom reads may occur, especially with HASH tables (if an update moves a record from one extent to another extent, the same record may be read twice).
  • Reads may not be repeatable (because the reads are rolling, a repeat may read the different committed data).
  • It prevents update conflicts (you will not have a record disappear; you may get the same record twice , i.e., phantom reads).

That is the geek-speak on RCSI. Next, I'll show you some charts that illustrate the impact of RCSI.

 

RCSI Impact on Some Counters

I generated the charts below from data that was captured in the SQL Server management data warehouse. The method I used was first restoring an existing populated database and then rerunning a base operational load with an optional ad hoc load in one of three variations:

 

  • Base (RCSI off) with no ad hoc queries
  • Base (RCSI off) with ad hoc queries
  • Base (RCSI on) with ad hoc queries

The first charts deal with ad hoc queries with and without RCSI.

The following chart shows how the queue length drops because requests do not have to wait until locks are released. The horizontal axis in all the charts is the time in minutes.

If you look at the read, you see a marked drop from 3.5 to 2. (Remember that there are reads happening from the operational load involving updates.)

You can also see that the idle time (the time that the processor is waiting for I/O to complete) drops.

The next charts show all three scenarios to illustrate how the ad hoc load impact can be greatly reduced.

The good thing is that the impact of RCSI on the ad hoc queries is almost the complete elimination of the impact.

 

How Do You Change It?

The commands are simple:

 ALTER DATABASE MyDatabase
    SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
    SET READ_COMMITTED_SNAPSHOT ON

 

Summary of Blockage Reduction from Using RCSI

I looked at a very specific scenario here: ad hoc queries against a very active operational data store. These queries are designed by people that have just enough knowledge to be dangerous to operational performance. The best practice is to query historical data via a database that is built from the operational stores. But, that best practice is not always viable, for example, same-day manpower shift balancing based on today's data. RCSI will lessen the impact of such queries.

The key resource required for this is plenty of space in tempdb following best practices. The key points are:

  • tempdb is on your fastest random read drives (as determined by SQLIO and other tools).
  • tempdb transaction log is on your fastest sequential read drives (as determined by SQLIO and other tools).
  • Neither tempdb nor tempdb transaction log drives are shared with each other or with any data file.

Further Reading

For more information, see the following resources: