Recommended Memory Settings for Nav 3.7/SQL Server 2000

dpracilio
dpracilio Member Posts: 22
edited 2006-02-02 in NAV/Navision Classic Client
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.

Comments

  • kriki
    kriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • bbrown
    bbrown Member Posts: 3,268
    What editions of SQL Server and Windows Server are you running(Standard/Enterprise)?
    There are no bugs - only undocumented features.
  • DenSter
    DenSter Member Posts: 8,307
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • bbrown
    bbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.
  • dpracilio
    dpracilio Member Posts: 22
    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
    kriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • bbrown
    bbrown Member Posts: 3,268
    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.
    There are no bugs - only undocumented features.

Categories