Auditing stored procedure execution

Question

Auditing stored procedure execution

Anders Pedersen

SSChampion

Points: 11410
More actions
December 12, 2006 at 12:08 pm

#175574

I've recently taken on a new job, for a company that has never had a DBA before. We have 41 databases, with what everyone assumes is a lot of dead stored procedures. Since we are comming up on end of year processing I figured it would be great to get a list of procedures we know has been run.
All I want to know is which stored procedures has been executed, I do not care who/when or what parameters. For now I just want to eliminate procedures that are not used at all so I do not spend my time trying to fix/optimize procedures that are no longer used.
My first idea was to simply insert into a table whenever a procedure has been called, but this requires modifying all the stored procedures. With about 3000 procs this is just simply not convenient, not to mention it introduces a lot of risk. Not to mention problems for the Acces 2000 and Excel 2000 connections...
Next try was using the sp_trace functionalty. For various reasons I do not want to use the GUI profiler. I set it up to capture event 42 (sp:starting). This gets me what I need, but is extremely chatty, it made about 77,000 records in 10 minutes..... I could set filters on this to eliminate the system procedures such as the reset connections ones. It also gets me UDF calls, which I do not want (at least at this time).
Would using Event Number 10, RPC:Completed, give me a better/smaller result set. I am a little unsure if it would catch everything I need. I.e. would that get procedures called by SQL Agent on the same server?

Any ideas would be greatly appreciated.

Thanks,
Anders Pedersen

Viewing 8 posts - 1 through 7 (of 7 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

Is this sql 2000? or 2005?

Use sp:completed with EventCalss, spid ,textdata , Duration, cpu, reads and writes columns only.... YOu don't need duration, cpu, reads and writes columns but you use those columns to tune your procedure later other wise you have to run the trace once again...

Use extended procedure to run the sql trace and write the output to a local file then to a table...

MohammedU
Microsoft SQL Server MVP

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

This is SQL2005, with no service packs, running on Windows 2003.

Jambu Krishnamurthy SSCrazy Points: 2054 More actions · Answer 3

Events: Stored Procedures -> SP:Starting

Filters: TextData -> Like - usp_%

On Filters tab: Select the checkbox: Exclue System IDs

this should get only the SPs being executed whose names start with "usp_"

in the trace

jambu

MohammedU SSCoach Points: 19644 More actions · Answer 4

Use text data filter if your all procedures are using same prefix like 'usp_' or 'stp_'

MohammedU
Microsoft SQL Server MVP

Anders Pedersen SSChampion Points: 11410 More actions · Answer 5

Thanks all. Decided to just use the built in functions for this, since the developers here had been pretty good about calling all the user defined functions fn_something, it was fairly easy. Follwoing is the basic calls I used for the reference of anyone else that might be trying to do the same:

declare

@r int, @ms bigint, @file nvarchar(255)

select

@ms = 5 -- Filesize

select

@file = 'L:\traces\SPTraceDur'

exec

sp_trace_create @traceid = @r output, @options = 2, @tracefile = @file, @maxfilesize = @ms

select

@r

-- 2

-- important, keep the @r value!

-- eventid 42 is sp:starting, 10 is RPC:complete; 43 is sp:complete

declare

@o bit

select

@o = 1

exec

sp_trace_setevent @traceid = 2, @eventid = 43, @columnid = null, @on = @o -- turns the event on, SP: complete

exec

sp_trace_setevent @traceid = 2, @eventid = 43, @columnid = 35, @on = @o -- database name

exec

sp_trace_setevent @traceid = 2, @eventid = 43, @columnid = 34, @on = @o -- object name

exec

sp_trace_setevent @traceid = 2, @eventid = 43, @columnid = 13, @on = @o -- duration

exec

sp_trace_setfilter 2, 34, 0, 7, N'fn%'

exec sp_trace_setfilter 2, 34, 0, 7, N'CblDt'

exec

sp_trace_setstatus @traceid = 2 , @status = 1 --0 to stop, 1 to start, 2 to destroy

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

To read the file created from above use the fn_trace_gettable function. To get a count of how many calls has been made:

select

count(*) from fn_trace_gettable ( 'L:\traces\SPTracedur.trc' , null )

Replace the null with an integer value to read the first X files. It currently takes me about 5 seconds to run the above statement on 31 files.

Note #1: The above trace is almost a bare minimum of what you would need for my original requirement, but I added duration to it as it made sense to also look at the performance while I was at it. If you want to do this and also track when something ran need to add in the start time column.

Note #2: If you select * from fn_trace_gettable, it will return every column possible in a trace, so you want to only select the columns you have data in:

select

Duration, ObjectName, DatabaseName from fn_trace_gettable ( 'L:\traces\SPTracedur.trc' , null )

Hope that helps anyone that might have the same problem,

Anders Pederse

MohammedU SSCoach Points: 19644 More actions · Answer 7

Thanks Anders Pedersen for sharing your exprience..

MohammedU
Microsoft SQL Server MVP