October 14, 2009 at 1:06 am
Hello folks
I'm writing for some suggestions on using DMV's to monitor how frequently stored procedures are executed and how frequently tables are accessed?
For instance, DMVs like SYS.DM_DB_INDEX_USAGE_STATS can be used to monitor how often indexes are used and hence how often tables are accessed can also be deduced.
Is there any equivalent DMV for monitoring object access of other types, such as how often certain stored proc's are executed? And if not, what is a good way to monitor this?
Thanks!
October 14, 2009 at 4:31 am
October 14, 2009 at 9:32 am
db_bunny (10/14/2009)
Hello folksIs there any equivalent DMV for monitoring object access of other types, such as how often certain stored proc's are executed? And if not, what is a good way to monitor this?
Thanks!
A method that I use is to use a query such as
select object_name(p.objectid, db_id('MyDBName')) as ProcName
,s.last_execution_time as LastRunTime
,row_number() over (partition by p.objectid order by s.last_execution_time desc) as RowNum
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_query_plan (s.plan_handle) p
I take the results of that and throw it into a LogTable in my Admin database where RowNum = 1.
I then setup a job that runs daily. This will get me the procs that have been run during the monitored period. I can then query that table and compare the results to sysobjects to find procs that have not run with a query like the following:
select s.* from sys.objects s
Left Outer Join admindb.dbo.ProcLogTable 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
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply