Hi
I have hundreds stored procedure that tipically execute a select with "sp_executesql" with parameters to optimized ed reuse the same dynamic query plan.
The schema (semplified) is:
CREATE PROCEDURE [dbo].[sp_XXX_Get] (...) AS
BEGIN
SET NoCount ON;
EXECUTE [sp_XXX_GetBase] @Par_1, @Par_2, @Par_N, @Records OUT
END
CREATE PROCEDURE [dbo].[sp_XXX_GetBase] (...) AS
BEGIN
SET NoCount ON;
[construction of select and parameters in @ExSqlStatement]
EXEC sp_executesql @ExSqlStatement, N'@Records udtLong OUT', @Records OUT
END
and normally I'm wait something like
- 1 static query plan for stored procedure (objtype = "Proc", cacheobjtype = "Compiled Plan")
- 1 couple of dynamic query plan (objtype = "Prepared", cacheobjtype = "Compiled Plan )
But I note that, not for all, sometime not all select condictions are passed like parameters and I findout have several dynamic query plan every stored procedure.
So, I need relate the dynamic query plans with the stored procedure that generate it to see how correct them.
I searched on microsoft documantation and direcly on internet but I not found nothing.
Someone can help me ?
Thank you in advance
The query that I use to detect "Proc" and "Prepared" plan is:
use [database]
go
set transaction isolation level read uncommitted
set implicit_transactions off
SELECT
SO_name = isnull(SO.name, SO2.name), SO_xtype = isnull(SO.xtype, SO2.xtype)
, s='|||'
, ECP_bucketid = ECP.bucketid, ECP_Cacheobjtype = ECP.Cacheobjtype, ECP_Objtype = ECP.Objtype, EST_TEXT = replace(replace(replace(EST.TEXT, char(9), ''), char(13), ''), char(10), '')
, s='|||'
, ECP_bucketid = ECP.bucketid, ECP_RefCounts = ECP.RefCounts, ECP_UseCounts = ECP.UseCounts, ECP_Size_In_Bytes = ECP.size_in_bytes , ECP_Memory_Object_Address = ECP.memory_object_address
, s='|||'
, ECP_plan_handle = ECP.plan_handle , ECP_pool_id = ECP.pool_id , ECP_parent_plan_handle = ECP.parent_plan_handle
FROM sys.dm_exec_cached_plans ECP
CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) EST
left outer join sysobjects SO on SO.id = abs(EST.objectid)
/*WHAT'S THE RELATIONSHIP?*/
left outer join sysobjects SO2 on SO2.id = abs(EST.objectid)
/*WHAT'S THE RELATIONSHIP?*/
where
1=1
and
(
1=0
or ECP.objtype = 'Proc'
or ECP.objtype = 'Prepared'
)
order by
isnull(SO.name, SO2.name)
, isnull(SO.xtype, SO2.xtype)
, ECP.Objtype, ECP.Cacheobjtype
, EST.TEXT, ECP.bucketid, ECP.UseCounts
go
