SQL Server Performance

#$%#$% memory problems!!

Discussion in 'General DBA Questions' started by Will192, Jan 30, 2006.

  1. Will192 New Member

    This past weekend we added 2gb of memory to our server, bring it to 8gb. We are running Win2000 Advanced Server with SQL Server 2000 Enterprise Edition.

    Before the memory upgrade I had the SQL Server manually set to allocate 4gb out of the 6gb to SQL Server.

    After the upgrade I manually set SQL Server to allocate 6gb out of the 8gb to SQL Server.

    Now the fun stuff starts. Now jobs start failing, the master database is reported to have problems(but no DBCC command reports any errors) and other weird things.

    I manually set it back to 4gb and nothing changes, still weird behavior. I manually set it to dynamically take between 1gb and 6gb based on needs and everything starts to work fine. (so far)

    I have worked with Dell and have ran their memory diagnostics and they say everything is fine. The server is all Dell parts and all the memory came from Dell.

    What the #$%#^& is going on?

    Live to Throw
    Throw to Live
  2. Twan New Member

    is awe enabled?

    are the /3gb and /pae switches in the boot.ini

    Cheers
    Twan
  3. Will192 New Member

    Yes. They have been for about a year.

    Live to Throw
    Throw to Live
  4. joechang New Member

    i would suggest you remove the /3GB from boot.ini
    then see if you can increase the SQL Server memory in increments to 6GB
    the problems you are having are probably actually address space,
    a 32-bit OS has 4GB address space, of which 2 or 1 GB is allocated to the OS,
    many programs report this as a memory issue, which is not correct,
    the /3GB switch reduces the address space allocated for the OS,
    the /PAE can also increase OS memory & address space usage, but not if you reduce the amount of AWE memory used by SQL

    anyways, the correct method for assessing whether SQL needs more physical memory is disk IO and procedure cache evictions,
    not out of memory messages, which actually indicates a shortage of address space
  5. Will192 New Member

    So if I have a Win2000 Advanced Server with 8gb of memory and I want to allocate 6gb of memory to SQL Server, then I don't want the /3GB switch in my boot.ini file?



    Live to Throw
    Throw to Live
  6. joechang New Member

    not necessarily,
    if you have a well designed app, extensive use of stored procs, very few compiles, no use of cursors, other API Server cursors, extremely careful use of xprocs,
    these apps frequently run fine with both /3GB and /PAE

    apps designed by people who did not understand what was going on under the hood cab cause SQL to crash through some combination of /3GB and /PAE

    watch in Perfmon, the process object, the sqlservr instance, virtual bytes and private bytes counters, also the SQL Server errro logs, look for messages like: fail to allocate 65536 bytes or 131072
  7. Will192 New Member

    I got AWE enabled last night, told SQL server to take 5.5gb out of the 8gb, not a range of memory.

    I rebooted and let the nightly jobs run. There are some big jobs that run overnight, joining tables with millions of rows, etc.

    Everything ran fine with no errors last night!!! I will continue to monitor it, but it appears everything is working fine and SQL Server is using the memory.

    Thanks for the response(s) to my post.

    Live to Throw
    Throw to Live
  8. joechang New Member

    so much for dell support, don't bother unless you really think there is a hardware fault

Share This Page