Finding Your Isolation Level

Often when I am troubleshooting SQL Server or Azure SQL Database performance issues which I believe to be related to concurrency, I get curious about what isolation level is being used. But how does one find out the isolation level a query is executing with? There are actually several locations that may need to be checked.

Isolation Level Hierarchy

Isolation level settings can be configured in three different places:

• Database (this is only a default, with limited choices)
• Connection (affects all queries using this connection)
• Statement (affects only a specific query)

Database Isolation Levels

Isolation level settings for the entire database exist only as a default. All connections created will inherit the database's isolation level. Furthermore, a database only has two possible isolation levels:

• read committed
• read committed snapshot (also known as "RCSI")

There are four additional isolation levels (read uncommitted, repeatable read, serializable, snapshot) which can only be selected at the connection or statement level. In SQL Server and Azure SQL Database Managed Instance, the default database isolation level is read committed. In Azure SQL Database, the default is read committed snapshot.

To find the isolation level setting for a database, query the sys.databases view:

1SELECT name, is_read_committed_snapshot_on
2FROM sys.databases
3WHERE name = DB_NAME();

If is_read_committed_snapshot_on equals 0, then the database is in read committed. Otherwise it is RCSI.

Connection Isolation Levels

By default, a connection inherits the isolation level of the database it connects to. Connections can also have their isolation level explicitly changed using the SET TRANSACTION ISOLATION LEVEL statement.

To check the status of the current connection, run DBCC USEROPTIONS. This returns properties about the connection, including its isolation level.

Statement Isolation Levels

Isolation levels can also be set for individual statements. This is done by specifying a table hint. If a statement joins multiple tables, each table will require its own hint, or else operations on that table will use the connection's isolation level instead. For example, if joining two tables and desiring the serializable isolation level for this statement only:

1SELECT a.Column1, b.column2
2FROM TableA a WITH(SERIALIZABLE)
3JOIN TableB b WITH(SERIALIZABLE);

To check the isolation level(s) present in the statement, the simplest way is to look at the T-SQL itself and see if any hints are present. If not, it is operating at the isolation level of the connection.

Another option, especially when investigating performance issues, is to use Extended Events to capture properties of queries being executed. One such example of this is the blocked process report, which Erin Stellato of SQLskills has a great tutorial for. This extended event session captures the isolation levels of both the blocked and blocking processes, and reflects the actual isolation level being used (as opposed to just showing settings for the database or connection as discussed above.) While extended events are the most work, they also produce the best results. The blocked process report's output is as follows:

Summary

Isolation levels exist in a hierarchy with 3 tiers in SQL Server, and depending on the query in question you may end up checking in multiple places to find which isolation level is being used.

Isolation levels are incredibly important both on-premises and in the cloud, and have tremendous influence over both the performance and correctness of query results. If you would like to learn more about isolation levels, I have an entire presentation on the topic.