MSSQLTips

I'm planning on doing some stored procedure performance tuning.  It makes sense to concentrate first on the SP's that get executed the most often, as saving a couple of seconds could quickly add up.

What is the best/easiest way to determine which SP's are getting executed the most frequently?

 Which version of SQL Server?

2005 SP2

 The DMVs hold a wealth of information

This query shows the queries in the procedure cache with the greatest usecounts

select qs.refcounts, qs.usecounts, cacheobjtype, objtype, qt.text
from sys.dm_exec_cached_plans qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as qt
order by qs.usecounts desc
go

I love it when smart people point me towards an answer.

What would I then join to in order to get the DB_id and the object_id of the stored procs?

 Hi

 The id's can be found within sys.dm_exec_sql_text and converted.  For ad-hoc or prepared queries, they'll be NULL

select db_name(qt.dbid), object_name(qt.objectid), qs.refcounts, qs.usecounts, cacheobjtype, objtype, qt.text
from sys.dm_exec_cached_plans qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as qt
order by qs.usecounts desc
go

 

Thank you, O Wise One