Question: Is there a way to count the number of executions of a stored procedure  
Author Message
vmynatt





PostPosted: Fri Nov 17 17:28:06 CST 2006 Top

SQL Server Developer >> Question: Is there a way to count the number of executions of a stored procedure

Hello,

Does anyone know if it's possible to keep track of the number of
times a stored procedure is run? (by any and all users.) I want to
try to clean up an existing legacy system which is full of old stored
procedures. Many of which I believe are no longer used. But the
source code is so scattered and messed up that searching it all is not
a great idea. thanks for any help you can give.

Scott

SQL Server237  
 
 
AlterEgo





PostPosted: Fri Nov 17 17:28:06 CST 2006 Top

SQL Server Developer >> Question: Is there a way to count the number of executions of a stored procedure Scott,

Not that I know of. You can use profiler, but be careful. You might have
stored procs that are only run at the end of the month, or worse yet, at the
end of the year. Both are common business scenarios.

An approach I've used is to rename suspect stored procedures to something
like:

MyStoredProc_z

If an application is using it, hopefully it will throw an error and you can
rename it back. This could be dangerous though - and could muck up your RI.
I use the _z as a suffix, because the stored proc will sort in the same
order if you have to look for it.

Even seaching all the nooks and crannies for references in your source code
might not work. If your environment is mucked up, then you might have
instances where the source code no longer exists anywhere - more common than
you think in small companies with legacy systems.

Unfortunately, the safest approach is to let them be unless you want to go
through an extensive off-line QA process.

A lot of extraneous stored procedures accumulate because they are not used
by the application, but were used adhoc to clean up data or for other
reasons that don't touch the application. Moving forward, use a naming
convention for stored procedures that might only be used temporarily. I like
z_MyStoredProcedure. That way, you can clean them up periodically and they
all sort at the end of the list in EM and Query Analyzer.

-- Bill




> Hello,
>
> Does anyone know if it's possible to keep track of the number of
> times a stored procedure is run? (by any and all users.) I want to
> try to clean up an existing legacy system which is full of old stored
> procedures. Many of which I believe are no longer used. But the
> source code is so scattered and messed up that searching it all is not
> a great idea. thanks for any help you can give.
>
> Scott
>


 
 
Aaron





PostPosted: Fri Nov 17 18:50:13 CST 2006 Top

SQL Server Developer >> Question: Is there a way to count the number of executions of a stored procedure If you have upgraded to SQL Server 2005, you can get much of this
information from the dynamic management views.

If you are on a previous version, the only ways you can track this are
through:
- custom logging in each stored procedure
- 3rd party auditing tools
- running a profiler trace 24x7




> Hello,
>
> Does anyone know if it's possible to keep track of the number of
> times a stored procedure is run? (by any and all users.) I want to
> try to clean up an existing legacy system which is full of old stored
> procedures. Many of which I believe are no longer used. But the
> source code is so scattered and messed up that searching it all is not
> a great idea. thanks for any help you can give.
>
> Scott
>


 
 
Tibor





PostPosted: Wed Dec 20 12:23:59 CST 2006 Top

SQL Server Developer >> Question: Is there a way to count the number of executions of a stored procedure

create proc p as

GO
EXEC p


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/




> Aaron, I've looked at some of the dynamic views, and I don't see a way to get
> back to the names of the sprocs. Can you give a specific example?
>
> Gordon
>

>
>> If you have upgraded to SQL Server 2005, you can get much of this
>> information from the dynamic management views.
>>
>> If you are on a previous version, the only ways you can track this are
>> through:
>> - custom logging in each stored procedure
>> - 3rd party auditing tools
>> - running a profiler trace 24x7
>>
>>


>> > Hello,
>> >
>> > Does anyone know if it's possible to keep track of the number of
>> > times a stored procedure is run? (by any and all users.) I want to
>> > try to clean up an existing legacy system which is full of old stored
>> > procedures. Many of which I believe are no longer used. But the
>> > source code is so scattered and messed up that searching it all is not
>> > a great idea. thanks for any help you can give.
>> >
>> > Scott
>> >
>>
>>
>>

 
 
Erland





PostPosted: Wed Dec 20 17:46:20 CST 2006 Top

SQL Server Developer >> Question: Is there a way to count the number of executions of a stored procedure
> I'm talkiing about getting back to a procname from
> sys.dm_exec_cached_plans.
> It returns:
> bucketid
> refcounts
> usecounts
> size_in_bytes
> memory_object_address
> cacheobjtype
> objtype
> plan_handle
>
> None of these will get me back to the name of the procs.
>
> Am I using the wrong DMV?

dm_exec_query_stats might be a better starting point. The sql_handle
takes you to dm_exec_sql_text which includes an objectid.

--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx