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]