SQL Server 2008 Memory Allocation

ADARTIHS

Hi Everyone,

I am having an Issue with SQL 2008 on a NAV Server.

Ths issue is 75% of the server (4GB 32 bit) memory is being used by SQL server even when NAV is not really being used. Also NAV is the only application usnig SQL.

Can you help

Please

ADARTIHS

bghubr

This is standard behavior for SQL. The server will reserve the maximum it can unless you configure the server differently.

kriki

After a fresh restart of the server, SQL takes (per default) less memory. When requests start arriving, SQL takes memory to keep the data in memory and starts using memory, but never releasing it. At least if SQL is not forced to release it.
You can control memory usage of SQL in SQL Server Management Studio => Properties of your SQL server => Tab memory => "Minimum server memory (KB)", "Maximum server memory (KB)". With these values you can put a minimum and maximum memory use of SQL server. The best for performance is a fixed size.
For a 32bit system, I generally leave 512MB for the system and the rest I give to SQL. But you need to check if SQL isn't memory swapping because that removes the advantage of using a lot of memory as cache for SQL. (You can also influence that by using "lock Pages in Memory" (http://www.sqlservercentral.com/blogs/sqldbauk/archive/2010/06/25/lock-pages-in-memory.aspx).

mgm

I have something likewise on my pc.
Running NAV2009R2 on SQL2008 with 8GB internal Memory.
When importing or compiling objects, especially tables with a lot of data, SQL takes more and more Memory. Also when running heavy jobs or scripts.
After finishing, it doesn't release the memory. Sometimes it uses so much memory that the whole machine is getting slow.
Is there a way to tell SQL not to use so much memory and release it after the job is finished?

kriki

I answered that in my previous post : the "Minimum server memory (KB)"+"Maximum server memory (KB)" settings.

stryk

Also have in mind, that 32bit systems allocate memory different than 64bit:

4GB is the max. memory space which could be directly adressed by 32bit bus system. Period.
Windows is "splitting" this 50:50 - means 2GB for the OS, and 2GB for applications, as SQL Server. This leaves a net RAM for SQL Server of 1.6GB.
When enabling the /3GB switch in boot.ini Windows changes this to 1GB for OS and max. 3GB for applications (net 2.6 GB).

To make a long story short: SQL Server with just 4GB sucks. If you put in more RAM you need to activate /PAE and AWE (search MS KB about details).

The "Max. Server Memory" - as Kriki already proposed - should be adjusted, e.g. like: physical RAM - 1 (or 2GB), for example with 8GB the MAx could be 7GB; with 16GB the Max could be 14GB.

mgm

I am running WIN7pro on 64bit 8GB i5 M520 (dual core?).
I gave SQLserver a max of 4096MB

Also when importing a fob, especially a table with lots of data in it, NAV takes some to 'redesigning'.
At the same time memory usage is going up steadily.

1. why is NAV making SQL use more and more memory?
2. is there no setting that releases the memory usage after the job is done? (should be, shouldn't it?)

kriki

stryk wrote:

Also have in mind, that 32bit systems allocate memory different than 64bit:

I always forget there is still some 32bit around in production.

1. why is NAV making SQL use more and more memory?
It is not NAV that makes SQL use more memory. It is the decision of SQL to use more memory.

2. is there no setting that releases the memory usage after the job is done? (should be, shouldn't it?)
There isn't. SQL doesn't like to give back memory to Windows.
The only way to limit memory use of SQL is the "Maximum server memory (KB)"-setting in the properties of SQL server.

DenSter

You don't have to reboot your laptop to get SQL Server to release the memory, all it takes is restart SQL Server itself.

I have my laptop's SQL Server memory set with 0 minimum and 4GB maximum memory. Every once in a while when lots is going on my laptop gets into RAM trouble, and often I get it back by restarting my SQL Server.

mgm

I already have a bat file on my desktop with the following code:

net stop "SQL Server Agent (MSSQLSERVER)"
net stop "SQL Server (MSSQLSERVER)"

:TIMEOUT /T 10

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

Guess I have to live with it.
Just wondering if this is an issue on Production servers. Do they have to be restarted as well after heavy jobs and importing lots of objects?

DenSter

The reason why you are feeling the pain is because you want to do all these other things with your computer, and SQL Server is competing with those tasks for system resources. Production servers are usually built to perform one thing: run SQL Server. It's alright if all of their memory is consumed by it, because that's what it is supposed to do. Of course you need to set it up so that it will leave enough memory for OS tasks, but on a properly configured production system that is not an issue.

kriki

Or do as I do: I have a VM on my machine that ONLY does SQL server.
BTW:I do use "Maximum server memory (KB)" on it and I don't have problems!