SQL Server uses more RAM than allowed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Server uses more RAM than allowed

Author

Topic

irxn
Starting Member

13 Posts

Posted - 2011-11-14 : 02:46:18

Hi,

I have configured 30 000 MB RAM in the SQL server properties.
But the SQL server uses 31,5 GB RAM.

How is this possible?

Best regards
Peter

Sachin.Nand

2937 Posts

Posted - 2011-11-14 : 02:47:14

Are there any CLR or linked servers used ?

PBUH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-14 : 03:36:18

The maximum RAM setting covers only the cache and pool, I believe. I am not 100% sure.
This is fixed in SQL 2012 where the max RAM setting covers everything.

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2011-11-14 : 03:53:04

quote:
Originally posted by SwePeso

The maximum RAM setting covers only the cache and pool, I believe. I am not 100% sure.
This is fixed in SQL 2012 where the max RAM setting covers everything.

N 56°04'39.26"
E 12°55'05.63"

yes it does..

Any process like CLR or linked servers will use memory outside the Buffer pool which is sometimes called MemoryToLeave.

PBUH

irxn
Starting Member

13 Posts

Posted - 2011-11-14 : 06:18:34

Hi,

thanks for your answers. Yes, CLR is used.
Now it's clear for me, why the server uses more than 30 GB.

Best regards,
Peter

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-14 : 06:32:03

quote:
Originally posted by Sachin.Nand

Any process like CLR or linked servers will use memory outside the Buffer pool which is sometimes called MemoryToLeave.

It was called MemToLeave on 32 bit (was the memory under 2GB that was left out when allocating the buffer pool).
On 64 bit it's just non-buffer memory or non-paged memory.

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2011-11-14 : 06:47:55

quote:
Originally posted by GilaMonster

quote:
Originally posted by Sachin.Nand

Any process like CLR or linked servers will use memory outside the Buffer pool which is sometimes called MemoryToLeave.

It was called MemToLeave on 32 bit (was the memory under 2GB that was left out when allocating the buffer pool).

--
Gail Shaw
SQL Server MVP

So is it true for both /3GB and AWE for 32 bit ?

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-14 : 07:43:57

It's true for 32-bit SQL Server. The memory area is known as MemToLeave only on 32-bit (regardless of what flags it's using). It is not called MemToLeave on 64-bit.

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2011-11-14 : 11:07:55

I agree that MemoryToLeave is specific only to 32 bit SQL server but I dont think Memorytoleave is regardless of the flag's set.

With /3GB switch the whole buffer pool is mapped to SQL Servers VAS so is there a Memorytoleave with /3GB switch enabled ?

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-14 : 11:46:46

Yes, and it's exactly the same as without that flag. It's the portion of the Virtual Address Space (2GB or 3GB) that's left aside when allocating the buffer pool.

/3GB just adjusts the VAS breakdown between user and kernel. It's 2GB each by default, with that flag it's 3GB to user, 1GB to kernel. That's all that flag does.

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2011-11-14 : 11:56:43

Ok so if it's 3 GB to user with /3GB switch which I believe will be totally allocated to the buffer pool so how much will be set for Memorytoleave ?

Just want to make my concepts more clear.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-14 : 12:28:00

You believe wrong. Please go and do some reading, there's lots of info available, since you don't seem to believe me.

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2011-11-14 : 12:44:31

I don't think I said anywhere that I don't believe you.

So if I believe wrong the way you say then it means that with a /3GB switch there is never a memorytoleave area allocation ?

PBUH

Sachin.Nand

2937 Posts

Posted - 2011-11-14 : 13:09:10

Ok here is what I have understood after doing a bit of further reading.

With /3Gb switch the user address space increases to 3GB - Memorytoleavearea.So now in a non AWE mode but with /3Gb switch enabled when SQL Server starts it leaves 256MB of of it's VAS for external processes and reserves space for 256 threads of 512KB each and rest it allocates to the buffer pool.So now any single page allocation of 8KB will be done from buffer pool and anything larger than 8KB must be allocated from the 256MB area i.e Memorytoleave area

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-14 : 13:48:45

Yup.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources