1

Suppose that I've turned on RCSI and my server is doing just fine; There have been no spikes in resource usage and the increased load on tempdb has been minimal. This is all good, but how can I tell if the change has caused my users problems? For example, for the purpose of finding issues caused by RSCI that Read Committed would have not caused are there any:

  • Error messages that I can track?
  • Diagnostic scripts I can run?
  • Metrics to watch?
  • Logging the application developers can put in their own code?
  • Specific user complaints I should be on alert for?

I'm quite happy to use RCSI, but the purpose of this question is to find actionable ways to track the impact of turning it on after the fact.

1

1 Answer 1

5

scope

This is a difficult question to answer and account for all possible issues, because just about every workload has unique characteristics that require special attention. Some can run flawlessly using optimistic isolation levels, and others require intervention in certain areas.

In most cases, a single isolation level is not 100% right for any workload, but developer oversight, naivety, etc. can result in incorrect isolation levels being used. Whether that causes any problems or not generally comes down to luck, or lack of concurrency.

Error messages that I can track?

For RCSI, the only errors I've ever encountered have been related to the version store filling up/running out of space. You can get some sense of what to look for by searching through available error messages:

SELECT
    m.*
FROM sys.messages AS m
WHERE m.language_id = 1033
AND   m.text LIKE '%[vV]ersion [sS]tore%'
ORDER BY
    m.message_id;

If you were talking about Snapshot Isolation, your bigger concern would be update conflicts:

SELECT
    m.*
FROM sys.messages AS m
WHERE m.language_id = 1033
AND   m.message_id = 3960
ORDER BY
    m.message_id;

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table '%.*ls' directly or indirectly in database '%.*ls' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

But that's a totally different scenario.

Diagnostic scripts I can run?

That depends on what you're attempting to diagnose. Most commonly, that would be "what's using the version store, and how much is it using?"

Here's a code fragment from sp_PressureDetector, slightly reformatted to respect answer length:

SELECT
    free_space_gb =
        CONVERT(decimal(38, 2), SUM(d.unallocated_extent_page_count * 8.) / 1024. / 1024.),
    user_objects_gb =
        CONVERT(decimal(38, 2), SUM(d.user_object_reserved_page_count * 8.) / 1024. / 1024.),
    version_store_gb =
        CONVERT(decimal(38, 2), SUM(d.version_store_reserved_page_count * 8.) / 1024. / 1024.),
    internal_objects_gb =
        CONVERT(decimal(38, 2), SUM(d.internal_object_reserved_page_count * 8.) / 1024. / 1024.)
FROM tempdb.sys.dm_db_file_space_usage AS d
WHERE d.database_id = 2;

If you're on SQL Server 2017 or better, you can check using something like this:

SELECT
    d.name,
    reserved_page_count,
    reserved_space_gb = 
        CONVERT(decimal(38, 2), reserved_space_kb / 1024. / 1024.)
FROM sys.dm_tran_version_store_space_usage AS t
JOIN sys.databases AS d
  ON t.database_id = d.database_id;

Note that these only monitor for version store size in tempdb. If you're on SQL Server 2019+ and using Accelerated Database Recovery, you'll want to monitor the Persistent Version Store.

SELECT
    d.name,
    persistent_version_store_size_gb = 
        CONVERT(decimal(38, 2), persistent_version_store_size_kb / 1024. / 1024.)
FROM sys.dm_tran_persistent_version_store_stats AS t
JOIN sys.databases AS d
  ON t.database_id = d.database_id;

Metrics to watch?

On top of version store size, you may also want to look at long running transactions using the Version Store. Something like this may give you enough details:

SELECT
    at.transaction_id,
    at.name,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state,
    dt.is_snapshot,
    dt.first_snapshot_sequence_num,
    dt.max_version_chain_traversed,
    dt.average_version_chain_traversed,
    dt.elapsed_time_seconds
FROM sys.dm_tran_active_transactions AS at
OUTER APPLY
(
    SELECT TOP (1)
           dt.*
    FROM sys.dm_tran_active_snapshot_database_transactions AS dt
    WHERE dt.transaction_id = at.transaction_id
    ORDER BY
        dt.elapsed_time_seconds DESC
) AS dt
ORDER BY
    at.transaction_begin_time DESC;

In general, you'd want to look for long running transactions that may be preventing version store cleanup.

Logging the application developers can put in their own code?

I can't think of anything that would be useful.

Specific user complaints I should be on alert for?

You would want to keep an eye on user complaints about incorrect results, or duplicative entries.

Trying to be concise here, there may be areas of the application that are sensitive to concurrency, where blocking is needed for result correctness that read committed locking provides.

I'd suggest looking at examples in these posts by Paul White:

In particular, the details of just what consistency guarantees you're currently getting with read committed locking as the isolation level, and how data modifications can get odd results under read committed snapshot isolation. Queries that are sensitive to stale reads can have a READCOMMITTEDLOCK hint applied, as shown in the sequence table article.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.