How can I track how many times a specific SP is called every day?

Question

Post reply

How can I track how many times a specific SP is called every day?

VoldemarG

Hall of Fame

Points: 3883
More actions
April 3, 2014 at 4:11 pm

#284129

I need to know /observe for a period like 10 days in a row HOW MANY TIMES a specific Stored Proc is executed on the server.
How can I do that?
is there a way to precisely know a number of such calls via DMVs?
thanks!
Likes to play Chess

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63500 More actions · Answer 1

This might help.

http://sqlfool.com/2009/08/find-recently-executed-stored-procedures/

But you could also do something simple like creating a simple logging table (ObjectName, ExecuteDate) and then create another stored procedure to do the insert...

CREATE PROCEDURE

uspLogSprocExecution(

@ObjName)

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO LogTable(ObjectName,ExecutionTime) VALUES (@ObjName,GETDATE());

SET NOCOUNT OFF;

END

Then you would just call the LogSprocExecution stored procedure from inside the stored procedures you wanted to log... (it's just more flexible than hard coding it into that single stored procedure.)

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

You could also create a very "light" server side trace using the default TSQL template in Profiler, remove all the columns you don't need, and a filter for the exact procedure you wish to track, then check the trace file whenever you need.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

pietlinden SSC Guru Points: 63500 More actions · Answer 3

Silly me. Didn't even think of using a trace! Schooled again!

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

I used to do it using a sp as well, then discovered it was a heck of a lot easier with the trace, then created a process to import a trace files daily into a table for "monitoring" bad queries with high reads, CPU, etc.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

pietlinden SSC Guru Points: 63500 More actions · Answer 5

Good points... good to hear I'm just behind on the learning curve... and not just headed off in the wrong direction.

Dohsan SSCrazy Points: 2972 More actions · Answer 6

MyDoggieJessie (4/3/2014)
I used to do it using a sp as well, then discovered it was a heck of a lot easier with the trace, then created a process to import a trace files daily into a table for "monitoring" bad queries with high reads, CPU, etc.

This sounds very interesting, don't suppose you'd be able to share some of your code snippets to do this?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

If your monitoring for multiple days in a row, I wouldn't use trace. Data volume can get way too high. If it's a small number of procedures you want to track, add an insert (or call to a logging procedure) at the start.

Now, if you want durations, CPU usage or other execution characteristics, for that you do need a trace.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

milos.radivojevic Default port Points: 1499 More actions · Answer 8

The following DMV query returns the number of execution for a specific stored procedure since the last server restart:

SELECT execution_count

FROM sys.dm_exec_procedure_stats

WHERE object_id = OBJECT_ID('YourSPNameGoesHere')

If your server is online more than 10 days without interruption you can issue this query every day and figure out the number of calls.

As suggested you can also use an XEvent session with synchronous_event_counter as a target to cound executions of the stored procedures. This will return exact number of calls but as already written it could affect the production system. Extended Events are significantly better for production server as SQL tracing with Profiler, but it still can affect the server. If you can use figures from DMV you don't need to trace.

___________________________
Do Not Optimize for Exceptions!

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

I'd suggest using extended events for this. They're more light weight than trace events and their filtering is much, much better, so you'll only be capturing the data you want. Just be prepared to deal with the data that could be generated over a 10 day period. You might want to aggregate the information on a daily basis so you can keep just the current data online.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

milos.radivojevic (4/4/2014)
The following DMV query returns the number of execution for a specific stored procedure since the last server restart:

It returns the number of executions since that procedure's plan was last cached.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass