SQL Server Performance

Optimum Memory config SQL Server 2005 64 bit SE

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by flynch, Apr 19, 2007.

  1. flynch New Member

    Hi ,

    I'm running SQL Server 2005 64bit SE on VMWare and would really appreciate advice on a simple, safe memory config.

    There is 8Gb memory and I would like SQL to be able to use 7Gb and Windows 1Gb.

    The min / max memory setting in SQL and the use of AWE switches are driving me mad!

    Help greatly appreciated.

    Many Thanks
    Fergus
  2. thomas New Member

    I've had a lot of trouble with memory on 64-bit SE. According to this thread,

    http://groups.google.co.uk/group/mi...71fc3bcbd4e43?lnk=raot&hl=en#a1171fc3bcbd4e43

    Std Ed ignores the 'Lock Pages In Memory' option (or more correctly, the Lock Pages In Memory windows service account permission), which is the 64-bit equivalent of AWE.

    I recommend you manually set a min server memory of 6GB and a max of 7GB. With it being a virtual server you may find you get these type errors

    A significant part of sql server process memory has been paged out. This
    may result in a performance degradation. Duration: 0 seconds. Working set
    (KB): 40820, committed (KB): 91192, memory utilization: 44%.

    which I am getting on a virtual 64 bit SE server. Seems like there's nothing we can do about it. I don't know to what degree performance is degraded. On my server I have 2GB RAM for SQL Server and it does appear to be taking a good chunk of this. So I suspect this error may actually be spurious and that SQL Server is getting confused by something, possibly the virtualisation.
  3. flynch New Member

  4. thomas New Member

    Are you getting this issue?

    I'm not actually seeing performance problems (yet), just these errors in the log. Some operations have been slow, the other day even a simple xp_readerrorlog took 18s for a small error log.

    Trouble is this particular server is not yet in Production. My concern is, that when it goes live, it will creak. Of course I was not consulted on whether putting a live server on a virtual was a good idea, I was merely handed it as a fait accompli. Urgh.
  5. satya Moderator

    No need to change any windows settings, 64 bit windows automatically gives relevant memory of user mode address space. The sp_configure option for AWE is present on the 64 bit version, but ignored.

    One of the few differences is that if the sql startup account is granted the "Lock pages in memory" permission SQL will lock all buffer pool pages in memory, thus making them inelligible for paging by the OS.

    Relevant whitepaper on the chip part of ithttp://www.intel.com/business/bss/products/server/64-bit_tipping_point.pdf

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. thomas New Member

    Yes but Satya the point of the thread I linked in my first reply is that according to someone at Microsoft, "Lock Pages in Memory" is IGNORED in Std. Edn., it only has effect in Enterprise Edition.
  7. joechang New Member

    running sql server in vmware is just asking for pain
  8. thomas New Member

    Word. It was chosen before I'd even been invited to the party, sadly.
  9. flynch New Member

    Hi Thomas,

    After following the instructions at ....

    http://blogs.msdn.com/psssql/archiv...ations.aspx?CommentPosted=true#commentmessage

    My Procedure Cache has been consistently over 95% and users have reported a significant improvement in performance.

    Some of the references in this document are straining at the limits of my technical knowledge! However I would recommend at least trying this out on a test rig.

    Fergus
  10. satya Moderator

    Hm, setting up on VMware very much grey area to comment. Only way is to test and apply, even if you approach CSS you will not be with much luck.

    TOm
    I need to check that back with Slava, when I have had similar discussion this wasn't mentioned for it.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  11. thomas New Member

    I'm going to see CSS in London next week, but the band from Brazil not the MS Support people.

    Thanks flynch, I'll have a look at it.

Share This Page