Solved

Help explain SQL memory components

Posted on 2011-03-16
4
200 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now