April 25, 2011 at 9:00 am
So I know I can use dm_exec_procedure_stats to look at the last time a sproc was executed PROVIDED it is still in cache.
From what I understand a server restart, memory contention, or just lack of use can cause a sproc to drop from having its plan in the cache and as such it will no longer appear on dm_exec_procedure_stats.
Two questions:
1. Is there a similar way to see when a view was last executed? I am interesting in seeing what sprocs and views are seldom / never used.
2. I want a more accurate picture/history/log of the last time something was executed. I had two thoughts on how to accomplish this... suggestions?
2.a: Modify the sprocs I want to track to include an INSERT statement to toss some info about the sproc into some tracking table I setup.
Pros: Extremely accurate data, perfect historical data, and I can collect the user, parameters if I want, etc, etc, etc....
Cons: Could I run into locking issues, performance degradation, etc... if I have 1,000 sprocs being run a minute will I ever have to worry about things being held up due to table inserts taking place all on the same table? Is there anything I should do to help ease any stress on the server using this approach?
2.b: Instead of modifying sprocs, write a new scheduled job that every X hours goes and looks at dm_exec_procedure_stats and dumps the results into a table. It could update any existing records with the most recent execution time and insert any new records.
Pros: Much less work than modifying all sprocs. Much less strain on resources, query runs hourly instead of every time a sproc executes. Since I don't care for a perfect history of every execution and all I want is to the know the last time a sproc was run in general, this seems like a better way to do that. "Did sproc 'someSproc' run in the last 6 months?" is what I really want to know. Not "exactly how many times, and at what frequency did sproc 'someSproc' run over the last 6 months.
Cons: May be imperfect information. Perhaps a sproc is only run once every week, but it is a very tiny sproc with a cheap execution plan... would it perhaps get purged from the cache very quickly and I would miss recording it during my hourly scheduled check?
Can't collect detailed data (though I don't care about that for these purposes)
Thoughts?
April 25, 2011 at 9:30 am
Maxer (4/25/2011)
Two questions:1. Is there a similar way to see when a view was last executed? I am interesting in seeing what sprocs and views are seldom / never used.
Not using the plan cache DMVs. Views don't have cached plans, they're just part of other queries
2. I want a more accurate picture/history/log of the last time something was executed. I had two thoughts on how to accomplish this... suggestions?
Server-side trace if you want complete accuracy
2.b: Instead of modifying sprocs, write a new scheduled job that every X hours goes and looks at dm_exec_procedure_stats and dumps the results into a table.
Cons: May be imperfect information. Perhaps a sproc is only run once every week, but it is a very tiny sproc with a cheap execution plan... would it perhaps get purged from the cache very quickly and I would miss recording it during my hourly scheduled check?
Can't collect detailed data (though I don't care about that for these purposes)
There are also things (with recompile) that result in a proc's plan never appearing in the cache at all.
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
April 25, 2011 at 9:52 am
Makes sense, thanks!
Unrelated:
Also, I didn't know you considered yourself a "Ranger", awesome series.
(I also never noticed your tag line before... is that new as well?)
April 25, 2011 at 9:58 am
Maxer (4/25/2011)
Also, I didn't know you considered yourself a "Ranger", awesome series.(I also never noticed your tag line before... is that new as well?)
Marcus was one of my favourite characters, and I love the concept of the anla'shok. The tag line has been there for quite some time.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply