Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-10 : 08:58:50
|
| Tim writes "Greets!1) Is there any way to find out what stored procedures, views, etc. are actually being used? We've had 10 too many consultants helping out over the years and it seems that SP's just get left on the server and they haven't been used nor will they ever be used.I'm not just blaming the consultants, I too am guilty as charged. We've been monitoring SP's by running xp_sqltrace and that may be our best option and we'll continue to do so to get a list of used SP's.We've really been trying to clean up the system lately and it's become quite time consuming. ;-)We're also trying to find out whic SP's are taking the longest to run, but this we're also having to do on a hit and miss basis. Basically, we get an idea of what all is run throughout the day via xp_sqltrace and then we systematically run each of the queries and view the query plan and then we go to work. If there's an easier way, I'm all ears. ;-)Best Regards,-=tim" |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-07-10 : 12:23:02
|
| If you have SQL 2000, you will find the Profiler useful. It allows you to start a trace which can be customized to log stored procedures specifically. Included in that trace are vital statistics involving the duration of each completed stored procedure (and the parameters it was run under).This list should at least help you identify which storedprocedures are being commonly used, and give you a short list of procedures to troubleshoot or optimize based on their duration.The profiler works on 2000 and 7.0 - it may work on other versions but that is outside my experience. |
 |
|
|
|
|
|
Determine what SP's are being used (SQL 6.5)