We have a microsoft cluster set up with several different databases, we have 5 different webservers that connect to the databases nad a very frequently visited site. a couple of days back we starrted experiencing a problem when posting data into one of the databases, the specific database is the biggest one we have, just below 8 gb of data ( all of the data is basically in 2 different tables).
we run w2k advanced serve service pack 4 , ms clustering , sqlserver sp3.
we started getting messages of timeouts from the webservers, so the timeout was because data couldnt be posted in the database.
after trying to figure out what the problem was , running dbcc checkdb etc i found no errors, but decided to restore a backup. , i did so and things worked perfect again. but only for a days time.. this of course made me consider database /table size being a problem, i now deleted the tables that contained the data and scripted new ones, that "solved" the problem of being able to post data , BUT, we need to have the other data in there as well. i am a bit at a loss now , anύ clues out there?
Try a bit of a test. Run profiler on your new system with no data, and run some of the update statements that were timing out. Then, go to a test system (hopefully with all of the 8GB of data), and run the same profiler trace, and same updates on that database. If the durations of each update are wildly off, you may need to add an index in order to boost performance. This will take some experimenting on the test server.
If the durations are not very far off, then you may have a blocking problem. Look for big reports, or select statements that are grabbing large portions of these big tables. Good luck.