Last Time Sproc fired Off?

Question

Post reply

Last Time Sproc fired Off?

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
January 8, 2010 at 8:18 am

#141587

Hello Everyone
I am looking for any possible means to know or check when a stored procedure was last fired off. Does SQL Server log this? So far I am not finding anything. I am looking to clean up a couple of databases that have had too many hands involved, and those hands created many sprocs that were and are never used, I just do not know what ones they are.
If I cannot find anything, my plan is to place a small insert statement with the sproc name, and a getdate method to insert into a table, then check it after a month or so.
Thanks
Andrew

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I think that is what you are going to have to in order to determine which sprocs are being used.

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

SQL does not log anywhere the execution date of stored procs. You can run a server-side trace or you can add your own tracking to the procs.

I'd be wary about dropping after only a month. What if it's a proc that's only used during the financial year end?

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Having had an almost identical problem in days gone by (SQL 2000) allow me to list what I had done, and some of the mishaps encountered.

1. Created a table in each DB named "Performancestats", with default values for each column such as date time the procedure was executed, application name, machine name and user name.

2. Initialized the table listing all existing stored procedures (2000 T-SQL would not be compatible with SQL 2008)

3. Added an IF EXISTS for the PerformanceStats table to each SP I thought was not being used, and if the table existed updated the Lastdate time it was invoked in the PerformanceStats table. In this manner I could halt the data gathering effort by simply renaming or deleting the PerformanceStats table without a need to go back and in one fell swoop remove the code from each and every SP that I had modified, but rather could do that job over time and the least interference with production activities.

4. For what I believed were unused SPs did NOT delete them, but renamed them much to my relief when I found some were utilized to generate data for quarterly / annual reports.

Needless to say the data gathering effort ran over an extended period of time.

For what it might be worth I can search for and supply you the code I utilized.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Thanks

Good point about the length of time, but we do not have fiscal year data in this database.

We can always pull the one that gets dropped from the script and create it once again.

I have come up with a table and a sproc to take care of this for me. Also some dynamic code so that I have to copy the query only once. I mean, I do not have to try and list the sproc name in each query. There is a nice function that will return the sproc name

OBJECT_NAME(@@PROCID);

Thanks Everyone

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

A method I have used in the past was similar but only worked for 2005. However, I did not modify any procs.

I setup a daily job to run the following query:

;with proclastrun as (

SELECT DB_NAME(dbid) AS DatabaseName

,OBJECT_NAME(ObjectID) AS ProcName

,der.creation_time

,der.Last_execution_time as LastRunTime

,Text

,row_number() over (partition by dest.objectid order by der.last_execution_time desc) as RowNum

FROM sys.dm_exec_query_stats as der

CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) as dest

Where DB_NAME(dbid) = 'mydb'

)

Insert Into admindb.dbo.ProcLastRun (ProcName,LastRunTime)

Select ProcName,LastRunTime

From ProcLastRun

where RowNum = 1

Then from there I could query to find what procs did not make it into the table. This is something that I would let run for a long time.

One such reporting query

select s.* from sys.objects s

Left Outer Join admindb.dbo.mydb a

on a.ProcName = s.name

Where type in ('p','fn')

and a.ProcName is null

and s.name not like 'dt_%'

Order By s.name asc

This isn't 100% accurate. However, for our scenario it worked great.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Johan Bijnens SSC Guru Points: 135380 More actions · Answer 6

IMO, sampling the proc cache on a daily basis is a way to long interval.

It is indeed a good way with minimal impact and no modification of existing stuff.

FWIW I would sample every 5 minutes, just to enhance the chance of catching the execution.

(off course you should investigate an plan lifetime if your ram is under pressure)

BOL ref: http://msdn.microsoft.com/en-us/library/ms181055.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

ALZDBA (1/8/2010)
IMO, sampling the proc cache on a daily basis is a way to long interval.
It is indeed a good way with minimal impact and no modification of existing stuff.
FWIW I would sample every 5 minutes, just to enhance the chance of catching the execution.
(off course you should investigate an plan lifetime if your ram is under pressure)
BOL ref: http://msdn.microsoft.com/en-us/library/ms181055.aspx

Thanks - and I will certainly take that into consideration.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Just bear in mind that there are some procs (any with recompile) that will never appear at all in the cache.

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 9

Thank you to everyone for the wonderful ideas and thoughts about this issue

I greatly appreciate the code samples given by Jason, I will keep that one in my stash of code for SQL 2005.

Thank you again to everyone

Andrew SQLDBA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

AndrewSQLDBA (1/9/2010)
Thank you to everyone for the wonderful ideas and thoughts about this issue
I greatly appreciate the code samples given by Jason, I will keep that one in my stash of code for SQL 2005.
Thank you again to everyone
Andrew SQLDBA

You're welcome and NP.

Like I said though - keep in mind that my it is not 100%. Gail pointed out one of the shortcomings (procs that don't show up in cache), and then there is also the frequency of running the code.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events