How Do I Determine CPU / Elapsed Time in SQL Server Stored Procedures

0
1981

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'
, *
from sys.dm_exec_procedure_stats
where 1=1
and database_id = 5
and execution_count > 0
order by total_elapsed_time/1000000/60/execution_count desc

 

Best Regards

Peter

Previous articleGenerating Test Data Using Random Number Generation
Next articleData Warehouse Automation And ETL Tools – Part 01
Peter Nolan
Peter Nolan is one of the worlds leading thought leaders in Business Intelligence. Across his 29+ years in BI Peter has consistently invented new and innovative ways of designing and building data warehouses. SeETL now stands alone as the worlds most cost effective data warehouse development tool.