SQL SERVER – How to Know Transaction Isolation Level for Each Session?

During recent, Comprehensive Database Performance Health Check, I had a very interesting scenario. I was hired by a customer to identify their system’s unexpected performance bottlenecks. I do these for a living so I am usually pretty comfortable. However, this customer was so unique that after their permission I am sharing you their story of transaction isolation level.

Our customer was an outsourcing center for all of their development work. Just like a typical outsourcing center, their both of team did not have proper coordination between them. They were all over the place with the coding standard. Additionally, the developers who were building the application were good with programming but really not sure how to write SQL Server code which is scalable. Developers had no clue how SQL Server Transaction Isolation Level works and they had started to use them for their queries.

Every developer before writing query was taking a guess about the transaction isolation level and was specified at the top of the query. Honestly, the best practice is to use a single transaction isolation level for your entire database and use one or two transaction isolation level for a batch of queries where required. However, in my current customer’s case, every query was using different isolation level and it had created all the performance problems.

Here is the syntax for different isolation level in SQL Server.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Developers were running any of the above scripts when they were running the query based on their understanding of the system, which is extremely bad practice.

Here is the query which we ran to identify how many different queries are running at my customer’s place and what is the isolation level for each of the query.

SELECT session_id, start_time, status,
total_elapsed_time,
CASE transaction_isolation_level
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
ELSE 'Unspecified' END AS transaction_isolation_level,
sh.text, ph.query_plan
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph

Please note that above query will only display the queries which are currently running on your system.

Reference: Pinal Dave (https://blog.sqlauthority.com)