Our databases are currently suffering from lots of blocking. According to our monitoring tool (DPA), the blocking is being caused by SELECT statements which I thought was odd. We currently have "Is Read Committed Snapshot On" set to TRUE on the databases but we have "Allow Snapshot Isolation" set to FALSE.
The amount of blocking in our application is causing significant availability issues for our company which is highly problematic. At this point, I believe a few dirty reads is worth getting rid of the blocking but I don't really understand the implications of changing these settings. I have read the MSDN articles but am still a bit lost.
Our application is written in .NET using Entity Framework and our developers are not very strong at performance tuning and have limited database knowledge.
I'm looking for advice (consulting) on how to proceed.
You don't need "Allow Snapshot Isolation" set to TRUE since you already have RCSI enabled. If you are seeing SELECT statements that are blocking writes - then I would look at the connection properties from the application - or the actual code.
If the connection is forcing read committed isolation or the code itself is setting read committed - that will overwrite RCSI and your select statements will act the same way they would with RCSI turned off.
From what I have seen - .NET Entity Framework defaults to serializable and you need to explicitly set the isolation to snapshot.
Thanks so much Jeff. Is there any way to find out what isolation level is being used for the transactions? I don't believe this is possible with SQL Profiler.
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
I'm seeing all "ReadCommitted" and a few "Serializable". Does that tell me that Entity Framework is overriding the RCSI setting at the database level? Shouldn't I be seeing lots of "Snapshot" sessions?
I believe the snapshot isolation in sys.dm_exec_sessions is when you explicitly set the isolation level (ALLOW_SNAPSHOT_ISOLATION is true and you SET TRANSACTION ISOLATION) - and not RCSI. RCSI is read committed snapshot and the sessions transaction isolation level will appear as read committed.
I am not a .NET programmer - and I do not use entity framework, so I really cannot answer how it is implemented. From what I can see though - it all depends on how the code is written. If you are utilizing transactions in entity framework, the default isolation level for the transaction is serializable (hence - the sessions that have 4 in the transaction_isolation_level column).
If you have serializable transactions - they will block reads and writes until they are completed.
The default isolation level for EF6 is READ COMMITTED. It was SERIALIZED in prior versions. I don't understand why I am seeing SERIALIZED SELECT statements being run. The devs have scoured the code and they are not explicitly setting it to that. Is it possible that SQL is escalating the isolation mode for some reason?
Might have figured it out. We use third party software called Hangfire as our queuing system. It uses SQL Server. It executes our scheduled queries. It seems to be wrapping our queries in SERIALIZED transactions.