asked
read uncommitted & blocking
SQL Server 2012 EE sp1 FCI running on VMware guests Window servers 2008 r2 EE. The application is dependent on DTC and is using .Net Have made some changes on VMware to optimize memory and storage. Although I don’t want to rule anything out. A couple times a week we see massive blocking occurring 500 + SPIDS blocking. All with lck_m_s waits. Seems to be 4 different queries involved. Often the lead blocker has isolation level read uncommitted but the blockees are read committed. This is per the blocking report in performance dashboard. Sometimes there is high CPU other times not. The only thing that seems to cure this is time. Wait long enough and the blocking drops to 10 to 20 SPIDS and processing goes on normally. My question is regarding read uncommitted. I did not think that would cause shared locks so can this really be the lead blocker? Could it maybe have to do with the distributed transaction having to send results to Web servers?
This site works by you voting. For all helpful answers, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark.
You could try to use "READ COMMITTED SNAPSHOT". This can solve some blocking issues as any updated records that are in a transaction are saved in the version store. Any other session that tries to read from the table will read the value from the version store. Using read committed snapshot will increase the load on tempDB database.
16 People are following this question.
