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 |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2003-03-19 : 12:24:10
|
| I'm watching performance on our server constantly. Right now, I have 2 computers. 1 I'm working on, the other, I'm running profiler on. Now, if a certain stored procedure takes +5 seconds to run, I start to get concerned since it's average time is about 3 seconds. This is my first indicator that something is going south on me. If it hits 10-15+ seconds, I know it's time to put the phone on make busy and start scrambling. What I'm trying to figure out, is a better way to do this. It's a pain in the ass having profiler up all the time. What'd be nice is to sample the query time for the sp over a few minutes and get an average. If the average is in my "critical zone", then page/email me so I can fix it before the phone calls start. I'm thinking something like alerts would be great for this, but I'm not sure how I would get it to run my stored procedure and monitor the results for me. Any ideas on this? I definitely want to find a cleaner(lazier) way.Mike"oh, that monkey is going to pay" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-19 : 12:40:15
|
| Well instead of running it through the GUI for Profiler, how about running xp_sqltrace and calling it from a stored procedure? I haven't done it before, but I'm sure that somebody could help you with it here.Tara |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2003-03-19 : 12:45:42
|
| Hey! That's cool. I didn't know that existed. I can play with that one. Still open to suggestions from others, but that's a good start. Thanks man!Mike"oh, that monkey is going to pay" |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-03-19 : 13:27:49
|
| This page might give you some tips. I used the info on that page to identify some of my "long running procs" that took almost 1 second to run :). I felt pretty good that my longest running procs took just less than 1 second :) http://vyaskn.tripod.com/analyzing_profiler_output.htmMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
A better way to Profile