Isolation level read commited snapshot

Question

Isolation level read commited snapshot

TheBI

SSC Veteran

Points: 215
More actions
December 10, 2014 at 12:35 pm

#299399

Hi All
I have several databases set to read commited snapshot isolation level. Tempdb is configured according to best practices, but I don't see it's used much.
The application uses EF6, and it calls the stored procedures in the following way
Database.ExecuteSqlCommandAsync("exec dbo.spSync_MatchesByTenant @MatchesGroup, @TenantId", parameter, licenseIDParam);
Is it possible the code does not use the read commited snapshot isolation level of the database?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If the isolation level is enabled then you get the TempDB hit even if sessions are requesting other isolation levels. It's not necessarily going to be heavily used, depends on the length of the statements you run and what the read/write ratio is like.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheBI SSC Veteran Points: 215 More actions · Answer 2

namesnapshot_isolation_state_descis_read_committed_snapshot_on

F1SBON1

I suspect the SP called from .net is called with another isolation level (serializable or read commited)

in XE session i'm catching

xml_report < deadlock > < victim - list > < victimProcess id = "process4a2a85848" / > < / victim - list > < process - list >

< process id = "process4a2a85848" taskpriority = "0" logused = "31544" waitresource = "PAGE: 23:1:1461 " waittime = "236" ownerId = "5145434319"

transactionname = "user_transaction" lasttranstarted = "2014-12-10T19:47:02.110" XDES = "0x1431c74c90" lockMode = "S" schedulerid = "23"

kpid = "9892" STATUS = "suspended" spid = "403" sbid = "2" ecid = "0" priority = "0" trancount = "2" lastbatchstarted = "2014-12-10T19:47:02.110"

lastbatchcompleted = "2014-12-10T19:47:02.110" lastattention = "1900-01-01T00:00:00.110"

clientapp = ".Net SqlClient Data Provider" hostname = "xxxxx" hostpid = "6000" loginname = "xxxxx" isolationlevel = "read committed (2)"

xactid = "5145434319" currentdb = "10" lockTimeout = "4294967295" clientoption1 = "673185824" clientoption2 = "128056" >

< executionStack > < frame procname = "F1SB.dbo.spSync_LiveMatches_F1SB_39_MAGICSERVICE" line = "48" stmtstart = "14606" stmtend = "18184" sqlhandle =

"0x03000a00a6bf6e5be1293201f9a3000001000000000000000000000000000000000000000000000000000000" >

MERGE [F1SB].dbo.LiveMatchOdds AS T

USING (

…

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

With read committed snapshot on, any request for read committed actually gets read committed snapshot. It's not a separate isolation level, it's read committed done with row versions.

Anything requesting serialisable will get serialisable.

You'll still get TempDB overhead from all data modifications even if every single session requests serialisable because the row versions have to be there in case there's a query running under read committed. You always pay the cost, even if you get no benefit because of requesting other isolation levels.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheBI SSC Veteran Points: 215 More actions · Answer 4

Too many deadlocks and timeouts?

and read commited snapshot is supposet to eliminate it

What can be done more?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

TheBI (12/11/2014)
Too many deadlocks and timeouts?

???

and read commited snapshot is supposet to eliminate it

There's no isolation level that will eliminate timeouts. Timeouts are a result of queries running longer than the application is willing to wait. By default in .Net that's 30 seconds. Identify the slow queries, tune them

Any of the snapshot isolation levels prevent reader-writer deadlocks. Other forms of deadlock are still possible, and since you have apps requesting serialisable they're very likely to deadlock.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheBI SSC Veteran Points: 215 More actions · Answer 6

Thanks a lot,

I'm in a communication with the developers and see that they use asynchronous parallel calls of the above sp for merging data. It's another reason for the deadlocks, i suppose.

Will keep on here, inform you how the situation will change after the changes done in the code...