Slow performance by alert engine
-
I have a strange circunstance here at my development, I Have SQL 7.0 and Windows 2000 server, and we are working with VB 6; the users call me to say that the application is very, very slowly, and when I run the SP sp_who2 on the Query Analizer, I can see that a process executed by the NT Adminitrator User that have an incredible Disk I/O and CPU Time, and the program name that execute this process is MS SQL Alert Engine. The question is....Why??? Because there is not any backup, alerts, jobs or restore in progress, and this situation is continue and very exasperant to my users and me.
If you have any idea or suggestions to tell me what can be wrong on my database settings, I will appreciate your comments, because I didn´t found information about this specific and angry topic
best regards
Mike Ruiz
-
Mike, this is no problem at all. The Alert Engine SPID will stay connected and show up in your sp_who2 output even if it is not doing any specific job at that specific moment. CPU and Disk I/O are added to over time, so if the server has been up for some time they will have large values.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Thank you Chris for your answer, but just a few commnets about this problem: I can understand that CPU and Disk I/O can grow continuosly, but I think that the problem could be any settings configuration that I can not see at this time, relationated with the SQL Server Agent, because I have a monster by server, with 4 CPU's, 1 GHz on RAM, etc, etc, but when I stop this service, then the performance and the transactions working very fast. I read that this service can fire an alarm when an error is found it, and if there is not configurated errors, then the Server Agent create a loop, taking resources of the entire server, is that true? How can I resolve this, or look for a solution on the settings server? Any idea, Chris? thank for your time again .
Mike
-
Never heard of that, but of course there could be some bug in it that could do that. Where did you read it?
Do you have any alerts defined?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Chris:
Ok, there is not defined alerts on the server but still there are jobs like backups and restores and SQL Books on Line says the next by "solve problems with alerts" :
CPU use is High
"As Agent SQL Server depends on SQL Server and she supervises it, it can be catched in an infinite curl shooting the same alert. This usually happens when SQL Server remains without some essential global resource and an alert for this event has been defined
When the activated number of alert exceeds the speed of process of Agent SQL Server, an overflow takes place
In order to eliminate an overflow of the process of the alert:
"Increase to the amount of time of the value Retardation between answers "
--(I don´t think that be the reason because I don´t have configurated alerts)--
"Correct the global problem of resources to prevent that the alert uses all the resources"
--Could be this? and How can I correct this "global resources problem"? Where?--
"Erase the registry of application of Windows NT if: the overflow does not disappear, it does not want to hope that Agent SQL Server eliminates the overflow or wants to have an empty registry of application Windows NT"
what means that? whenever an error exists, I must that to erase the registry of Windows?
Well, I hope you can help me and thanks again
Mike
P.D. I hope that I has written it correctly because I had it to translate of Spanish English
-
Hmm, interesting. I found the part in BOL so you don't need to worry about me misunderstanding you. They are not talking about erasing the registry in Windows, it describes clearing the Windows NT Application Log. Are you seeing a lot of errors in the application log? It is located in Control Panel, Administrative Tools, Event Viewer (if you have Spanish Windows it will have different names but I hope you know where to look). See if your application log is full of errors from some alert.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Chris:
Now, the darkness is leaving. Look, lately, the server do not concurre in this situation (about slow performance), because... somebody clean up the Application Log of Windows Server!! Then I can think the SQL BOL is right! and thanks for that you help me to understand that was the Application Log and not the Windows registry
Therefore, at this moment I don´t think this problem can be happen, but when occurs, I will scream for help again If this were not the reason of the error
Thank you so much
Mike
-
Ah, great that it's OK now. Interesting however what caused this. Probably have to wait until next time it happens to find out.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply