Memory limit < Memory used?

Question

Memory limit < Memory used?

Rin Sitah

SSCrazy

Points: 2180
More actions
May 6, 2011 at 5:38 am

#239109

Hi there,
Im just wondering if anyone can shed any light on this:
We have a sql 2008 x64 on win server 2008 r2. We've limited maximum memory for the SQL server to 102400 MB (100GB) via the Server Properties in Mgmt Studio. However, in resource monitor, its saying that the sqlsevr.exe process is using 108,873,584KB of memory (~103GB)
My question is, why is it taking up 3GB more memory than the maximum limit that we have set it to?
Many thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 1

Did you restrict the SQL Server or not with Max Memory parameter if not then it can use more memory , 100 GB RAM is too high, what is the purpose of this database server which one is using 100 GB RAM ?

Check this also SQL Server required 100 GB Ram or not

My Blog
www.aureus-salah.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

How can you say a server is not needing 100 GB ram?

I've heard about multi TB dbs. 100GB for that is not overkill.

ricer Right there with Babe Points: 722 More actions · Answer 3

That doesn't make sense.

100 GB is not too high especially depending on load and size of the database.

Just because it might be too much for the servers in your company does not mean it's true for all situations.

-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

The max memory setting only controls the max size of the buffer pool, the paged memory allocation.

SQL also used non-paged memory for things like thread stacks, backup buffers, CLR, in-process linked server drivers and a few other things. Since this is non-paged (not in 8kb chunks) it can't come from the buffer pool. It's memory allocated in addition to the buffer pool, ie can be above and beyond the max memory limit.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Gail is Right. That's the reason why you are seeing more memory consumption than Max Mem.

Thank You,

Best Regards,

SQLBuddy

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

Thats why I was asking about database purpose or load on it, check it my second statement with question mark if you guys can see

My Blog
www.aureus-salah.com

Rin Sitah SSCrazy Points: 2180 More actions · Answer 7

GilaMonster (5/6/2011)
The max memory setting only controls the max size of the buffer pool, the paged memory allocation.
SQL also used non-paged memory for things like thread stacks, backup buffers, CLR, in-process linked server drivers and a few other things. Since this is non-paged (not in 8kb chunks) it can't come from the buffer pool. It's memory allocated in addition to the buffer pool, ie can be above and beyond the max memory limit.

Thanks Gila,

Does this memory get released when its not being used any more?

I've been doing some traces on the memory consumption over the weekend, and it seems to be creeping- from 103.86 GB on friday to 104 today.

Will this continue to grow until it causes problems with the OS?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

Rin Sitah (5/9/2011)
Does this memory get released when its not being used any more?

Only if the OS requests that SQL release memory AFAIR.

Will this continue to grow until it causes problems with the OS?

It shouldn't. The non-paged are should be relatively small, unless you have lots of memory-hungry CLR or in-process linked server drivers. If the OS comes under memory pressure, it will ask SQL to release memory and SQL should do so.

Monitor it, if you do see signs of memory pressure (low available MB being one of the signs), you can reduce the max memory setting for SQL slightly (not much), that won't directly affect the non-paged area, but by reducing the buffer pool you reduce the overall memory usage.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass