Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
inTrance
Starting Member
2 Posts |
 Posted - 2007-10-15 : 15:11:31
|
| Hello!We've a SQL Server table with 6 million records and a data size of 1.5GB. This table is used by just one process that select mostly single records and updates them. Unfortunately the SQL Server becomes very slow after some work. After the memory consumption has grown to approx. 1.6GB, SQL Server needs minutes for every single query.Our configuration is:- Windows 2003 Small Business- SQL Server 2005 Workgroup Edition- 4GB RAM- 2 Xeon CPUsDo you've any hints for us? That's the first time that we are handling such huge tables.Thanks in advance,inTrance |
|
|
crosan
Starting Member
13 Posts |
Posted - 2007-10-15 : 15:22:12
|
| Is it slow all the time, does it get progressivly slower throughout the day, or does it just come to a standstill at some particular time of the day or during some other process? I guess I would be most curious to find out what "after some work" means exactly. Also, how big is the entire database? A 1.5Gb table on a system with only 4Gb of RAM may also be part of it, you may want to see how much your system is paging out to disk. |
 |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-15 : 15:28:04
|
| Also, make sure your table is indexed properly and not doing table scans. |
|
|
|
inTrance
Starting Member
2 Posts |
Posted - 2007-10-15 : 15:35:08
|
| It becomes progressivly slower while working with the database. There are two files for our database in the data directory. One has 3.5GB and the other has 2GB.We read larger text files (~ 200.000 records) into one table and insert/update this data into the main table. Additionally we've another table with 20 million records which contains n-m relations between our main table and a small table with 20 records. At the moment I am at home, so I can't check further details, but I'll post them tomorrow.Thanks for this first, quick reply! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-15 : 15:52:18
|
| Sounds to me like your application was written at at a time when you had much less data but as time has passed the volume o data has gorwn and the way in which it was programmed has started to show its vulnerabilities.From your posts there isn't really enough information to advise you. We would need to see th code for individual queries which are performing badly, and so on. But essentially it would be better to get a good consultant in to advise you; otherwise folk here may offer very reassuring noises, but in the absence of a sound diagnosis of the problems they would just be guessing.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-15 : 22:16:23
|
| Did you put /3gb in boot.ini file? How often do you rebuild index and/or update statistics? |
|
|
|
|
|
|
|
|
SQL Server stands still