
use AdventureWorks
GO

CREATE PROCEDURE GetOrdersSub1(@StartOrderDate datetime)
as
select * from Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate
EXECUTE GetOrdersSub2
GO

CREATE PROCEDURE GetOrdersSub2
AS
SELECT * FROM Sales.SalesOrderDetail
GO

DBCC FREEPROCCACHE
GO

-- execute this query after every EXEC
-- to see number of executions for each proc's query plan
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.* 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE text NOT LIKE '%sql.text%'
ORDER BY qs.EXECUTION_COUNT DESC
GO

EXEC GetOrdersSub1 '20040731'
GO

-- one execution of each proc
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.* 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE text NOT LIKE '%sql.text%'
ORDER BY qs.EXECUTION_COUNT DESC
GO

EXEC GetOrdersSub1 '20040731'
GO

-- same query 2 executions of each proc

EXEC GetOrdersSub1 '20040731' WITH RECOMPILE

-- 3 executions of Sub2, 2 executions of Sub1

EXEC GetOrdersSub2 WITH RECOMPILE

-- still 3 and 2