April 15, 2011 at 10:03 am
I have two instances on a win2k8 enterprise x64 box (virtualized). The default instance is 2k8 and the named instance is R2. I've got 20 gig of ram 'installed'. The named instance is running at the max server memory setting of 7.5gig. The default is showing max server mem of 7.5 but only 848mg MAX is used even though the 7.5 is allocated to sql. This is grinding our main website to a halt.
Verified:
Lock pages in memory set for the accounts used to start sql
AWE enabled (there seems to be a debate about this setting, i've toggled it on/off with no difference)
min server memory 7000mb (both instances)
max server memory 7500mb (both instances)
Edit: Using Performance Monitor I get Target Server Memory for the problematic instance of 6.656000 but total server memory is 397680 and resource monitor shows 48% Used Physical Memory
April 16, 2011 at 3:46 am
Hi,
have you restarted the default instance? How much physical memory is available on the virtualized server and how much on the esx host system? So are you sure that it getting enough memory from the host system? Please post the results of the memory manager performance counter group.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 16, 2011 at 3:46 am
Hi,
have you restarted the default instance? How much physical memory is available on the virtualized server and how much on the esx host system? So are you sure that it getting enough memory from the host system? Please post the results of the memory manager performance counter group.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 16, 2011 at 10:40 am
Thanks for the reply Patrick, Yes I have restarted the default instance and the named. The server has been rebooted to no avail. The server was just rebooted and I'll send the perfmon trace as it ramps up through the day.
I dont know the exact amount of ram on the host but Systems moved it to a new host with no other vms on it to try to rule out virtualization. The host system has >100gig of ram and I'm only using 20.
I've currently done the following, moved the production databases off the default instance to the sql2k8 instance last night in case it is not solved by monday morning. This box is running sitecore which powers our website. very public failure if it locks up.
Double checked the PAE switch in Boot.ini as Hot Add/Swap memory is not available on the host server. AWE is enabled,
max server memory on the named instance is 10.5 gig,
min memory 10 gig,
reporting services was showing using 2.5 gig, (report server still on the default)
The default instance has been reduced to 2.5 max and 2.0 min
Ram totals 15 gig for sql and 5 gig for the OS and other services such as Full Text Filter (this is a dedicated SQL box with no other apps on it) I'll look up how to determine the required resources for Full Text to make sure that this is not contributing to the problem.
SSAS service disabled
SSRS enabled
SSIS enabled
Full Text enabled
April 19, 2011 at 9:55 am
Hard lesson learned. Dont let anyone build a box for you to run SQL without checking their work. The Host server for this virtualized database server does not have hot swap ram therefore you must have the /PAE switch in the boot.ini. I was getting very odd metrics and started over at the beginning. After setting the switch and rebooting the server the issues went away. The traces I was running showed I was using more than 3 GIG of ram though.
??
'nix
April 19, 2011 at 10:01 am
Set only Max Server Memory on both Server and dont set minimum memory and install the Service pack for SQL Server 2008 also if not
There is no need to set /PAE parameter in Windows Server 2008 and if this virtualized then its showing from the HOST server allocated RAM
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply