Unanswered: what the most fequently used procedures?
i need to find out what the most fequently used procedure are?
i have over 6000 procedures, but all arent used everyday. i need to find out the most fequently used ones? i have see from monOpenObjectActivity the number of times a object has been used, can cant tell what ones are fequently used?
monOpenObjectActivity shows tables, not procs. BTW, UsedCount is actually "OptimiserUsedCount", depending on what you are looking for, you may need monOpenPartitionActivity.UsedCOunt or RowsInserted/Deleted/Updated.
monCachedProcedure shows procs. There is no UsedCount. Make sure you check ObjectID and PlanID to count instances of a proc in the proc cache.
Both tables are transient, it works only when you (a) capture snapshots at relevant points in time, and (b) inspect the series of snapshots. Unused procs will disappear as they get aged out of the proc cache.