patternsqlMinor
SQL Server memory usage
Viewed 0 times
sqlusagememoryserver
Problem
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
I used
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 MBI 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?Solution
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.
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_mbCode Snippets
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_mbContext
StackExchange Database Administrators Q#17647, answer score: 4
Revisions (0)
No revisions yet.