Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have about 200 desktops across the US using our point of sale/website application on a daily basis. This application is considered mission critical and cannot go down for our retailers.

For the last few months users have been experiencing severe application slowdowns and crashes. Procedures that should take 1-2 seconds are taking 10-20 seconds now.

These last started just before peak periods in the flower business (just before Christmas) and now again (just before Valentines day). One general pattern we see is that the heavier the load, the more the system seems to slow down and crash. We added more memory and disks after Christmas to see if this would help stabilize the system.

An outside DB Admin and senior .net programmer were brought in to analyze and pinpoint the primary cause of these issues. We have looked at both hardware, software and network side of thing. The DBA Admin spent many hours analyzing the database and made some tweaks and modifications over the last several weeks.

The preliminary determination is that certain stored procedures or reports within the application were poorly written and pulling to many resources (or creating endless loops in some cases). Some procedures were disabled. The DB Admin is working with a programmer to optimize these procedures currently, and correct the errors.

We have not been able to consistently reproduce the crashes or slowdowns ourselves.

Our current set up is as follows: asp 2.0 Database- Dell 2950- 20Gb SQL Server 2005 (8 x 73GB) About 20GB in size Webserver- Dell 1950- 8GB Windows 2003 R2 Enterprise SP2 (4 x 73Gb) iis6 (memory use about 50% with 4 application pools currenlty).

Any comments would be greatly appreciated before I pull all my remaining hair out...

share|improve this question
    
So you've a diagnosis already? –  TristanK Feb 8 '12 at 11:28

2 Answers 2

up vote 0 down vote accepted

Can't offer anything specific, especially if you have already had some outsiders work on it. If it only started in the last few months then try finding what changed or check your hardware.

Any Windows updates? antivirus programs changed or start running scheduled scans during the day? backup jobs? a disk got close to full? sql server service pack installed? you did check the Event logs, right?

Also check your network cables and cards. Any network errors? A bad cable, NIC or switch might look ok according to the LEDs but could be faulty, running half duplex, dropping packets etc.

Did you make any changes to the software? break or drop some indexes?

If your version of SQL Server includes the Profiler then set it up to record slow queries.

Or add some monitoring tools like nagios that email you as soon as the CPU load starts to climb. Then you can log into the box immediately and start investigating.

share|improve this answer

When weird things start happening for us we setup DebugDiag1.2 to catch and log the crashes, and Failed Request Tracing with .net page tracing to determine exactly what bad requests are doing.

DebugDiag: download from http://www.microsoft.com/en-us/download/details.aspx?id=26798

Some instructions here http://blogs.msdn.com/b/tess/archive/2009/03/20/debugging-a-net-crash-with-rules-in-debug-diag.aspx I would set it up with 'action type for unconfigured first chance exceptions' as 'log stack trace' unless you are really good at looking thru dumps.

Failed Request Tracing:

Setup here: blogs.msdn.com/b/tess/archive/2008/08/19/troubleshooting-a-performance-issue-with-failed-request-tracing-and-appcmd-in-iis7.aspx with your own page traces: msdn.microsoft.com/en-us/library/bb386420.aspx

links removed coz I dont have enough rep :(

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.