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 |
|
Rand773
Starting Member
2 Posts |
 Posted - 2004-05-24 : 17:21:29
|
| Is there a way so set up an alert that will send a email notification when a process reaches over 1 million cpu?Arthur D. LorenziniDatabase AdministratorState of South Dakota |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-24 : 18:22:41
|
I think you'd have to write something to handle that. I don't know of anything built-in that would look at that.You could write a job that looked at sysprocesses to see if there were any over 1 million, an then have the job send you an e-mail.Take a look at this to get an idea how to capture that "1 million CPU" number.Use Mastersp_helptext sp_who2 Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-24 : 18:32:52
|
| What does 1 million represent though? The CPU column from sysprocesses is just the number of milliseconds of CPU the connection has used since it logged in. So if a connection has run lots of queries, this column will be summed up for each of them. Is that what you want? And why 1 million?Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-24 : 18:48:33
|
| Methinks this is a "find the long running queries" job.I'm with Tara in that this number might not tell you too much, depending on how connection pooling is happening for you. One connection might be used over and over, and you would probably see lots of CPU time for that connection, but it doesn't mean that the last query used over 1 million ms.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-24 : 18:51:56
|
| I would setup a trace in SQL Profiler and send the results to a table. Then run this to find the long running queries:SELECT TOP 10 TextData, DurationFROM TraceTableNameWHERE TextData IS NOT NULLORDER BY Duration DESCTara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-24 : 19:14:52
|
| Here's a good article on doing what Tara suggested (and then some):http://vyaskn.tripod.com/analyzing_profiler_output.htmPlease note that running profiler does incurr a performance hit while it's running. It usually best to run it for a short period of time (a few hours, and not more than 24 hours) and log the results to a table for analyzing later.MichaelBTW over 1300 posts now W00t!<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
Alerts