Optimum Memory config SQL Server 2005 64 bit SE | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Optimum Memory config SQL Server 2005 64 bit SE

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
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.
Hi, Have a look at: http://blogs.msdn.com/psssql/archiv…ations.aspx?CommentPosted=true#commentmessage SQL Server becomes sluggish or appears to stall on 64 bit installations
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.
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.
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.
running sql server in vmware is just asking for pain
Word. It was chosen before I’d even been invited to the party, sadly.
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
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.
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.
]]>