Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to understand more about the memory usage for SQL Server. I can't seem to figure out what's exactly stored in the memory buffer. My setup is a Windows Server 2008 x64, 8 GB RAM, SQL Server 2008 standard R2, maximum SQL Server memory 6000 MB.

After running SQL Server a couple of hours, the resource monitor shows sqlservr.exe has used about 6000 MB of memory, but when I check the usage for data and plan caches I get less than 6000 MB:

Cached plans          2541 MB
Database data cached  2706 MB
Total                 5247 MB

I used sys.dm_exec_cached_plan and sys.dm_os_buffer_descriptors to find these values, so from these values I don't know what the 750 MB are used for... I don't think SQL Server is reserving memory since this is a x64 machine... Am I missing something here?

share|improve this question
    
Code? Data other than DB data? Nothing at all? –  Andrew Barber May 9 '12 at 14:57
    
@AndrewBarber No code needed. The OP provided plenty of information. –  Thomas Stringer May 9 '12 at 15:11
    
@Shark I wasn't asking for code; I was offering random suggestions for possible answers. :p –  Andrew Barber May 9 '12 at 15:35
1  
@Shark I think Andrew was giving suggestions for what is occupying the remaining memory, i.e. executing code –  JNK May 9 '12 at 15:35
    
@AndrewBarber My apologies!!!! I completely misunderstood your comment. :) –  Thomas Stringer May 9 '12 at 15:38
show 2 more comments

migrated from stackoverflow.com May 9 '12 at 15:06

This question came from our site for professional and enthusiast programmers.

1 Answer

There are other memory consumers outside of the buffer pool. For instance, SQL Server will use memory for worker threads, multi-page allocations, linked servers, extended stored procedures, CLR, etc.

The buffer pool is never going to equate to 100% of memory consumed by SQL Server. It's a common misconception, as well as setting max memory will be the actual max memory SQL Server uses (not true).

EDIT: Here's how I think you could get non-buffer pool memory size. Note: wait for the input of other SQL Server professionals before taking this query as fact. I cannot guarantee that it is absolutely correct.

declare 
    @data_cache_size_mb decimal(12, 2),
    @plan_cache_size_mb decimal(12, 2),
    @total_mem_mb decimal(12, 2)

select
    @data_cache_size_mb = count(*) * 8 / 1024.
from sys.dm_os_buffer_descriptors

select
    @plan_cache_size_mb = sum(size_in_bytes) / 1024. / 1024.
from sys.dm_exec_cached_plans

select
    @total_mem_mb = sum(pages_allocated_count) * 8 / 1024.
from sys.dm_os_memory_objects

select
    @data_cache_size_mb as data_cache_size_mb,
    @plan_cache_size_mb as plan_cache_size_mb,
    @data_cache_size_mb + @plan_cache_size_mb as buffer_pool_size_mb,
    @total_mem_mb as total_instance_mem_mb,
    @total_mem_mb - (@data_cache_size_mb + @plan_cache_size_mb) as non_buffer_pool_size_mb
share|improve this answer
    
is there a way to check the memory usage for those components? or a way to calculate? –  kenux May 9 '12 at 15:53
    
@kenux See my edit. I'll rehash this, I am not completely sure that my theory is absolutely accurate. I'd wait for other knowledgeabe professionals to chime in with their opinions before taking my query as 100% correct/factual. –  Thomas Stringer May 9 '12 at 16:21
2  
Note that in SQL Server 2012 the max server memory setting is much more reliable because the memory manager can now control CLR, multi-page, etc. –  Aaron Bertrand May 9 '12 at 16:28
    
@AaronBertrand I didn't know that. That's a bit of a disadvantage, though, because it's nice to be able to control the buffer pool size. –  Thomas Stringer May 9 '12 at 16:29
    
@AaronBertrand Does my theory look correct in my query?? I'm just spitballing and trying to drill down into OP's question. –  Thomas Stringer May 9 '12 at 16:30
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.