Have the relationship between the dynamic query plans and the stored procedure that generates it

Copper Contributor

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

0 Replies