Recommended Memory Settings for Nav 3.7/SQL Server 2000

dpracilio

Hi Guys,

I want to configure SQL Server 2000 to run at the most optimal with the amount of physically memory we have on our server. It is a high-end x86 dual xeon server with raid 1, raid 10 arrays with 4 gb of memory setup as a cluster. What settings do you recommend I set and how much shall i reserve for the SQL instance?

Thanks,
David.

kriki

The more memory you give to SQL, the better. But be sure you don't give to much so things get swapped to disk. If you leave 300MB-500MB for other processes, you're fine.
Don't run anything else but SQL-server.
Don't put the log-file and the DB-file(s) on the same disks.

Search for SQL on the forum for more info. There is a lot of it.

bbrown

What editions of SQL Server and Windows Server are you running(Standard/Enterprise)?

DenSter

You'll need SQL Server Enterprise edition if you want to use more than 2GB. Set your memory usage to fixed, and play around with it as to the allocation of it.. Don't set it to dynamic memory usage.

Here are some tips:
OS + SQL Server exe on one RAID1 array
Database files (mdf and ndf files) on separate RAID1+0 array
Log file on its own RAID1+0 array

That's most of the SQL Server finetuning that you can do with big performance gains.

bbrown

A few additional comments/suggestions:

You will also need Windows Entrprise Edition for SQL to use more than 2 GB of memory. Do not enable AWE memory on servers with less than 8 GB of memory.

The first choice for logfile placement is a dedicated RAID 1 array. This means NOTHING else is placed on this drive. The sql log is a sequentially accessed file. If the log file will not fit on a single drive, then span it across multiple RAID 1 arrays or use RAID 0+1.

dpracilio

Hi Guys,

Thanks for replying so quickly.

I do have Enterprise Edition and Windows Server Ent. 2003. We have 4 GB of physical memory so im thinking of allocating a fixed amount around 3 GB to SQL.

We have the quorum, log file and first part of the db on sep. dedicated Raid 1 arrays and the second (main) part of the db on a Raid 1+0 array.

Bearing in mind the Raid 1 volume that the log file is placed on is 72 gb, do you forsee this getting used up quite quickly with a 70 user database and a transaction log that will be backed up hourly.

Dave

kriki

dpracilio wrote:

Bearing in mind the Raid 1 volume that the log file is placed on is 72 gb, do you forsee this getting used up quite quickly with a 70 user database and a transaction log that will be backed up hourly.

With so much space on the disk and every hour a transaction-log-backup, you will never reach it. I think if it gets to use 10 GB, it will be a lot.

bbrown

Depending on the size of the database, the one exception might be restores of *.fbk files. The Navision restore is a logged event and can grow the transaction file quite a bit.