Memory

Question

Memory

Bruna

SSC Journeyman

Points: 87
More actions
January 17, 2007 at 5:18 am

#118508

Hi there,
We have a HP server:
2 processors dual core
8 GB memory
RAID 10
SQL Server 2003 R2 32/64 bits SP1
SQL Server 2005 Standard 64 bits SP1
This server have ony SQL Server run.
We have only one database in the server. Only two users using the system for few minutes, and SQL Server allocated almost 8GB of memory.
If we run Maintenance Plans for this database, the same thing happen.
We never seen something like that. But SQL Server 2005 and 64 bits is a new environment for us.
Give me a hint, please.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

If we run Maintenance Plans for this database, the same thing happen.

what exactly is happening. is SQL occupying your whole of 8GB RAM. if so check the memory settings thats configured. i think u have configured a fixed moenory for ur sql server.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Bruna SSC Journeyman Points: 87 More actions · Answer 2

Hi Sugesh,

Yes, is SQL occupying your whole of 8GB RAM!!

I didnt configure a fixed memory. It´s dinamyc.

The minimun is 0MB. Its default after install.

Steve Goodwin-362749 SSC Veteran Points: 272 More actions · Answer 3

As long as there more available memory on your server SQL will choose to consume the memory rather than push data out of memory. SQL will continue to consume memory until it exhausts all the available memory, or outside pressure (other apps) request memory. Since no other applications are running on your server, and you haven't throttle the max memory available to SQL, it is not unusual for SQL to use all the memory.

I am making some assumptions here, but I would guess that you haven't seen this behavior before because you where running in a 32 bit environement before and you likely had Win 2003 SE or 2000 SE. These versions are limited to 2GB of addressable application memory (unless you manually set the /3G switch to use 3GB for applications, leaving 1GB for OS).

You need Windows EE to address more than 4GB in a 32 bit environement. Even then you have to configure SQL to use the memory (AWE) and you may have to configure PAE on the server as well depending on whether or not you have installed hot swap memory. In the 32 bit environment (without getting overlry technical) the memory above 4GB is still not directly accessable due to architecture limitations. Its esentially is like a big in-memory swap file.

I assume you had AWE enabled in SQL, but did not have hot-swap memory in Win and didn't configure /3G or /PAE so you where limited in memory use.

In the 64 bit environment memory above 4GB is directly accessable by default, so all you need to do is enable AWE in SQL

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

with sql server 2005 the memory management is dynamic until u set a fixed memory configuration the internal processes in managing the memory has been improved too much in such a way that sql server compromises for other applications and operating systems also. Search for memory architecture in books online to know how memory is managed in sql server 2005.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

SQL 2005 x64 will keep grabbing memory as long as nothing else on the system is using it, even if there is very little work for it to do.

But it is very sensitive to memory pressure and should release memory fairly quickly when any other process needs it. At least that is the way it is supposed to work. It senses when the available physical memory falls below some threshhold and starts releasing things like old page buffers as soon as it can. This works when other processes allocate memory gradually, but if something suddenly wants to allocate a huge chunk of memory it will have to live with virtual memory from the swap file until SQL gets a chance to react. SQL may have to wait for a checkpoint to write pages to disk before it can release them.

If you think something on your server isn't working well because SQL has all the memory, you can set the "max server memory" option to restrain it. It's normal to see SQL grab almost all of the memory, I'd wait until I saw actual performance issues before I limited it.