September 21, 2010 at 6:05 am
Cath Trimble (9/21/2010)
@sqlbuddy123SQL Server 32 bit can't use more than 4GB of memory.
Here's someone who's proved SQL Standard 32bit can use more than 4GB on a 32bit OS:
http://sql-server-performance.com/Community/forums/t/2322.aspx
8th post down. Mistake in BOL apparently.
Thank you for posting that, Cath. I was about to say something about my post and "why wasn't anyone listening"... @=)
September 21, 2010 at 6:14 am
Wow...
that blog is exactly the answer of my question. :w00t:
Thank you very much for your help....
Regards,
September 21, 2010 at 8:08 am
george sibbald (9/21/2010)
@brandiesorry, was not ignoring you, just taking a different viewpoint.
Different viewpoints are good. Even moreso when the documentation proves wrong. @=)
September 21, 2010 at 4:53 pm
Hi Guys
Thanks for the shaking the myth.
Sorry for the post. I was under a misconception.
Found some good articles supporting this..
http://www.teratrax.com/articles/sql_server_64_bit.html
http://www.modhul.com/2007/11/10/optimising-system-memory-for-sql-server-part-i/
http://www.sql-server-performance.com/tips/awe_memory_p1.aspx
http://www.jimmcleod.net/blog/index.php/2008/06/03/sql-server-2005-and-awe-memory/
Thank You,
Best regards,
SQLBuddy
September 22, 2010 at 1:49 am
@mrakki
please still post back your results and final configuration, will be useful info
Thank you guys, for the great articles and the information you provided. 🙂
As soon, as I get the first results I will post them here.
Thank you.
Regards,
MrAkki
December 7, 2010 at 9:11 am
Hello everyone,
I am in a same kind of situation. I have 32 bit SQL server 2005 standard edition on a Windows Server 2008 64 machine with 16 GB RAM.
I am still not able to utilize more than 3.5 GB memory(RAM) on this server. I had turned on AWE settings to use upto 10GB memory but it restricted the SQLServer.exe to use only 1.5 GB memory only. Then I read that AWE doesn't work on a 64 OS.
Not sure what do, can anyone help. I have read all the posts but pretty confused after reading them and not able to get to a conclusion.
Please help. I can perform any test and post the results real here quick as I have a server which is a replica of the production server.
Thanks,
Vikas
December 7, 2010 at 9:30 am
check for any AWE related Messages at the front of your errorlog. It should work. Check memory SQL is actually using via sysmon, not the task manager.
Follow the instructions for a 32 bit system.
Perhaps MrAkki can post back on his setup.
---------------------------------------------------------------------
February 8, 2012 at 4:05 pm
MrAkki (9/21/2010)
SQL Server 2005 32bit can be installed on 64bit platforms (WOW - Windows on Windows), although I can't think of any good reason to do so
We found a reason to do so: after trying to get a 64bit install of SQL Server to access 32bit third party ODBC drivers via Linked Server, apparently 64bit SQL won't access 32bit ODBC and vice versa. I found bloggers reporting reghacks that worked for them, but it didn't work for us. So, we're stuck with 32bit SQL until the third party develops 64bit ODBC drivers.
Anyway, the question then becomes memory access. I came upon this thread, which didn't seem to clarify our situation and then my boss let me use a new server we bought as a test before putting it into its intended production. I was able to successfully get 32bit SQL 2k5 Std on 64bit Win 2k3 R2 to address more than 4gb ram (Server has 12gb RAM). These are the exact steps I performed.
Clean format and install of Win2k3 R2 64bit. OS Reports 11.9GB RAM.
Install Win2k3 R2 SP2.
Install SQL Server Std 2k5 32bit.
Install SQL SP3.
Can't remember where I got this memory query, but:
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')
Target Server Memory is what SQL Server can use, Total Server Memory is what SQL Server is currently using. The column indicating memory is "cntr_value", which showed this:
--BEFORE AWE:
--Target Server Memory (KB), 3473408
--Total Server Memory (KB), 22016
I added the sql service account to "lock pages in memory" policy, restarted SQL Server service, then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
Restarted SQL Server service again and ran the same memory query:
--AFTER AWE:
--Target Server Memory (KB), 10420224
--Total Server Memory (KB), 19456
I hope this helps verify that it can be done!
February 13, 2012 at 3:48 am
My understanding of how AWE works means that AWE is not available on a 64-bit OS. Therefore the 32-bit SQL Server functionality that allows use of memory above the 4GB line by exploiting AWE is also not available when you run 32-bit SQL Server on a 64-bit OS. Therefore you cannot use more than 4GB memory on 32-bit SQL Server running on a 64-bit OS.
AWE is a kludge to get around the 4GB memory restriction in 32-bit operating systems.
There is no way in a 32-bit OS to directly address memory above the 4GB line. However, with friendly hardware the OS can segment memory above the 4GB line into 4KB pages. It can then get the hardware to copy the contents of a given 4KB page to a location below the 4GB line where it can be read and written, and copy the updated 4KB page back to its home above the 4GB line. This process of segmenting and copying memory is known within Windows as AWE. (OK, to be strictly true AWE in its final form covers more than just this function, but the memory mapping is what caused AWE to be developed.)
If you have an AWE-aware application (such as SQL Server), then it can make use of the AWE APIs to reserve some of the 4KB segments for its own use, and to get these copied in and out of addressable memory.
The need to do this memory mapping does not exist in a 64-bit OS, and the APIs are not available for applications to use. Therefore because 32-bit SQL Server has no memory map APIs to address memory above the 4GB line, it cannot use this memory and is restricted to using 4GB only.
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
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply