January 9, 2009 at 6:20 pm
Everybody,
This question seems to be in a grey area.
We are running Windows 2003 Server R2 x64 Enterprise Edition and SQL Server 2000 Enterprise Edition with SP4.
I can see the entire amount of memory in the slider, but I do not know if SQL can use all of it.
Virtually every other post I see about this references Windows 2000 Server or Windows 2003 Server 32-bit versions. We are running the 64-bit version of Windows 2003 Server R2 - Enterprise Edition.
I suspect since SQL 2000 seems to be able to see the memory in the Enterprise Manager GUI, it will be able to use it. But my recent experience is with SQL 2005 x64 and it was not an issue there.
When I check the process using Task Manager it shows memory usage of right around 4GB. I set the memory allocation at 30579 for the min and max but have done none of the other tricks (AWE, PAE, etc.)
This server is not yet in production, but that day is coming very soon. I would like to know if SQL will see the entire amount of RAM or if I need to use special switches.
I need a simple answer, please do not reply with the same URLs that I have been visiting all day that do not address my concerns.
Thanks!
January 10, 2009 at 1:28 am
If you do not set Min and Max memory within your SQL Instance configuration, it will use as much memory as it needs. This includes RAM and page. If you set Min and Max, it will only use an amount of memory within that range.
This is the same regardless of architecture - x86 or x64.
If you enable Lock Pages in Memory for the service account running the SQL Database Engine, then SQL Server can manage its own RAM and won't be paged out because Windows forces it based on its own memory management.
SQL Server, like any application, can only use memory that is available / is allocated to it. Always keep in mind that Windows has a memory overhead as do any other applications. One server I recently built as 64GB of RAM and is running Windows Server 2003 x64 Enterprise SP2 with SQL Server 2005 x64 Enterprise Edition. We have allocated 48GB to it because the rest of the RAM is used by the OS, services and Distributed Transaction Coordinator.
So, to answer your question simply. SQL Server will use all the RAM available as it requires it. But it will never use ALL of your RAM as the OS always has an overhead and it won't get paged out.
--
Andrew Hatfield
January 10, 2009 at 2:18 am
Andrew,
Thanks for the reply, but it is rather stark contrast to many articles I have seen discussing SQL Server 2000 and 32-bit services/applications.
I set the memory amount very high and have yet to see the server allocate anything over 4GB to SQL Server.
I can appreciate the SQL 2005 behavior, having seen it up close. Nothing I find really indicates that SQL 2000 EE and Windows 2003 Server R2 x64 EE will behave.
Our paid consultants tell us we need to do the AWE SQL flag, apply a hotfix and then set the memory at a fixed level.
I question if this is required for our 64-bit platform. Since the slider lets me see all 32GB of RAM I suspect it is not. I just want the high-water memory mark to exceed 4GB before I feel warm and fuzzy about it.
I am hoping to get an answer and some form of Microsoft reference article to support the claim. It would not be good if we put this server into production only to discover it cannot use more then 4GB of RAM.
Thanks
January 10, 2009 at 4:57 am
Hi Doug,
Sorry, I missed the bit about SQL 2000. Sort of important in your case. I've much more experience with SQL 2005 so take my advice with a grain (or bag) or salt.
AWE is not required, not enabled, on a 64 bit platform. On a 32 bit platform, AWE enables an application to address memory greater than 2GB. This is not required in a 64 bit system as a single process can address more than 2GB. This is only a restriction on 32 bit platforms.
As for your SQL Server not using more than 4GB of RAM, has SQL required more than 4GB of RAM? Can you run a stress test against it, similar to SQLIO?
--
Andrew Hatfield
January 10, 2009 at 3:23 pm
You'll need AWE enabled within SQL, no switches are required for the OS. While Windows can address all the memory, being 64 bit, SQL 2000 being only a 32bit process cannot see it, thus the need for AWE.
Additionally you should install the patch http://support.microsoft.com/default.aspx?kbid=899761
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply