SQL Server Performance Forum – Threads Archive
Help with suspected server overload
Hello, this is my firs time posting here. I’v been searching these forums alot for a possible answer but couldnt come up with it, so i figured i might as well just bring the topic up. I’m running an ASP.NET application, the application itself is huge, completed with thousands of user controls and pages. During peak times the website gets around 1000-1200 sessions active for lengthy periods of time. The application itself is located on a frontend server, HP Proliant GL380 G4.The database server is an old (4year old) HP Proliant ML370 G1 (dual PIII 733mhz, 1gb sdram) My problem is that the ASP.NET application crashes frequently, hard crash leaving an event log entry that looks like this :
Date : 29/12/2005
Time : 16:16:30
Type : Error
User : N/A
Computer : XXXX
Source : .NET Runtime
Category : None
Event ID : 0
Description :
The description for Event ID ( 0 ) in Source ( .NET Runtime ) cannot be found.
The local computer may not have the necessary registry information or message DLL files
to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description;
see Help and Support for details. The following information
is part of the event: .NET Runtime version 1.1.4322.2300- Setup Error: Failed to load
resources from resource file
Please check your Setup. I’v done all the neccessary debugging on the application and i just cant find anything that is wrong with it, so i decided to see if it was the outdated SQL hardware,
I have ran performence logs and the profiler on the database server for a period of 3 days, to try and see if my CPU load is bad queries or just high visitor load, the results are as follows : Over the length of 3 days, 70% of the CPU usage is topped at 100%, while the rest 30% is between 10%-80%, Checking the profiler output there is no lengthy executed queries, only when the application is crashing and restarting itself the big queries initialize and then being cached, no further indication of a CPU load could be found. My question is, if the database server, due to its limited hardware, can cause a hard crash to an ASP.NET application? how can i find the link between those findings and what actually is wrong? We are thinking of acquiring new hardware but we have to be sure its the DB server that is the problem and not the application itself. I appricate your time reading this, and id appricate input of any kind.
Thank you, NCIL
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlexceptionclasstopic.asp for the error referred to the source on the event viewer log. Take help from these articles
http://www.sqljunkies.com/Article/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk
KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;224587 and
KBAhttp://support.microsoft.com/kb/835864
…and perform the steps defined for further assessment. BTW what is the memory allocated to SQL Server?
Is this a dedicated SQL Server?
What is the service pack on SQL and operating system?
ANy recent changes to the infrastructure of Windows or SQL SErver? Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thank you for the answer! As for your questions, 1. There is 755mb allocated to the SQL server.
2. The server is indeed a dedicated server.
3. SQL version : Microsoft SQL Server 2000 – 8.00.534 (Intel X86) (reports SP3, but looks like SP2?), Windows 2000 SP3
4. No, but once we disabled the Boost priority check in the Process tab in SQL settings the application started to crash every 10minutes sharp (almost), then we checked it again and the application went back on crashing once every 30-40 minutes, still crashes every 15mins during peaks tho. Hope that helps, looking forward for more help!
1. It could be best to leave the SQL Memory settings to DYNAMIC than a max and min values.
3. 8.00.534 is SP2.01 which could be a troubled service pack and the latest one is SP4 + Q904660 (8.00.2162). You must test the implications of latest service pack on the test platform to avoid such issues or AV errors from Application to SQL Server.
If this is a third party developed application then take help from Vendor. So try to update the service pack level to the upto date level in order to fix few bugs on Windows, Application & SQL Server levels. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Sounds more like an application related problem than SQL. Run IISstate to see if it can catch any exceptions: http://www.iisfaq.com/default.aspx?view=P197
To me is application problem. May you have to contact vendor.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
quote:
I’m running an ASP.NET application, the application itself is huge, completed with thousands of user controls and pages. During peak times the website gets around 1000-1200 sessions active for lengthy periods of time.
Does app take advantage of connection pooling?
I’m running an ASP.NET application, the application itself is huge, completed with thousands of user controls and pages. During peak times the website gets around 1000-1200 sessions active for lengthy periods of time.
quote:
I have ran performence logs and the profiler on the database server for a period of 3 days, to try and see if my CPU load is bad queries or just high visitor load, the results are as follows : Over the length of 3 days, 70% of the CPU usage is topped at 100%, while the rest 30% is between 10%-80%, Checking the profiler output there is no lengthy executed queries, only when the application is crashing and restarting itself the big queries initialize and then being cached, no further indication of a CPU load could be found.
That cpu usage is crazy! Problem can be solved by much faster and/or more CPUs or by tunning queries and indexes. It doesn’t have to be one bad query, such problem may be caused by extensive use of cursors, frequent query (re)comilations and/or bad index design (if there is big i/o and parallel execution plans too).
I have ran performence logs and the profiler on the database server for a period of 3 days, to try and see if my CPU load is bad queries or just high visitor load, the results are as follows : Over the length of 3 days, 70% of the CPU usage is topped at 100%, while the rest 30% is between 10%-80%, Checking the profiler output there is no lengthy executed queries, only when the application is crashing and restarting itself the big queries initialize and then being cached, no further indication of a CPU load could be found.
quote:
My question is, if the database server, due to its limited hardware, can cause a hard crash to an ASP.NET application?
I don’t know the answer, it looks like there is a correlation between db server performance and your queries, but I don’t know how it can cause app crash. The only reason I can imagine is not handling timeout exception properly. Maybe connection are not released after timeout exception and in peak period resources are quickly exhausted. However, I don’t know asp.net so I could be wrong.My question is, if the database server, due to its limited hardware, can cause a hard crash to an ASP.NET application?
quote:how can i find the link between those findings and what actually is wrong?
You can group by substring(textData,1,50) and order by a sum(cpu) desc if you put trace result in the table. That way you find the most cpu intensive queries. If you call only stored procedures from asp.net you can do it on procedure bases. See if cursors are used and how often queries/sps are (re)compiled. That way you find out what is causing high cpu usage, but I still don’t know if it is the reason for application crash.
Thanks for the help fellas, we’ve found the problem was in the application itself, a recursive call that was a tough find!<br /><br />hopefully we’ll be able to fix it and see how it goes from there!<br /><br />Thanks a bunch everyone <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
Please let us the vendor’s investigations and root cause, that may help others too. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Well, the problem was a stack overflow in the polling system of the applicataion. It was traced using DebugDiag and Wndbg. Thanks to Jim Cheshire’s blog regarding ASP.NET debugging (which is off topic but still an excellent blog which can be found here <a target="_blank" href=http://blogs.msdn.com/jamesche/>http://blogs.msdn.com/jamesche/</a>)<br /><br /><br />Thanks again for your help <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />
I appreciate your feedback to post the resolution and this helps others too.
Keep it up. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>