August 18, 2005 at 10:21 am
Hi everyone,
I'm sure somebody has seen this and it is more than likely an oversight on my part. I have a database server with 4GB of memory running Win2k standard and SQL Server 2k standard. SQL server is only using 1.61GB of the available 2GB. The buffer cache hit ratio is continuously dropping below 90%. Can anyone tell me what I can do to get SQL Server to use the remaining memory. I really need to upgrade the O/S so that I can use the 3GB switch but unfortunately I cannot do that right now. Any ideas on this would be greatly appreciated.
Thanks
Jason
Regards,
Jason P. Burnett
Senior DBA
August 18, 2005 at 2:35 pm
That is because you are runing the standard edition.
August 19, 2005 at 6:48 am
August 19, 2005 at 8:30 am
This is a dedicated server. There are no other applications running. The memory settings are set for dynamic control with no maximum limitation. I really appreciate your input. Do you have any ideas given the new information?
Regards,
Jason P. Burnett
Senior DBA
August 19, 2005 at 10:18 am
The 1.61 Gb seems somewhat normal even though it can use 2 Gb. My non /3Gb servers use 1.8 Gb with min/max server memory set to 2048 (2Gb.. Even with Enterprise Edition and /3Gb I've only got SQL to use between 2.7 and 2.8 Gb even when setting min/max server memory to 3072 (3 Gb). Maybe we can get a SQL MVP to get us the inside scoop on 'the why'.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 19, 2005 at 10:59 am
It must be used to manage some kind of overhead. Thanks for you input.
Regards,
Jason P. Burnett
Senior DBA
August 22, 2005 at 3:30 am
August 26, 2005 at 12:07 am
What are your settings for min and max memory?
You could try to set you Min as close to 2.0 gb and your max at 2.0 gb and make sure the "set working set size to " is 1. This should force SQL to capture your min settings.
Furthermore, you may want to look if there are no exceptions registered in the errorlog file at server startup.
crasc
August 26, 2005 at 1:08 am
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316749 explains that SQL will use all but 384MB of it's memory between 2 and 3GB. I don't know why it isn't mentioedn elsewhere
Less than 2GB it is dynamic. More than 3GB is not accessed unless via AWE and PAE etc
If you force SQL to use the full 2GB of RAM, then I reckon that you will leave NO user mode memory space left any other applications. This would include lots of windows apps not just 3rd party installed ones.
Despite each app having a full 32bit virtual address space, I'm sure that all apps are mapped to actual memory withing the 2GB user mode address space. The other 2GB is usd by the OS and kernel mode code.
The set working set size option and setting min and max memory will effectively grab all memory and no other apps will run. Such as your backup software (I assume you do such a thing), your server monitoring agents (eg HP or Dell agents), MOM agents, SAN software and so on
If you had Windows 2000 advanced server, you could use /3Gb to allow more user mode address space and then force SQL to use the full 2GB.
Personally, I wouldn't play with any memory or working set size options.
I'd upgrade the OS and let Windows and SQL work as advertised.
Then again, are you getting any performance issues? SQL may only use 1.6 GB of RAM (rather than 1.7 or 1.8) because that is all it needs, say your DB is only 1.6 GB and is now all in memory, or only 1.6GB of data of a 15 GB DB is used most of the time.
But hey, I'm not responsible for the server...
August 26, 2005 at 4:25 am
Yes, there are performance issues. I think the real issue is a high avg physical queue length (average of about 10 per disk). They are working on that. But also, the buffer cache hit ratio often drops below 90%.
The specs are:
6GB internal memory
about 75GB of databases
Total server memory sticks at 1702000.
I think SQLServer could use some more memory. The only "other" software running on that machine is IIS (reporting services)
But Hey, i'm not responsible for the server either... A friend of mine is. But it makes me curious. Why does SQLServer not getting more memory, or am i looking at the wrong numbers?
August 26, 2005 at 4:59 am
IIS en SQL SERVER on the same machine?
Is is possible to put IIS on one machine and SQL Server on another?
August 26, 2005 at 5:06 am
If you set max and min memory and the working set size, you will probably make the disk issues worse by causing paging (for agents etc as well as IIS and RS)
Even if used SQL Enterprise (which has enhance IO algorithms for read ahead) etc, you will be limited by the OS for memory.
Your buffer cache hit ratio means SQL is going to disk for data and it's not in the cache.
SQL will not take more memory because if leaves 384MB alone for other uses (see the KB). The reason it is 1.6 GB not nearer 1.8 GB is because of IIS and RS using resources (which means it is not a dedicated SQL box...)
You cannot upgrade the OS. It is pointless upgrading to SQL enterprise until you do.
If you change SQL memory settings, you will cause more issues.
Your options are limited:
1. You could shift your RS and IIS onto another box, but I guess you have the standard edition of RS which means that it is difficult to split you RS off later from your SQL (I have tried, no joy). If you have RS entreprise then you need an SQL enterprise license, but again, why when you have basic OS).
2. Shut down unnecessary apps and services. And any antivirus software which can cause issues with IO.
3. Upgrade you IO subsystem. RAID 1+0. 15000 rpm disk. Bigger whizzier RAID card. Mutiple arrays for log, data, tempdb etc.
These are just minor issues though, compared to having a basic OS.
August 26, 2005 at 5:50 am
I'm confused. Is the 384MB not part of the "Total Server Memory" counter?
I could't resolve this from the KB.
But if it is not a part of Total Server Memory then it would mean that Total Server Memory could go as high (on Standard Edition) as 2048-384=1664MB=1703936KB.
August 26, 2005 at 6:16 am
"Total server memory" (if you read the explanation in perfmon) is how much memory SQL server is using. Not how much the server has fitted. It does not include sqlservr.exe and dlls (another 20MB or so)
So, when you look in task manager and look at sqlservr.exe it is higher, by 20MB or so, then "total server memory".
Now 2048 - 384 = 1664MB. Which is 1.625GB. He says he was using 1.61GB, or 0.15GB less than your optimum, call it 20MB.
There are other processes running on this box that also require memory from the 2GB user mode address space, thus it will probably never reach this "optimum".
From my own experience (having lots of dedicated SQL boxes with 2 and 3 GB of RAM and no IIS etc), after a few days each sqlservr.exe process will be using around the same amount of memory (max mem less 384MB) confirming the KB article.
In any case, I say "around" and "or so" because there is always a margin of error in any observation, every server is different etc. So whether it 1.625GB or 1.61Gb of data is fairly insignificant, this is a margin of error of under 1%.
Going back to the problem in hand, there is a simple problem of a basic OS and standard edition and not a dedicated box. Any other solution is just patching not fixing.
Of course, the IO issues could be because the database is physically and logically fragmented... but that's another issue...
August 26, 2005 at 8:29 am
I want to thank everyone who responded. The microsoft article referenced in one of today's posts was exactly what I was looking for. It is plain to see that my only option is to upgrade my O/S. So I will be going with Windows 2003 Server Enterprised Edition as soon as possible and enabling the /3GB switch.
Thanks Again
Jason
Regards,
Jason P. Burnett
Senior DBA
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply