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...
