Memory and Database Server Specs - What are you using?
-
Hi,
I'm curiuos to know what a good database server should have?
I'm running SQL 2000 on a box with 2 processors; 145GB HD (which is nearing capacity), and 1 GB memory, which ran out yesterday "Insufficient memeory - Error: 17803, Severity: 20, State: 12".
Your input will help develop a doc to see if I can get Sanata to bring me a new box.
Thanks. Jeff
Many thanks. Jeff
-
How SQL Server memory be configured? Any other applications also run in same server? Which SQL Server service pack do you install? Any more information in SQL Server errorlog?
-
Hi,
The memory is configured as per default installation. No changes have been made. There are 32 databases ... 3 are big and consume 2/3 of disk. Plus users from these 3 are always using processes. SP3a is being used. This is only a database server, so no other apps are on it. Here is a snipit from the error logs. THanks.:
2004-02-03 12:36:04.57 spid70 DBCC TRACEON 1204, server process ID (SPID) 70.
2004-02-03 14:13:46.04 spid1 Dynamic Memory Manager: Stolen=127698 OS Reserved=4944 ...
2004-02-03 14:13:46.04 spid1 Procedure Cache: TotalProcs=14 TotalPages=436 InUsePages=436
2004-02-03 14:13:46.04 spid1 Buffer Counts: Commited=129864 Target=129864 Hashed=2166...
2004-02-03 14:13:46.04 spid1 Buffer Distribution: Stolen=127262 Free=0 Procedures=436...
2004-02-03 14:13:46.04 spid1 LazyWriter: warning, no free buffers found.
2004-02-03 14:13:46.05 spid1 Query Memory Manager: Grants=0 Waiting=0 Maximum=2019 Available=2019
2004-02-03 14:13:46.05 spid1 Global Memory Objects: Resource=3279 Locks=77 ...
2004-02-03 14:13:47.04 spid75 Insufficient memory available..
2004-02-03 14:13:47.04 spid75 Error: 17803, Severity: 20, State: 12
2004-02-03 14:13:47.05 spid87 Insufficient memory available..
2004-02-03 14:13:47.05 spid87 Error: 17803, Severity: 20, State: 12
2004-02-03 16:29:37.30 spid88 Process ID 52 killed by hostname DBSLC, host process ID 308.
2004-02-04 02:00:04.13 backup Database backed up: Database: ActivePDF, creation date(time): 2003/09/10(14:07:
Many thanks. Jeff
-
You need to determine what you need based on what it's used for. We can tell you what each of us uses but that won't really help you as we might be using it for something totally different than you. For example:
I have two servers, one shared array with 28 73GB hard drives. 4 1GB processors. My database storage is 800+ GB.
Do you need that much? Probably not. Perhaps a better description of what you USE your database for might help us. Someone might be doing something similar and tell you what hardware setup they have.
-SQLBill
-
Firstly, if the memory is configured as default then you'll have dynamic memory management enabled. This means that SQL Server will grab and release memory as needed. The default setting is a minimum of 0 and a maximum of your total memory. Try reducing the maximum to allow Windows to have some breathing room. It could be that your heavy users are grabbing a lot of memeory at the same time Windows is grabbing memory for some other internal process.
You say your server has 1GB memory, yet you databases nearly consume 143GB HD. Generally the more memory you have the better SQL server will perform as it can put more and more data in cache. Check out the size of some of tables that are being queried. If you have tables that are nearing 1GB and someone does a SELECT * from that table, no amount of memory fiddling will help. In this case if you can't change the process, you'll need more resources to handle the request.
--------------------
Colt 45 - the original point and click interface
-
Can this problem be reproduced?
-
What do you mean by more resources? Thanks.
"In this case if you can't change the process, you'll need more resources to handle the request.".
Many thanks. Jeff
-
More CPU power so the process completes quicker, more memory so SQL Server can cache more data, faster Disk I/O subsystem to process reads/writes faster.
Naturally any of these will require a bit of reasearch into what and why your application is doing what it's doing. EG: If your app is performaing heavy calculations, it may turn out that more CPU power is all that's required.
--------------------
Colt 45 - the original point and click interface
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply