February 4, 2009 at 7:45 am
There are lot of stored procedures in our SQL 2005 database.I want to know against every stored procedure when was it last accessed by any our application?
Similarly can have it for Table also
February 4, 2009 at 9:24 am
I think that a lot of us would like just that
Unfortunately as far as i know there is no way of doing this without using Profiler
February 4, 2009 at 10:57 am
Andrew Reilly (2/4/2009)
I think that a lot of us would like just thatUnfortunately as far as i know there is no way of doing this without using Profiler
You can use the dynamic management views to see the procedures currently in the procedure cache, which will tell you the last time they were accessed. But if it's not in cache, there's nothing stored anywhere that I'm aware of that will tell you anything about the last time it was accessed.
"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
February 7, 2009 at 9:52 am
Thanks for the reply.
Can i run the profiler all the time in live server and store the results? (should not reduce the performance of the server-concurrantly 100 users will be using)
February 7, 2009 at 11:31 am
The only thing worse than running Profiler on a production server for an extended period of time is running profiler on a different computer connecting to a instance of sql server from another location. Profiler is great for GUI and for giving you a quick look at data, but it is horrablely inefficient and can actually shut down sql server (I have done this personally).
If you are wanting this information the best thing to do is to setup a profiler to collect all of the data that you want to collect and make sure that you are writing it to a file. Once you have done this, goto
FILE > Export > Sript Trace Destination > For sql server (your version here)
It will ask you to save this. Go ahead and then open it up and run this on the server that you want to.
This will create a server side trace which is much more efficent than profiler and it will take up vastly less resources. Make sure that the file you are writing to is not on the same drive as the Data, Log, Temp, or any other drivers that are being used on a regular basis. I store mine on the backup drive of the machine and then move them off nightly.
Also, be careful about what you store and how you store it. My server dump out roughly 500 mb a day worth of trace data, which I then aggregate down into about 30k rows.
To make sure that the trace is running, use the following command
select * from fn_trace_getinfo(null)
This will show you all the traces that are currently running on the system.
I have this setup as a nightly job to take kill the prior night trace, aggregate the data, and then start a new one.
Hope that helps.
More information can be found here http://www.sqlservercentral.com/articles/Profiler/63097/
Fraggle
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply