SQL Server: Find Transaction Level of Previous Historical Queries-CodePudding

How do I find the Transaction Level of Queries that have Previously run historical in SQL Server? This is only for currently running queries .

https://blog.sqlauthority.com/2018/06/07/sql-server-how-to-know-transaction-isolation-level-for-each-session/

SELECT session_id, start_time, status,
total_elapsed_time,
CASE transaction_isolation_level
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
ELSE 'Unspecified' END AS transaction_isolation_level,
sh.text, ph.query_plan
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph

Currently using Sql server 2019.

CodePudding user response：

This is not possible under the standard configuration.

Your only options are

create an Extended Event session that tracks it
enable Query Store and read the information from the plans
use the Profiler to setup a trace