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?

  • Code? Data other than DB data? Nothing at all? May 9, 2012 at 14:57
  • @AndrewBarber No code needed. The OP provided plenty of information. May 9, 2012 at 15:11
  • @Shark I wasn't asking for code; I was offering random suggestions for possible answers. :p May 9, 2012 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, 2012 at 15:35
  • @AndrewBarber My apologies!!!! I completely misunderstood your comment. :) May 9, 2012 at 15:38

1 Answer 1


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.

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

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

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

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

    @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
  • is there a way to check the memory usage for those components? or a way to calculate?
    – kenux
    May 9, 2012 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. May 9, 2012 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. May 9, 2012 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. May 9, 2012 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. May 9, 2012 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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