Finding Serializable Queries

  set NOCOUNT ON;
  SELECT 
  cast(CASE s.transaction_isolation_level 
		WHEN 0 THEN 'Unspecified' 
		WHEN 1 THEN 'ReadUncommitted' 
		WHEN 2 THEN 'ReadCommitted' 
		WHEN 3 THEN 'Repeatable' 
		WHEN 4 THEN 'Serializable' 
		WHEN 5 THEN 'Snapshot' END as varchar(100)) AS IsolationLevel 
 , DB_NAME(r.database_id) as DbName
 , cast(substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)  else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as varchar(max)) as CurrentText
  , s.[host_name]
  , s.program_name
  , s.login_name
  , cast(h.text as varchar(max)) FullQuery
  INTO #queryResults
  from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r
  on s.session_id =r.session_id and s.last_request_start_time=r.start_time
  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
  where is_user_process = 1
  and s.transaction_isolation_level  <> 2
  and s.session_id <> @@SPID; 

GO
	INSERT INTO #queryResults
	  SELECT 
	  CASE s.transaction_isolation_level 
			WHEN 0 THEN 'Unspecified' 
			WHEN 1 THEN 'ReadUncommitted' 
			WHEN 2 THEN 'ReadCommitted' 
			WHEN 3 THEN 'Repeatable' 
			WHEN 4 THEN 'Serializable' 
			WHEN 5 THEN 'Snapshot' END AS IsolationLevel 
	 , DB_NAME(r.database_id) as DbName
	  , substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)  else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as CurrentText
	  , s.[host_name]
	  , s.program_name
	  , s.login_name
	  , h.text FullQuery
	  from sys.dm_exec_sessions as s inner join sys.dm_exec_requests as r
	  on s.session_id =r.session_id and s.last_request_start_time=r.start_time
	  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
	  where is_user_process = 1
	  and s.transaction_isolation_level  <> 2
	  and s.session_id <> @@SPID;

  	waitfor delay '00:00:00.2'

GO 500

select DISTINCT *, count(*) over (partition by FullQuery, program_name, login_name, host_name) as timesRun
from #queryResults;


DROP TABLE #queryResults