March 11, 2011 at 10:51 am
Hi
I need some help on the best setup with the following:
We have a Microsoft Windows Server 2003 Enterprise Edition Service Pack 2 (18GB Ram) running 1 instance of Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
With the above setup being 32bit my understanding is that SQL can only use up to 4GB of Ram?
To enable the whole 18GB to be considered I need to use AWE?
I know I need to add the account that SQL server uses to the Lock Pages in Memory under gpedit but I’m not sure on what I need to set the AWE Min and Max to?
Would it be 50% of the available Ram so set both Min and Max to 9216MB? and leave 9GB for the OS? Or another combination?
Thanks in advance
March 14, 2011 at 4:37 pm
I can't help but we have similar set ups. I also have 2005 Standard Edition with SP2 on windows 2003 Enterprise with SP2 on 32bit. We have 8GB memory and 2 quad core zeons @ 2.33GHz
I am having a terrible over performance issue across the whole server. Navigating directories, querying SQL, everything is just slow. The page file grows up to 7.8 GB over the course of three days after reboot yet the CPU is barely taxed. There is plenty of disk space.
Is it possible I'm only utilizing 4GB of memory? I was not aware of this limitation on 32bit. I have no idea how to properly adjust the affinity masks at the OS and SQL levels and if I should use AWE feature. I hope you figure out your issue as it sounds like I might also benefit! Any advice much welcome!
March 14, 2011 at 7:22 pm
Bristolred, you are correct that x86 is limited to only 4 GB of addressable memory, of which 2 GB is reserved for the OS by default (this can be changed to 3 GB, but that's irrelevant if you have more than 4 GB in the box).
If you look in the process list of task manager, you'll probably see SQL Server using 1.7 GB of memory. You can get this information from within SQL Server by running the following query:
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')
Assuming that SQL Server is the only thing running on this system, I wouldn't necessarily bother with an Min Server Memory value. I'd recommend a Max Server Memory of 15360 (15 GB), allowing 3 GB available to the operating system. You can then monitor the perfmon counters for available physical memory, and this will let you know if you can increase this to 16 GB (although I wouldn't go past 16 GB).
9 GB is very conservative, assuming no other software runs on this machine (virus scanners, SSIS, etc). Again, feel free to set it at 9, and then monitor to see how much physical memory you're using, and increase if you can.
___________________________________________________________
jligda, it's probably worthwhile to create a new thread for your issue, just to keep things tidy - performance can be an in-depth problem. To get you started, I recommend figuring out exactly what the bottleneck is. Use the query above to determine what SQL Server's memory usage is. Note that due to a quirk in Task Manager, if you're looking at "PF Usage", this includes physical memory, so a PF Usage of 7.8 GB could simply indicate that you're still under physical memory. I recommend looking at the "Total Commit Change" and the "Peak Commit Charge" to see if you've ever gone over the "Total Physical Memory" value.
March 15, 2011 at 8:29 am
Bristolred (3/11/2011)
HiI need some help on the best setup with the following:
We have a Microsoft Windows Server 2003 Enterprise Edition Service Pack 2 (18GB Ram) running 1 instance of Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
With the above setup being 32bit my understanding is that SQL can only use up to 4GB of Ram?
To enable the whole 18GB to be considered I need to use AWE?
I know I need to add the account that SQL server uses to the Lock Pages in Memory under gpedit but I’m not sure on what I need to set the AWE Min and Max to?
Would it be 50% of the available Ram so set both Min and Max to 9216MB? and leave 9GB for the OS? Or another combination?
Thanks in advance
1) You REALLY need to get a professional on board for a few days to help you configure this system and to review your entire SQL Server infrastructure. You are asking questions that indicate there will likely be many things done incorrectly or suboptimally across the board.
2) /PAE in the boot.ini file allows for the use of >2GB of memory by 32 bit SQL Server. AWE sql config setting enables this.
3) beware lock pages in memory. you can starve the OS, leading to very poor performance an potentially an unstable server
4) I would set sql sever max memory to 14GB on this server and then monitor for excessive paging
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 18, 2011 at 9:16 am
Thanks for your help on this issue guys.
I now have AWE set and the system performance is now much better:-)
My next question is this. I've set AWE Max Setting to 15GB (15360KB) which has left 3GB for the OS.
In Task Manager there's an average of about 1.7 GB still available which means the OS is using 1.3GB and 1.7GB left for the OS seems fine.
If I run this:
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')
I get this:
SQLServerMemory Manager Target Server Memory (KB) 15728640
SQLServerMemory Manager Total Server Memory (KB) 15728640
Does this mean that now SQL has been allocated 15GB of RAM that it is actually using all of it? I know it can see it and I know it's there if it needs it but is it actaully using it all?
How can I tell?
Perfmon SQL Memory Manager just gives me the figure 15728640 for all LAST, AVG, MIN and MAX.
I mean in the sense do I need to install anymore RAM in the server? and up the AWE Max setting in line?
Thanks again in advance.
March 22, 2011 at 5:30 am
Anyone?
March 23, 2011 at 6:47 am
There are other DMVs you can use to see what is actually using buffer pool memory. You an also check out dbcc memorystatus.
In general though, unless you have less total data on the server than RAM (or never run any queries), all of your buffer pool will be used to hold data pages.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply