Auditing Stored Procedure calls

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Auditing Stored Procedure calls

11 posts, Page 1 of 2

1 2 »»

Auditing Stored Procedure calls

Rate Topic

Display Mode

Topic Options

Author

Message

Tom Brown

Posted Monday, January 09, 2012 3:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196, Visits: 1,319

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

azdzn

Posted Monday, January 09, 2012 5:46 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:46 PM
Points: 1,493, Visits: 239

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

GSquared

Posted Monday, January 09, 2012 6:39 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Tom Brown

Posted Monday, January 09, 2012 6:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196, Visits: 1,319

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

Tom Brown

Posted Monday, January 09, 2012 6:47 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196, Visits: 1,319

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 BigGrin

Post #1232384

Tom Brown

Posted Monday, January 09, 2012 9:27 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196, Visits: 1,319

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

GSquared

Posted Monday, January 09, 2012 11:07 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Grant Fritchey

Posted Tuesday, January 10, 2012 4:22 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144

azdzn (1/9/2012)

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

Tom Brown

Posted Tuesday, January 10, 2012 4:31 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196, Visits: 1,319

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

GSquared

Posted Tuesday, January 10, 2012 7:11 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions