July 31, 2008 at 6:32 pm
Hi All,
I hope that I have posted this in the right forum...
I have a requirement to find the top N used stored procedures in a database and monitor their elapsed time at regular intervals. I have written a query to do this using the 2005 DMVs as shown below.
select
'Object' = object_name(objectid, dbid),
'UseCounts' = sum(usecounts),
'AvgTime' = qs.average_time
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle)
join
(
select
'average_time' = avg(total_elapsed_time),
plan_handle
from sys.dm_exec_query_stats
group by plan_handle
) as qs
on qs.plan_handle = cp.plan_handle
where objtype = 'Proc'
and db_name(dbid) = db_name()
group by objectid, dbid, qs.average_time
order by average_time desc
Fairly standard I would have thought. The problem is that after a short period of time (say 5 mins) without db activity the statistics seem to reset. The server isn't being restarted every 5 mins (at least I would hope not). I'm new to this so maybe I am missing something? Any suggestions would be appreciated.
Thanks,
John
July 31, 2008 at 8:07 pm
The information there is truly volatile. What do you mean by there is no activity during that 5 minutes? It could be that other activity is causing that information to "flush" out if there is activity.
Could you execute your script on a frequent basis and save it out to a table for later aggregation and analysis? Just a thought.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 31, 2008 at 8:17 pm
Hi David,
The idea will be to run the script every night and output the results to a table. On my test database I run some stored procedures and the query returns some stats. I then wait for a short period of time and no stats are returned. Surely if another user has performed some action to flush the plans from my stored procedure execution, they would have added plans for other stored procedures so the query should return *something*?
Also, I would expect that a stored procedure plan would be cached for a reasonable period of time for optimisation.
I hope I have explained it a little more clearly 🙂
John
July 31, 2008 at 10:25 pm
John,
You had explained yourself fine the first time. 😉 I'm not always so quick though...
What you are saying makes sense theoretically.
I did find this in relation to sys.dm_exec_query_stats
"The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. " http://msdn.microsoft.com/en-us/library/ms189741(SQL.100).aspx
So, first thing you may want to do is make that a left join to the aforementioned table and just to verify that there are not other things clearing that cache out eliminate the "objtype = 'Proc'" to see if there are other things taking up that space in the cache.
As for things staying in cache longer for optimization, that is true but it very much depends on other activity and what the memory pressure is on your box.
I would be very curious to see what your results are to see if we can get this working a bit better.
My initial suggestion might still hold true, not running it at the end of the day but rather running it every minute or so and dumping the results to a table for aggregation later. Does that make sense?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 1, 2008 at 8:13 am
Is the database set to autoclose?
Are there any databases on the server that are the target of a logshipping scenario (getting logs restored regularly)?
Is there a scheduled job that's freeing the proc cache?
While the plan cache is volatile, it's a little odd that it gets completely cleared every now and again. I would expect some plans to be removed and others to stay around longer
Take a look in the error log, see if there's any entries about cache flushes.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply