CODEWITHSUNDEEP
sql-serversql-server-2008entity-frameworkado.netsql-server-profiler
Lawrence Wagerfield
Lawrence Wagerfield

Reputation: 6611

SQL Profiler not displaying all ADO.NET operations

I have the following code which creates a serializable transaction. The application works as expected:

using (var tx = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable, Timeout = new TimeSpan(0, 2, 0) }))
{
  // Modify database using EF.
  tx.Complete();
}

However, profiling the database is causing me some confusion.

// Start of transaction (and the first query). Login has invalid ISO LEV.
Audit Login       ... set transaction isolation level read committed ...
SQLTransaction    0 - Begin
RPC:Completed     exec sp_executesql N'SELECT TOP (1)... 

// Second query in transaction. Login now has valid ISO LEV.
Audit Logout
RPC:Completed     exec sp_reset_connection
Audit Login       ... set transaction isolation level read serializable ...
RPC:Completed     exec sp_executesql N'SELECT TOP (1)...

Since I can see the first query IS actually executed under serializable (I can infer this from the locks its acquiring), the only rationale I can draw is that Audit Login simply states the ISO level of the SPID at the point of logging on, and not what it's changing it to.

If that's the case, then where's the event which triggers the ISO change? I have turned on ALL events and can't see anything...

Upvotes: 2

Views: 663

Answers (1)

Andomar
Andomar

Reputation: 238086

Changing the transaction isolation level is not an RPC:Completed event.

Double check if you are monitoring the SQL:BatchStarting and SQL:BatchCompleted events.

Upvotes: 1

Related Questions