Get most expensive stored procedures in SQL Server

mostexpensivesprocs.sql

USE [YOUR_DATABASE_NAME]; -- Substitute for your database name
GO

SELECT TOP 10 -- Change as appropriate
	t.text ,
	s.sql_handle ,
	s.plan_handle ,
	s.total_worker_time / 1000 AS total_cpu_time_millis ,
	s.total_worker_time / s.execution_count / 1000 AS avg_cpu_time_millis ,
	s.total_logical_reads,
	s.total_logical_reads / s.execution_count AS avg_logical_reads ,
	s.total_logical_writes,
	s.total_logical_writes / s.execution_count AS avg_logical_writes ,
	s.total_physical_reads,
	s.total_physical_reads / s.execution_count AS avg_physical_reads,
	s.cached_time,
	s.execution_count ,
	s.last_execution_time,
	s.last_elapsed_time / 1000 AS last_elapsed_time_millis,
	s.last_logical_reads,
	s.last_physical_reads,
	s.last_worker_time / 1000 AS last_worker_time_millis
FROM	sys.dm_exec_procedure_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE	DB_NAME(t.dbid) = 'YOUR_DATABASE_NAME' -- Substitute for your database name
	AND s.last_execution_time > '2017-01-05 00:00:00.000' -- Change date as appropriate (or comment out)
	--AND s.last_physical_reads > 10 -- Sample filter; change column and value as appropriate
ORDER BY -- Comment/uncomment below to order by column of interest
	avg_logical_reads DESC
--	avg_physical_reads DESC
--	avg_cpu_time_millis DESC
--	last_elapsed_time_millis DESC
;