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)
you could use sys.dm_exec_sessions. this will give you a better picture
Please share query to help others.
I blindly use those commands, ll correct in my upcoming queries
That’s great Prakash. I am sure this would help you in long term.
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?
How to identify this for Compatability 80? as currently some of our apps are on 80 compatability
Please upgrade your compatibility level to latest level for optimal performance.
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!