Solved

Help explain SQL memory components

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

820 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