June 9, 2006 at 9:04 am
Hi,
I'm sure this is a problem many of you have had so I'll probably kick myself when I see the answer. I have been asked to provide stats on which stored procdeures on our SQL2K system are called most frequently and how often they are called.
I have a suspicion this information is available but i can't think where. Is there something in performance monitor? Will a trace kill the performance?
Thanks in advance.
S
June 9, 2006 at 9:24 am
Take a look at the master.dbo.syscacheobjects table. This is a virtual table that will list every compiled plan in the cache for every database on the server. Any currently-compiled procedure will be listed in there (along with all of the ad-hoc stuff), including a usecounts column that specifies the number of times the plan has been used. The dbid and objectid, or the sql columns can be used to determine what procedure/query/etc has run.
Active caches (due to lots of ad-hoc stuff / recompiles / huge plans / more active procs than memory / etc) will not have this info as accurate as you like, because less-important plans will be aged out to make room for new ones. But it can certainly point out your busiest procedures (sort on usecounts DESC), largest - and likely inefficient - plans (sort on pagesused DESC), ad-hoc/dynamic queries (sort on sqlbytes DESC), and so on.
Again, this is not necessarily a complete picture. Stored procedures that are often recompiled (they contain DDL or reference very active tables) will have their plans rebuilt, which IIRC resets the usecounts value. It also doesn't answer every question you've been asked. But it can provide a lot of info with zero performance hit.
-Eddie
Eddie Wuerch
MCM: SQL
June 9, 2006 at 9:37 am
Fantastic, this is just what was hoping to hear.
Thanks very much
S
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply