Solved

Help explain SQL memory components

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 37
Concatenating multiple comments into one row 16 46
sql calculate averages 18 32
DATETIMEOFFSET feature 1 5
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

10 Experts available now in Live!

Get 1:1 Help Now