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://darkslategrey-bat-805937.hostingersite.com)
