Madeira Team
Aug 27, 2012
Imagine the following scenario:
After a very nice weekend, you get up in the morning with a smile on your face, get to work and drink your nice morning coffee.
Suddenly, your manager rushes in and tells you there is a big performance problem with some business flow.
Unfortunately, you are not familiar with this business flow, and all you know is the name of the top procedure that is called by the application.
You start digging in the code, and you realize this procedure calls 5 child procedures, which in turn call a-lot of other procedures.
How do you know quickly who is the problematic procedure so you can fix it and go back to drink your coffee?
Here’s one quick way to do it:
The following script receives the database name, schema name and the procedure name, and returns a table with all of its “child” procedures (and functions).
Each row in the returned table contains the database and schema name, the procedure name, the path of the procedures calling it and the amount of time took for each execution of it.
It quickly shows the hierarchy of the procedures, and more importantly, which takes the most amount of time and needs to be optimized.
Be aware that the results may be a bit confusing sometimes, because a child procedure may seem to be taking more time than its parent. This will happen when the child procedure is executed by more than one parent, when each parent calls with different parameters, thus affecting the child’s execution time.
[code lang=”sql”]
declare @DatabaseName varchar(100)
declare @SchemaName varchar(100)
declare @SPName varchar(100)
set @DatabaseName = ‘myDB’
set @SchemaName = ‘dbo’
set @SPName = ‘myProcedure’
declare @collation sysname
select @collation = collation_name
from sys.databases where name=’master’
if object_id(‘tempdb..#dependencies’) is not null
drop table #dependencies
if object_id(‘tempdb..#allProcedures’) is not null
drop table #allProcedures
create table #dependencies
(
CallingProcedureDB varchar(1000),CallingProcedureSchema varchar(1000),
CallingProcedure varchar(1000),CallingProcedureObjectId int,
CalledProcedureDB varchar(1000),CalledProcedureSchema varchar(1000),
CalledProcedure varchar(1000),CalledProcedureObjectId int,
Lvl int, Handled bit
)
create table #allProcedures
(
DBName varchar(1000),SchemaName varchar(1000),
SPName varchar(1000),ObjectId int
)
–Populate the procedures table
exec sp_MSforeachdb
‘insert #allProcedures
select ”?”, s.name, p.name, p.object_id
from ?.sys.procedures p
inner join ?.sys.schemas s
on p.schema_id = s.schema_id’
–Start the "recursive" process
declare @lvl int = 0
declare @cmd nvarchar(4000)
insert #dependencies
(CalledProcedureDB, CalledProcedureSchema, CalledProcedure,
CalledProcedureObjectId, Lvl, Handled)
SELECT @DatabaseName,@SchemaName,@SPName,
object_id(@SchemaName+’.’+@SPName),@lvl,0
while exists(select * from #dependencies
where Lvl = @lvl and Handled = 0)
begin
set @cmd=
‘insert #dependencies
select d.CalledProcedureDB, d.CalledProcedureSchema,
d.CalledProcedure, d.CalledProcedureObjectId,
p.dbName,p.SchemaName,
e.referenced_entity_name ,p.ObjectId,
‘+cast((@lvl+1) as nvarchar(50))+’,0
FROM #dependencies d
cross apply
?.sys.dm_sql_referenced_entities
(CalledProcedureSchema+”.”+CalledProcedure, ”OBJECT”) e
inner join #allProcedures p
on e.referenced_entity_name collate ‘
+ @collation + ‘ = p.SPName
and isnull(e.referenced_database_name collate ‘
+ @collation + ‘ ,CalledProcedureDB) = p.DBName
where Lvl = ‘+cast(@lvl as nvarchar(50))
exec sp_MSForEachDB @cmd
update #dependencies set Handled = 1 where Lvl = @lvl
set @lvl+=1
end
select d.*,t.AvgElapsedTimeMS from #dependencies d
left join
(select
object_name(objectid) as ProcedureName,objectid,dbid,
avg(total_elapsed_time/execution_count)/1000 as AvgElapsedTimeMS
from sys.dm_exec_procedure_stats
cross apply sys.dm_exec_sql_text(sql_handle)
group by OBJECT_NAME(objectid),objectid,dbid
)t
on d.CalledProcedureObjectId = t.objectid
and d.CalledProcedureDB = db_name(t.dbid)
order by lvl
[/code]