November 25, 2009 at 9:13 am
I need to find Mostly Used Stored Procs in one of the databases of SQL Server 2000. is there any script which can give the info am looking for?
In SQl 2005, we can get this info by using two DMVs sys.dm_exec_query_stats , sys.dm_exec_sql_text....but not sure how to get this info in SQL 2000.
appreciate your help.
November 25, 2009 at 9:39 am
Unfortunately, there isn't a way that is quite that easy in 2000.
For a quick and dirty approach, you could do something like:
SELECT SUM(usecounts) TotalUses, pagesused, sql
FROM master..syscacheobjects WHERE usecounts > 1
GROUP BY pagesused, sql
ORDER BY SUM(usecounts) DESC
Note that this fails in many ways and is not going to be fully accurate, but it can give you some starter information while you wait on the next method to accumulate data.
Create a trace. Use SP:Completed. If you know how to do a server side trace, I'd advise doing that here and pushing the results out to a file due to the quantity of data and the length you will likely be running it for. The downside is that in SQL 2000 you cannot view the contents of that file until you stop the trace.
If you do not know what I'm talking about with server side trace, you could use Profiler, but it's going to have a higher performance impact on your server and may miss some events depending on the coding structure and activity level of your server.
Once you've let it run for a few days you can pull the data into a table and run analysis queries on it.
November 25, 2009 at 9:55 am
Since they are SP's you could add code that logs when an SP is used an then query that table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2009 at 12:50 pm
Jack Corbett (11/25/2009)
Since they are SP's you could add code that logs when an SP is used an then query that table.
For SQL 2000, this method seems to be the most frequently suggested - due to the lack of available tools to trace Proc usage.
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
November 25, 2009 at 1:08 pm
It can be hard, not to mention not necessarily acceptable in an environment, to change all procs. Someone will add a line of code, not realizing that there's been another change and deploy that to production.
A trace is how I've typically done this in the past.
November 25, 2009 at 2:23 pm
Was busy in one production issue. sorry for the late reply. Thanks all for replies...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply