MrVault
asked on
Help explain SQL memory components
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
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.
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
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.