April 23, 2009 at 2:10 am
Dear all,
I have some issue and I’ll be happy to hear some ideas.
1.I have 12GB RAM in my windows 2003 SBS server (Dell power edge 2900)
2.My SQL server is SQL server 2005 Enterprise.
3.I am using large DBs
4.In my Boot.ini file I put the /PAE switch.
When I leave the AWE options (in SQL server) unchecked I see in the task manager that “sqlserver” process allocate 1.7GB (never more than that-even if I set maximum memory 8GB in SQL properties)
Now,
when I checked the AWE options I see in the task manager that “sqlserver” process allocate ONLY 70MB RAM!!!
Any idea?
What is the recommend setting to set in Boot.ini and in SQL server (AWE) for 12GB RAM?
Sincerely
April 23, 2009 at 5:39 am
Dear all,
I have some issue and I’ll be happy to hear some ideas.
1. I have 12GB RAM in my windows 2003 SBS server (Dell power edge 2900)
2. My SQL server is SQL server 2005 Enterprise.
3. I am using large DBs
4. In my Boot.ini file I put the /PAE switch.
When I leave the AWE options (in SQL server) unchecked I see in the task manager that “sqlserver” process allocate 1.7GB (never more than that-even if I set maximum memory 8GB in SQL properties)
Now,
when I checked the AWE options I see in the task manager that “sqlserver” process allocate ONLY 70MB RAM!!!
Any idea?
What is the recommend setting to set in Boot.ini and in SQL server (AWE) for 12GB RAM?
Hi,
Check the following.This ll help for u.
AWE only supported OS-32-bit not for 64-bit.
1.SP_CONFIGURE 'SHOW ADVANCED', 1
RECONFIGURE
SP_CONFIGURE 'AWE ENABLED', 1
RECONFIGURE
EXEC SP_CONFIGURE N'MIN SERVER MEMORY (MB)', N'min'
RECONFIGURE WITH OVERRIDE
EXEC SP_CONFIGURE N'MAX SERVER MEMORY (MB)', N'max'
RECONFIGURE WITH OVERRIDE
SP_CONFIGURE 'SHOW ADVANCED', 0
RECONFIGURE
2.Before enable AWE u must add the sql server account permission Lock pages in memory.
(Its only for Enterprise edition )
3.If ur memory has configured hot-add memory u no need to setup /PAE in boot.ini
4.Once done the 3-steps U must restart the sql-Box.
Also verify the following
1.Check the sqlerrorlog
sp_readerrorlog
Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
2.Check the memory
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')
Edit:To remove the deprecated object.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
April 25, 2009 at 5:58 pm
Hi Muthu,
Many thanks for your help.
I did what you advise me, and its work. however the "Target Server Memory" and "Total Server Memory" never going up more then 3.6GB even if it set to 5GB
any Idea?
thanks
April 26, 2009 at 11:55 pm
Hi Muthu,
Many thanks for your help.
I did what you advise me, and its work. however the "Target Server Memory" and "Total Server Memory" never going up more then 3.6GB even if it set to 5GB
any Idea?
thanks
Hi,
Its depends upon urs App.How big ur Database?
Also Check the following Counters in perfmon.msc and replay.
1.Memory: Pages/sec
Average between 0 and 20
2.Memory: Available Bytes >
Less than 20 to 25 percent of installed RAM is an indication of insufficient memory
3.SQL Server: Buffer Manager: Buffer cache hit ratio >90
4.SQL Server: Buffer Manager: Page Life Expectancy >300
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
April 27, 2009 at 3:01 am
Hey Benn,
Windows 2003 Small Business Edition can use up to a maximum of 4GB RAM - see http://msdn.microsoft.com/en-us/library/aa366778.aspx#physical_memory_limits_windows_server_2003
SQL Server Enterprise with 12GB RAM would need Windows 2003 Enterprise or above.
If you were running a 64 bit edition of SQL Server, any 64 bit OS would do.
So, it is the OS that is limiting the memory usage - not SQL Server.
You should have a stern chat with your infrastructure people for installing SQL Enterprise on a 12GB RAM box under a 32-bit OS limited to 4GB!
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 27, 2009 at 4:46 am
Hi all,
Many thanks for your effort to help!!!
Yes you right, the problem is that my Windows SBS cant see more then 4GB RAM, I reinstall windows 2003 enterprise X32 with SQL 2005 and I am using AWE.
Its work fine now.
again thanks a lots
Ben
April 28, 2009 at 3:13 am
It is not just Windows SBS that cannot see more than 4 GB RAM. All 32-bit editions of Windows are not able to address more than 4 GB RAM, so you will never see anything using more than this in Task Manager.
One of the functions of PAE allows Windows to store data in memory above the 4GB line, but it has to be copied back below the 4GB line for an application to read or update it. Applications that are aware of PAE facilities can use the memory above the 4GB line as a cache.
SQL Server has the AWE option which allows it to take advantage of PAE. SQL only ever stores database bufferpool data above the 4GB line. If you want to see what use SQL Server is making of AWE memory then you have to look at the relevant Perfmon counters - BOL has more details. You will never see how AWE memory is being used in Task Manager.
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
April 28, 2009 at 4:13 am
If you have 3.5GB or more in your server (or you would like it) you need a very good reason not to move to a 64-bit platform.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply