How to monitor transaction isolation level changes in SQL Profiler or in any other tool

Question

I've successfully only been able to see transaction isolation level events in the Audit Login event. Are there any other ways to monitor the transaction isolation level changes using SQL Profiler or using some other tool? The reason I ask is because SQL Profiler does not seem to be able to output the events in the right order or it skips events because when setting the IsolationLevel to Serializable in my app it still shows transaction isolation level read committed.

Example Audit Login in SQL Profiler:

-- network protocol: Named Pipes
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level serializable

Highlight

Answer 1

I am afraid there isn't one.

Even if there was one, what would you want to see where multiple tables were queried in a join and one or more had NOLOCK which is read uncommitted?

The profiler reports queries at the statement level not the table level so you would have a mix of transaction isolation levels (this is true of the profiler and extended events)

The best you could do is to manually parse the statement start (batch and procedure) and look for the set transaction isolation level.

ed

Cabinet

For all queries in SQL Profiler would be nice. But in this particular case I would like to see the isolation level in a simple db.Orders.Find(1) query that it is in a BeginTransaction(IsolationLevel.Serializable) using Entity Framework 6. – Highlight

I don't think you will get it because even though you are running a statement (SELECT * FROM Orders probably) - to SQL that could be a view with 10 tables underneath with NOLOCK on some tables so different isolation levels – Cabinet

Answer 2

I found this question while trying to determine why Entity Framework was connecting to our database with the serializable transaction isolation level. As the original answer stated, there is no straightforward way to collect this data either from SQL Trace or Extended Events.

Below is a query to repetitively collect the transaction isolation levels from the sys.dm_exec_sessions and sys.dm_exec_requests DMVs. The tables will grow quickly but are useful for brief tracking of isolation levels from external applications:

SET NOCOUNT ON 

IF OBJECT_ID('dbo.Query', 'U') IS NOT NULL DROP TABLE dbo.Query 
IF OBJECT_ID('dbo.SessionData', 'U') IS NOT NULL DROP TABLE dbo.SessionData 
GO 

CREATE TABLE dbo.Query 
(
     SessionID INT
    ,StartTime DATETIME
    ,IsolationLevel INT
    ,IsolationLevelName VARCHAR(20)
    ,ObjectName VARCHAR(300)
    ,StatementText VARCHAR(MAX)
    ,QueryPlan XML
) 

CREATE TABLE dbo.SessionData 
(
     SessionID INT
    ,LoginTime DATETIME
    ,IsolationLevel INT
    ,IsolationLevelName VARCHAR(20)
    ,ProgramName VARCHAR(300)
    ,LoginName VARCHAR(300)
) 

WHILE 1=1 
BEGIN 

    INSERT INTO dbo.Query 
    SELECT 
         SessionID = req.session_id
        ,StartTime = req.start_time
        ,IsolationLevel = req.transaction_isolation_level
        ,IsolationLevelName = 
            CASE req.transaction_isolation_level 
            WHEN 1 THEN 'Read Uncomitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable Read'
            WHEN 4 THEN 'Serializable' 
            WHEN 5 THEN 'Snapshot'
            ELSE 'Unknown' END 
        ,ObjectName = OBJECT_NAME(st.objectid, st.[dbid]) 
        ,StatementText = SUBSTRING
          (REPLACE
            (REPLACE
              (SUBSTRING
                (ST.[text]
                , (req.statement_start_offset/2) + 1
                , (
                   (CASE statement_end_offset
                      WHEN -1
                      THEN DATALENGTH(st.[text])  
                      ELSE req.statement_end_offset
                      END
                        - req.statement_start_offset)/2) + 1)
           , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  
           , QueryPlan = qp.query_plan
    FROM sys.dm_exec_requests req 
    CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) st
    CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) qp
    WHERE ST.[text] NOT LIKE N'%INSERT INTO dbo.Query%'

    INSERT dbo.SessionData 
    SELECT
         SessionID = session_id 
        ,LoginTime = login_time
        ,IsolationLevel = transaction_isolation_level
        ,IsolationLevelName = 
            CASE transaction_isolation_level 
            WHEN 1 THEN 'Read uncomitted'
            WHEN 2 THEN 'Read committed'
            WHEN 3 THEN 'Repeatable read'
            WHEN 4 THEN 'Serializable' 
            ELSE 'Unknown' END 
        ,ProgramName = [program_name]
        ,LoginName = login_name 
    FROM sys.dm_exec_sessions 
    WHERE security_id <> 0x01
    AND session_id <> @@SPID

END

SELECT * 
FROM dbo.Query 

SELECT * 
FROM dbo.SessionData 

Thrilling

"why Entity Framework was connecting to our database with the serializable transaction isolation level" - Did you find answer? – Cosette