October 31, 2006 at 8:27 am
Hi All
I would appreciate any help from you all with regards to this:
I have a SQL 2005 standard installation with windows server 2003 enterprise Operating system
The server has 7GB of memory available to it, but as I know SQL 2003 standard edition would make use of the total server memory available to it.
Here are the changes that I have made. (I have converted it all to GB for easy reading)
I added the 3GB switch to the boot.ini file
I enabled AWE
I set mini server memory to 5GB
I set max server memory to 6GB
I turned on Performance monitor and the Target server memory counter = 6GB, the total server memory is also set to 6GB.
Now after all the changes above, the server seems to run slower and looking at task manager, the PF usage is around 6GB.
Can anyone please confirm that the changes I made were indeed correct and possibly educate me on the configuration and how to make the best use of server memory etc.
I look forward to hearing from you all.
Thanks
October 31, 2006 at 10:04 am
Take a look at this link...
http://support.microsoft.com/kb/283037
I believe when I pointed out about AWE I said something about "other switches when more than 4 GB" is present. I haven't had one of those... you will need to investigate a bit; but I think this goes in the right direction...
Pay particular attention to the comment below (from that link):
AWE is a set of application programming interfaces (APIs) to the memory manager functions that enables programs to address more memory than the 4 GB that is available through standard 32-bit addressing. AWE enables programs to reserve physical memory as non-paged memory and then to dynamically map portions of the non-paged memory to the program's working set of memory. This process enables memory-intensive programs, such as large database systems, to reserve large amounts of physical memory for data without having to be paged in and out of a paging file for usage. Instead, the data is swapped in and out of the working set and reserved memory is in excess of the 4 GB range. Additionally, the range of memory in excess of 4 GB is exposed to the memory manager and the AWE functions by PAE. Without PAE, AWE cannot reserve memory in excess of 4 GB.
The following is an example of a Boot.ini file where the PAE switch has been added:
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
October 31, 2006 at 11:51 am
Hi David
Thanks for the reply, what i am trying to understand now is that what other witches/what have I done wrong.
I have read the Ms link and I believe I have done what I was supposed to do. Its just happened that operations are now taking longer than when AWE wasnt enabled.
Please advice.
Thanks
October 31, 2006 at 1:40 pm
as I read the page, to use AWE on a machine with more than 4 gb you do NOT use the 3gb switch; but the /PAE. Your post says you have used the 3gb switch. and that link says it will run slower if PAE is not enabled.
Without PAE, AWE cannot reserve memory in excess of 4 GB.
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE < NOTE: NO /3GB switch... just /PAE
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
October 31, 2006 at 3:59 pm
November 1, 2006 at 7:46 am
I like x64 bit. No switches needed. Don't know if your server is capable, but might be an option.
GE
November 1, 2006 at 11:31 am
I can only provide the link... seems to me that you aren't reading the MS supplied article that says you must add the switch. That is just one link... if you do a few googles, as I recall, there is information all over the place. I don't have a machine with more than 4 GB... and all of my servers are running fine.
Not sure how much clearer an article can be, from MS:
"PAE is the added ability of the IA32 processor to address more than 4 GB of physical memory. The following operating systems can use PAE to take advantage of physical memory beyond 4 GB:
• | Microsoft Windows 2000 Advanced Server |
• | Microsoft Windows 2000 Datacenter Server |
• | Microsoft Windows Server 2003, Enterprise Edition |
• | Microsoft Windows Server 2003, Datacenter Edition |
To enable PAE, use the /PAE switch in the Boot.ini file."
Perhaps they aren't correct about their product. they've been wrong before... and maybe there is conflicting information that leads you to believe otherwise. If my machine was running slow, I would certainly TRY to follow their advice and set the switch.
I'm done here.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
November 1, 2006 at 1:14 pm
John,
David is correct in regards to adding the /PAE switch. This may be contributing to what you are seeing in terms of your paging file usage. I do disagree; however, with not using the /3GB switch. You want to use the /3GB switch up to 16 GB of memory. On systems with 16 GB of memory or more, remove the /3GB switch.
So what should you do? Add both the /PAE and /3GB switches to your boot.ini file, reboot the server, make sure AWE is enabled and re-check your min and max server memory settings. Let us know the results please.
November 2, 2006 at 5:39 am
November 2, 2006 at 8:28 am
one thing you need to remember, when setting the memory min and max, you need to leave some memory for the OS as well. with the 7 gigs you have there, I guess I would allocate no more than 6 gig to SQL, personally, I would go 5 gig - leaving 2 gig for the OS and other processes.
my 2 cents
-- Cory
November 2, 2006 at 8:42 am
John,
Cory is correct. If you have 7 GB of memory in your server, your Max Server Memory setting should be no higher than 6 GB. If your server is a dedicated SQL Server, I would leave 1 GB of memory for the OS. If you are running any other applications/processes on the server, I would leave 2 GB of memory for the OS.
November 2, 2006 at 3:11 pm
Hi
I think I have managed to figure out what the problem is, I reduced the max server memory to 3GB and the time the query runs has reduces significantly, i.e from 45mins to less than 20mins.
Although AWE is enabled and there is 8GB physical memory, if i set max server memory to 7GB, the Page file usage goes up to 7GB and everything runs really slow.
I dont quite understand the link, but on my C:, it has around 4GB free and that is the only drive where the page file is, I am wondering, is there any link between Max server memory, Page file on drives and also performance.
Also, the C: is the only drive with virtual memory specified.
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply