I have been working on a client that has used Stored Procedures in SQL Server as their ETL system. So I found a post about how to find the CPU and elapsed time usage of Stored Procedures.
Hopefully this will help someone else too.
select top 100 OBJECT_NAME(object_id, database_id)
, total_worker_time/1000000/60 'Total CPU Mins'
, total_worker_time/1000000/60/execution_count 'CPU Mins/run Avg'
, total_elapsed_time/1000000/60/execution_count 'Elapsed min Avg'
and database_id = 5
and execution_count > 0
order by total_elapsed_time/1000000/60/execution_count desc