SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 How many times a stored proc was run?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

skyfire1
Starting Member

4 Posts
Posted - 05/23/2007 :  21:58:03  Show Profile  Reply with Quote
Hello all,
I was asked a question the other day and to be honest I'm drawing a blank. The question was is there a way to find out how many times a stored proc was run in a day. Does anyone have any suggestions.

Thanks

jezemine
Flowing Fount of Yak Knowledge

USA
2875 Posts
Posted - 05/23/2007 :  22:10:59  Show Profile  Visit jezemine's Homepage  Reply with Quote
you could put a statement in the proc that logs to a table each time it's executed

www.elsasoft.org
Go to Top of Page

skyfire1
Starting Member

4 Posts
Posted - 05/23/2007 :  22:15:18  Show Profile  Reply with Quote
That was my first thought, maybe a trigger. I was just wondering if sql server kept statistics internally.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
16769 Posts
Posted - 05/23/2007 :  22:17:06  Show Profile  Reply with Quote
quote:
Originally posted by skyfire1

That was my first thought, maybe a trigger. I was just wondering if sql server kept statistics internally.

It can't be a trigger. Trigger is for tables / views.

KH
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2875 Posts
Posted - 05/24/2007 :  01:12:41  Show Profile  Visit jezemine's Homepage  Reply with Quote
sql server does not keep such statistics internally afaik.

www.elsasoft.org
Go to Top of Page

DMcCallie
Yak Posting Veteran

USA
62 Posts
Posted - 05/25/2007 :  14:17:31  Show Profile  Reply with Quote
SQL/DM by IDERA has that information in the Procedure Cache screen...

http://www.Idera.com
Go to Top of Page

sql_er
Constraint Violating Yak Guru

253 Posts
Posted - 05/25/2007 :  16:40:01  Show Profile  Reply with Quote
You could run the server side tracing the whole day, just tracing calls to that stored procedure (in case you don't have access to this stored procedure and as such cannot modify it, to log its execution)
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 05/26/2007 :  05:23:57  Show Profile  Reply with Quote
"in case you don't have access to this stored procedure and as such cannot modify it"

... or rename it and "wrap" it with something that can do the counting - and then rename-back when the Stats are done.

FWIW all our Sprocs have inbuilt Timer and Counter Logging stuff for this sort of purpose.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000