June 20, 2006 at 6:16 am
I am running SQL 2000 ent on an 8 way box with 16gb RAM. I have specified /3GB and /PAE in the boot.ini file, the OS is W2K3 Ent. When i check the memory usage i find SQL is using about 2.4gb Ram and no more. I ahev check the error messages at the SQL Server Agent Level and it says that it is using 8 processors and 2.4gb Ram.
How do i get SQL to use more memory or will it only use what it wants up to 2.4gb... Do i need to dynamically config memory or leave it as auto config.
Any thoughts !!! Thanks..
June 20, 2006 at 6:50 am
you need to enable awe
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 21, 2006 at 4:39 am
He's using AWE by specifying the /PAE switch in the boot.ini file, isn't he?
David - what errors are you getting, exactly? Why do you think SQLServer should be using more than 2.4 Gb of RAM? It will only take as much as it needs, so if this is adequate it won't take any more.
June 21, 2006 at 5:02 am
you can change as many switches as you like in boot.ini but you still have to enable awe in sql server, and you should also set the min and max memory to be indentical to the amount of memory you need.
e.g.
exec dbo.sp_configure 'min server memory',3750
exec dbo.sp_configure 'max server memory',3750
exec dbo.sp_configure 'awe enabled',1
reconfigure with override
go
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 21, 2006 at 6:19 am
1) Windows Task Manager does not properly report usage of memory above the 4GB line. Your AWE memory usage is not shown in Task Manager. You can track AWE usage by the SQL Server performance counters.
2) I have had a recent discussion with Microsoft Technical Support about setting a fixed amount of memory for SQL Server. They strongly advised never to do that, and always set different values for min and max memory, even when using AWE.
The reason they gave was if a fixed amount of memory is specified, SQL will not release any memory regardless of any pressure on memory use. By setting separate min and max values, SQL is able to release memory below the 4GB line if memory is under pressure.
Their explanation seems reasonable, but the advice is different to what I have previously heard for when AWE is used.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 21, 2006 at 6:42 am
It was explained to me that some of the features of sql server relate to SBS type environments where sql server is not necessarily on a dedicated box. In a dedicated environment, which is usually where I work there would be no other apps to release memory to. In a mixed environment then some range would make sense, however from what I've read about AWE it indicates sql server does not release ( awe ) memory and will actually take almost all of the memory from the box, which is why you need to set min and max memory. Given the opportunity I allocate the reserve physical memory check box.
It gets tricky to be sure, which is why I'm hoping to go 64bit very soon, this should some of the problems!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 21, 2006 at 7:02 am
Apart from the anti-virus and intruder detection software our security people require, we run nothing else on the SQL Server box. ...except for backups, integrity checks, etc. ...and sometimes a desktop when someone has to log on to the server instead of using a client.
Even a 'dedecated' box has things other than SQL on it...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 21, 2006 at 12:26 pm
We were having some resource issues with our cluster and had a discussion a w/MS on this question using AWE & the /3GB switch. What we ended up doing per their advice was to Not use the /3GB switch, enable AWE and set max memory for each instance. (we are running 3 named instances on an active/passive cluster). So far so good! The main confusion seemed to be in the way you look at the /3GB switch; turning it on takes away tha 1GB from the kernel addressing and seems to hurt performance.
June 22, 2006 at 5:32 am
yup in certain situations the 3GB switch can be problematic, sysmaint.exe, dtsrun, xp_preparexmldoc ( or similar) run outside of sql server process so need external memory - the 3gb switch can then cause problems .. I've had to remove it on a couple of DW servers.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply