memory allocation on SQL server 2005
-
He originally mentioned that the server was being used for ETL also. The O/S memory over and above the 4 gig SQL uses can be used by the ETL tool, so moving to 8 gig without 64 bit is still possibly a good thing to do if 64 bit is out of the question.
-
Hi all,
Lots of confusion regarding awe as SQL SERVER 2000 and 2005 provide different features.Few important points from msdn books online
http://msdn2.microsoft.com/en-us/library/ms179301.aspx
For Windows Server 2003, the AWE mapped memory management is dynamic.
Support for AWE is available only in the SQL Server 2005 Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems. SQL Server 2005 Analysis Services (SSAS) cannot take advantage of AWE mapped memory. If the available physical memory is less than the user mode virtual address space, AWE cannot be enabled.
-------------------
If anyone is using AWE feature on windows 2003 enterprise manager(PAE enabled)+sql server 2005 standard edition, please let me know if it works without any problem.
thanks
Reena
-
Reena,
With 8GB memory, /PAE, /3GB and AWE all needs to be enabled. On a SQL 2K standard edition, i dont think you can make the sql use more than 2GB with /3GB, /PAE or AWE. its a limitation of the edition unable to access larger memory itself. have a read at the article below. although the OS is windows 2K, but the sql memory limitation still applies. Although i've enabled 3GB on a 4GB server, my sql standard edition was still only using 1.7GB max.
http://www.sql-server-performance.com/awe_memory.asp
back to the memory management of sql2k vs sql2005. what i found out is that in sql2k, if you fix the memory allocation, you can gradually see sql is taking up that amount of memory and it stays there. but in sql2005, it dynamically allocating itself memory even after you've fixed the sql memory. in a scenario where the server is having memory contention between OS and SQL, SQL2005 will release its memory back to the OS whereas sql2k wont. that's what it means by dynamic memory mgmt in sql2005.
When some say memory >4GB in 32-bit OS are never used, tell me i'm dreaming. i've got a 8GB memory, 4-dual core CPU configured with PAE, 3GB and AWE. When i fixed the memory usage of SQL at 6.5GB, i definitely can see my sql2k (via perfmon > SQLServer:MemoryManager > Total Server Memory (KB)) is allocating itself the physical memory until my fixed memory size 6.5GB. This is how i track all my sql servers memory usage. how's its using the memory whether its as efficient as 64-bit, probably not. but i'm sure sql2k would benefit from larger memory and its not "wasted".
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
i never said it doesn't use RAM over 4GB. it's just not used in everything SQL does. i'm playing with a test server now and doing selects for 8 million rows total with an order by in each case.
on a 3.5 million row query this is almost 10GB of data. on 32bit it would be sorted in tempdb and on 64bit it's all done in RAM since SQL 64 bit can access more than 4GB of RAM per process for everything.
on 32bit the RAM is used only for the buffer cache i think and on 64 bit you can do work in your entire memory footprint
-
totally agree, because that's what i've found out as well. there a limitation in 32-bit sql2005 cant assign >25% of data buffer for sorting, grouping, etc. i think you'll hit the tempdb when the data buffer is about 25% full. e.g. in a 4GB environment, assuming 3GB is turned on, then i would guess ~700mb (700/2700=~25%) will be dumped into memory for processing before tempdb is hit. awe doesnt change this behaviour.
same query in 64-bit, a query can be assigned 16GB. therefore, if your single query is producing more than this memory, you'll probably hit the limit and needed tempdb as well. but regardless, some smaller queries still do run on tempdb in 64-bit.
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
I understand most of this discussion. I have a 64 bit, x64, and sql server 2005 64 bit standard. Is there anything that I need to do, options/configurations that I need to set like AWE or the 3 gig switch that needed to be set on the 32 bit servers, for all of the memory to be available?
I'm happy to be working on a 64bit server, just need to get my feet under me.
🙂
Thanks,
Mark
Mark Johnson
MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP -
Mark - did you ever receive a reply? I have a 64 bit windows server running 64 bit enterprise SQL 2005, and my NT administrators came running to me wondering why SQL Server is using 6.5GB of the 8GB of memory in Task Manager. I look and nothing is running in SQL Server. Is this just a case of SQL Server grabbing all the memory it can so it can allocate out as needed, and nothing else requesting memory on the box, so SQL Server is just holding it??
Thanks -
Matt
-
No, nothing special ended up needing to happen. However, there was a bug that caused the standard 64 to overclock the memory sql server was using and then caused it to thrash trying to allocate and release memory at the same time. We were able to puchase additional memory and then set the high limit for sql server just below the amount of memory that the server had available. When it started thrashing no one was able to perform any work until it settled down and this was a very visible machine. There is a kb article about it. It was supposed to be fixed by sp2 but did not appear to be. Not sure if sp3 fixed it or not.
So we ended up controlling the memory that sql server used anyway.
Good luck
Mark
-
Sql does tend to hold onto memory until it senses some other resouce needs it. Some of the other folks might have a good explanation of how sql uses and releases memory as needed.
Mark
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply