Which stored procedure is called most often?

I have more than 300 stored procedures. I want to check which procedures are called most frequently. The result should have every procedure name with number of executions.

more ▼

asked Mar 28, 2016 at 04:13 PM in Default

avatar image

11 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You don't state which version of SQL Server you are using, but if you are using 2008 or higher, the DMV sys.dm_exec_procedure_stats might be an alternative of having to touch all 300 procedures. It will tell you the last time a procedure was executed along with an execution count.

Reference: https://msdn.microsoft.com/en-us/library/cc280701.aspx

Keep in mind though that this would only house cumulative data since the last restart of the instance.

Hope that helps!

more ▼

answered Mar 28, 2016 at 06:19 PM

avatar image

14.4k 3 7 15

yes. but i already tested dm_exec_procedure_stats query it's not return proper result. for example i called one procedure on login time. so it will not return that procedure name & counts.

Mar 29, 2016 at 09:57 AM vshimpi

Even with that, I would think that the DMV would still have the stats for the procedure. It still has to have an execution plan and will reside in cache. If you have a high volume system I suppose that it could be pushed out of cache quickly.

Mar 29, 2016 at 12:18 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 28, 2016 at 04:13 PM

Seen: 79 times

Last Updated: Dec 11, 2016 at 07:25 AM

Copyright 2018 Redgate Software. Privacy Policy