Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Auditing Stored Procedure calls Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 3:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
I'd like to know how often a stored procedure is called, also useful would be the time, which user made the call, and how long it took.

Before I go and write some extra code to record this data from inside the stored procedure it occurred to me that the information is probably already available somewhere.
Does SQL Server already do this - perhaps in one of the sys.dm_ views or is it possible to search the transaction log for this data?



Post #1232291
Posted Monday, January 9, 2012 5:46 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:52 PM
Points: 1,520, Visits: 254
You can find a few information in DMVs (sys.dm_exec_query_stats) but there will not be that many details.
You will only know how many times it has been called since last server restart.

You can use profiler to gather following information :
- who called the SP (hostname, loginname or ntusername depending on the authentication type)
- when it was called
- ...





Post #1232349
Posted Monday, January 9, 2012 6:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I use server-side traces to do that kind of thing. Won't necessarily tell you who called a proc, if it's being called by a web-based application for example, but will tell you how often, how long, et al. Capture the text data on it and you can even get parameter values so you can check for things like injection attempts, or common options (for optimization purposes).

Very useful technique.

Check out sp_trace_create, and fn_trace_getinfo, and fn_trace_gettable. Takes a little bit of study and usually a tiny bit of trial and error, but once you get how to use them and are comfortable with them, server-side traces are a wonderful tool.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1232374
Posted Monday, January 9, 2012 6:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
Thanks, The profiler creates exactly what I want.

But on production I don't have access to profiler, only SSMS. Can I script the profiler actions to create a table for the output?

Are profiler jobs resource-intensive? since I'm only logging one rarely-used stored procedure I hope it won't be.

Post #1232379
Posted Monday, January 9, 2012 6:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
GSquared (1/9/2012)


Check out sp_trace_create, and fn_trace_getinfo, and fn_trace_gettable. Takes a little bit of study and usually a tiny bit of trial and error, but once you get how to use them and are comfortable with them, server-side traces are a wonderful tool.


You must be psychic, I was just asking for this
Post #1232384
Posted Monday, January 9, 2012 9:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430

Is there a trick to setting up traces?

I have a trace - all set up and working, and collecting data - with the filter set on textdata like '%MyProcName%'

exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%MyProcName%'

It detects calls from SSMS but not from the application.

The call from the application is happening because the call count increases when looking at
SELECT TOP 100 T.*, P.*
FROM sys.dm_exec_cached_plans AS P
CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) T
WHERE text like '%MyProcName%'


Post #1232566
Posted Monday, January 9, 2012 11:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Profiler is quite resource-hungry, but server-side traces barely impact the server at all.

I haven't tried filtering by a specific proc name. I sometimes filter by a particular database.

Can you post the script you used to create the trace? That would make helping you a bit easier.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1232682
Posted Tuesday, January 10, 2012 4:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 15,737, Visits: 28,143
azdzn (1/9/2012)
You can find a few information in DMVs (sys.dm_exec_query_stats) but there will not be that many details.
You will only know how many times it has been called since last server restart.

You can use profiler to gather following information :
- who called the SP (hostname, loginname or ntusername depending on the authentication type)
- when it was called
- ...


sys.dm_exec_query_stats doesn't keep information since the last server restart. It keeps information on each query from the time that query enters cache until it leaves cache. When that query leaves the cache, all that data goes away. If that query goes into cache again, it starts over.

Also, Profiler is not the tool I'd recommend. Instead I'd use a server side trace, which is a scripted mechanism for gathering the data. Profiler has additional overhead when hitting the system that should be avoided.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1233038
Posted Tuesday, January 10, 2012 4:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,293, Visits: 1,430
Thanks G^2

But I've managed to work it out with BOL on sp_trace_setevent

I needed to track events SP:Starting and SP:Completed for calls by the application, and SQL:BatchStarting and SQL:BatchCompleted for calls from SSMS.
Post #1233044
Posted Tuesday, January 10, 2012 7:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Yep. Glad you worked it out. Happy to be able to help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1233187
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse