SET  FMTONLY OFF;
EXEC [etl].sp_Get_Sale ?, ?, ?

SELECT  operation_id, 
		message_time,
		message_type, 
		message_source_type,
		message, 
		package_name, 
		event_name, 
		message_source_name, 
		subcomponent_name, 
		package_path 
FROM	[catalog].[event_messages] msg
WHERE	package_name = 'Sale.dtsx' AND
		message_time BETWEEN '2019-03-16' AND '2019-03-19 23:59:59' AND
		execution_path LIKE '%EXTRACT\SOURCES\UT\%' AND
		message_source_name = 'UT Sales' AND
		operation_id = 95166

SELECT TOP 10 cp.cacheobjtype, cp.objtype, cp.plan_handle, t.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE '%sp_Get_Sale%'
	AND t.dbid = DB_ID('MyDB')

	;WITH [Cost] AS
	(
		SELECT
			 YEAR([Cost_Register].[Период])*100 + MONTH([Cost_Register].[Период])	AS [Month]
			,[Product_Analytics].[Номенклатура]					AS [Product_ID]
			,[Product_Analytics].[Склад_RRRef]					AS [Warehouse_ID]		
			,[Cost_Register].[Организация]						AS [Organisation_ID]
			,[Cost_Register].[Стоимость]						AS [Cost]
			,[Cost_Register].[СтоимостьДопРасходыБезНДС]				AS [AddCostNoVAT]
			,[Cost_Register].[СтоимостьДопРасходы]					AS [AddCostWithVAT]			
		FROM
			[ut_filial].[dbo].[_РегистрСведений_СтоимостьТоваров] AS [Cost_Register]
		JOIN
			[ut_filial].[dbo].[_РегистрСведений_АналитикаУчетаНоменклатуры] AS [Product_Analytics]
		ON	[Product_Analytics].[КлючАналитики] = [Cost_Register].[АналитикаУчетаНоменклатуры]
		WHERE
			[Cost_Register].[Активность] = 0x01
	)
	UPDATE
		#SALE
	SET
		#SALE.[Cost]		= ([Cost].[Cost] * #SALE.[Quantity]),
		#SALE.[AddCostNoVAT]	= ([Cost].[AddCostNoVAT] * #SALE.[Quantity]),
		#SALE.[AddCostWithVAT]	= ([Cost].[AddCostWithVAT] * #SALE.[Quantity])
	FROM
		#SALE
	JOIN
		[Cost]
			ON	[Cost].[Month]		 = #SALE.[Action_Month]
			AND	[Cost].[Product_ID]	 = #SALE.[Product_ID]
			AND	[Cost].[Warehouse_ID]	 = #SALE.[Warehouse_ID]
			AND	[Cost].[Organisation_ID] = #SALE.[Organisation_ID]
	WHERE
		#SALE.[Cost] IS NULL

SELECT	*  
FROM	sys.objects 
WHERE	type = 'P' AND
	QUOTENAME( SCHEMA_NAME ( schema_id ) ) + '.' + QUOTENAME( name ) = '[etl].[sp_Get_Sale]'

SELECT	*
FROM	[sys].[dm_exec_procedure_stats]
WHERE	object_id = 2037582297

SELECT *
FROM sys.dm_exec_cached_plans cp
WHERE	plan_handle = 0x05000A00D90973794081933E0E0000000000000000000000

SELECT	*
FROM	sys.dm_exec_plan_attributes (0x05000A00D90973794081933E0E0000000000000000000000)  

SELECT TOP 10 cp.cacheobjtype, cp.objtype, cp.plan_handle, t.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE '%sp_Get_Sale%'

SELECT TOP 10 cp.cacheobjtype, cp.objtype, cp.plan_handle, t.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE '%sp_Get_Sale%'

SELECT	*  
FROM	sys.objects 
WHERE	type = 'P' AND
	QUOTENAME( SCHEMA_NAME ( schema_id ) ) + '.' + QUOTENAME( name ) = '[etl].[sp_Get_Sale]'

SELECT	*
FROM	[sys].[dm_exec_procedure_stats]
WHERE	object_id = 2037582297
-- количество plan_handle у процедуры не изменилось

SELECT *
FROM sys.dm_exec_cached_plans cp
WHERE	plan_handle = 0x06000A003AC0EC2040A125FF040000000000000000000000

SELECT	*
FROM	sys.dm_exec_plan_attributes (0x06000A003AC0EC2040A125FF040000000000000000000000)
-- А здесь видно, что у данного plan_handle, другой объект 

select p.[plan_handle], pp.[query_plan] 
from sys.dm_exec_cached_plans p
    cross apply sys.dm_exec_query_plan(p.plan_handle) pp
    cross apply sys.dm_exec_plan_attributes (p.plan_handle) pp2
where pp2.attribute = 'objectid'
and cast(pp2.value as int) = object_id('etl.sp_Get_Sale', 'P')
option (recompile);

select p.[plan_handle], pp.[query_plan] 
from sys.dm_exec_cached_plans p
    cross apply sys.dm_exec_query_plan(p.plan_handle) pp
    cross apply sys.dm_exec_plan_attributes (p.plan_handle) pp2
where pp2.attribute = 'objectid'
and cast(pp2.value as int) = 552386618
option (recompile);

(@P1 datetime2(0),@P2 datetime2(0),@P3 smallint)EXEC [etl].[sp_Get_Sale] @P1, @P2, @P3