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

0
113

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

 

Comments

comments

SHARE
Previous articleGenerating Test Data Using Random Number Generation
Profile photo of Peter Nolan
Peter Nolan is one of the worlds leading thought leaders in Business Intelligence. Across his 25+ 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.

NO COMMENTS

LEAVE A REPLY

Sign up to our newsletter!