select cast (query_plan as xml) as All_Plan, r.plan_handle, * 
	from sys.dm_exec_requests r
	outer apply sys.dm_exec_sql_text(r.sql_handle) as s
	outer apply sys.dm_exec_text_query_plan (r.plan_handle,0,-1) p
	 where 1=1  
		--and r.sql_handle is not null 
		and session_id = 416

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan, *
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
--where text like '%LOAD_INCR_BS%'  -- имя процедуры 
where text like '%select ID_SCENARIO,ID_DATE%' -- или часть запроса

	select cast (query_plan as xml) as All_Plan, text, r.plan_handle, * 
	from sys.dm_exec_requests r
	outer apply sys.dm_exec_sql_text(r.sql_handle) as s
	outer apply sys.dm_exec_text_query_plan (r.plan_handle,0,-1) p
	 where 1=1  
		--and r.sql_handle is not null 
		--and session_id = 114 -- поиск по id запроса
		and text like '%INSERT INTO SLICE_BS_1 WITH(TABLOCK)%' -- поиск по тексту запроса

	-- Поискать план в кэшах
	SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan, *
	FROM sys.dm_exec_cached_plans 
	CROSS APPLY sys.dm_exec_sql_text(plan_handle)
	CROSS APPLY sys.dm_exec_query_plan(plan_handle)
	where text like '%INSERT INTO SLICE_BS_1 WITH(TABLOCK)%' -- поиск по тексту запроса

--- посмотреть план запроса 
DECLARE @id_session INT =338 -- написать номер сессии 

SELECT s.session_id, 
       d.NAME                                      AS DName, 
       Cast(Dateadd(second, s.total_elapsed_time / 1000, Getdate()) - Getdate() 
            AS TIME 
       )                                           AS elapsed_time, 
       ses.original_login_name, 
       s.granted_query_memory * 8. / 1024. / 1024. granted_query_memory_GB, 
       s.cpu_time, 
       s.logical_reads, 
       s.reads, 
       s.writes, 
       s.wait_type, 
       s.wait_time, 
       s.last_wait_type, 
       Substring(st.text, s.statement_start_offset / 2 + 1, 
       ( CASE 
           WHEN s.statement_end_offset = -1 THEN Len( 
           CONVERT(NVARCHAR(max), st.text)) * 
                                                 2 
           ELSE s.statement_end_offset 
         END - s.statement_start_offset ) / 2 + 1) AS sql_text, 
       percent_complete, 
       Try_convert(xml, pl.query_plan), 
       sp.*, 
       s.*, 
       st.*, 
       ses.* 
FROM   sys.dm_exec_requests s 
       INNER JOIN sys.databases d 
               ON d.database_id = s.database_id 
       INNER JOIN sys.dm_exec_sessions ses 
               ON s.session_id = ses.session_id 
       CROSS apply sys.Dm_exec_sql_text(s.sql_handle) st 
       OUTER apply sys.Dm_exec_query_plan(s.plan_handle) sp 
       OUTER apply sys.Dm_exec_text_query_plan(s.plan_handle, 
                   s.statement_start_offset, 
       s.statement_end_offset) pl 
WHERE  s.session_id = @id_session