September 5, 2007 at 7:04 am
Hi all
I've got a win 2000 SP4, 4 CPU, 4 GB RAM sql server 2000 standard edition SP3 box with 12 databases.
Memory configured dynamically and nothing else runs on this box.
All databases feed magazine websites.
4-5 times a month we experience very heavy traffic for one of our magazine sites (that's when another global portal like AOL or Yahoo creates a link to our sites).
we get about 300,000 to 800,000 connections sometimes within 1-2 hours.
At that time SQL Server starts to decline in response time and then just becomes unresponsive.
And we have to restart the server to bring it to a stable level. If we wait we start to get alerts from our monitoring tools letting us know that all the 12 sites are down.
Now i've been reviewing our application code (it doesn't look good at all) and fixing some of the sql by converting them to sp's and replacing temp tables with subqueries. I would like to know if there are any sql server settings i can change like worker threads to at least make the server stay stable and not deal with locks and timeouts.
I added more memory for queries from 1024 to 2048.
I've been using "with (nolock) and (rowlock)" for some of the procedures but would like to know if anyone has any other ideas
Thank you
Any help appreciated
September 5, 2007 at 10:01 am
Is some kind of clustering/load balancing configuration (adding in another server or 2) an option?
Ken
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 5, 2007 at 11:57 am
not right now.
we are moving towards LAMP in the near future.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply