Solved

Help explain SQL memory components

Posted on 2011-03-16
4
213 Views
Last Modified: 2012-05-11
I've heard it said "if you use sp_configure to set the max memory then SQL won't take more than that". but I've also heard it said that max_memory is really only for the buffer pool and that there are other pools of memory. I am trying to wrap my head around how SQL uses memory and I'm coming here so we can discuss instead of reading a blog with no feedback.

1. are there more memory pools than just that in max_Memory? like latch or others?
2. how do I see what those pools are using/taking/configured-to-use? via perfmon or t-sql?
3. what memory pool does the sqlservr process correspond to. it always seems to be a bit higher than what is set in max_memory
4. is it possible for sql in some shape or form to use more than max_memory (maybe their other pools)?
5. how do you change memory utilization of the other pools?

6. it seems like sql must be using more memory than max_memory because the total memory utilization in task manager does not add up to the difference between the total memory in the server and the Available MBytes perfmon counter. something must not be there in the form that task manager monitors.
0
Comment
Question by:MrVault
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 35151987
sp_configure max memory is how much memory in MB that SQL Server can request from the OS, it has a hard maximum and will not go above that number.

In that memory all the pools of memory are there.  You can see many of these with the System DMVs.

sys.dm_os_memory_* and sys.dm_os_sys_memory.

Remember that the number of bytes consumed must be divided by 1024 and divided again to get MB that would go in sp_configure.

SQL Server will never use more than is set in max memory in sp_configure.

All the memory allocation for the pools is set by the engine.


0
 

Author Comment

by:MrVault
ID: 35182774
can you point to articles explaining this? i've seen astatements in the past such as "you need more latch memory" or "the buffer pool is taking too much memory", etc.
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 35183311
0
 

Author Comment

by:MrVault
ID: 35183720
thanks. It was a bit hard to follow mostly because a large portion dealt with 32 bit servers, AWE, sql 2000/2005, etc. we're running sql 2008 enterprise edition 64 bit r2. plus it did seem to indicate that sql will use more than just the buffer pool allocation.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question