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

SQL SERVER - How to Know Transaction Isolation Level for Each Session? isolationlevel-800x99

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

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

, , , ,
Previous Post
SQL SERVER – Parallel Redo on AlwaysOn Secondary – DIRTY_PAGE_TABLE_LOCK
Next Post
SQL SERVER – Script to Identify Memory Used By Each Database

Related Posts

8 Comments. Leave new

  • you could use sys.dm_exec_sessions. this will give you a better picture

    Reply
  • I blindly use those commands, ll correct in my upcoming queries

    Reply
  • Deeptendra Daityari
    March 6, 2019 1:19 am

    Hello Pinal,

    I came across an Isolation level called Chaos while studying a SSIS package property in my project. Can you please help me in understanding how different is it from other isolation levels? Also do we have any scenarios in real time where we might need to establish such isolation level?

    Thanks

    Reply
  • Chaitanya Yanamadala
    March 20, 2019 2:37 pm

    How to identify this for Compatability 80? as currently some of our apps are on 80 compatability

    Reply
  • Hi,

    is there a way to set isolation to some set of users by default or for all select statements by default. The use case is lot of users are writing queries and they are blocking inserts or updates from ETL on database, instead of forcing all users to use nolock or set isolation level it will be great if this can be controlled at server level or user level or for all select statements. Highly appreciate your response, thanks!

    Reply

Leave a Reply

Menu