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've a server with 12 GB of physical RAM. I just lowered its 'Max Server Memory (MB)' setting from 10 GB to 8 GB.

If I look at the processes' committed memory in Perfmon, I see:

select *
from sys.dm_os_performance_counters
where [object_name] like 'SQLServer:Memory Manager%';
go
Target Server Memory (KB):  8388608
Total Server Memory (KB):   8388608

Showing that SQL Server has already relased the additional 2 GB of memory from the Buffer Pool.

However, if I look at the sys.dm_os_sys_info:

select [bpool_committed]
     , [bpool_commit_target]
     , [bpool_visible]
from sys.dm_os_sys_info;
go

I get inconsistent results:

bpool_committed     = 1048576
bpool_commit_target = 1048576
bpool_visible       = 1048576

Shouldn't sys.dm_os_sys_info reflect the information on the performance counters?

I'm running Microsoft SQL Server 2005 SP1 on Windows Server 2003.

share|improve this question
    
What are the breakdowns for the other counters in the Memory Manager object (e.g. Free Memory, Database Cache Memory, SQL Cache Memory, Optimizer Memory, Log Pool Memory)? If there is some inconsistency here I bet dollars to donuts a service restart will straighten it out. –  Aaron Bertrand May 21 '12 at 14:24
add comment

1 Answer

up vote 1 down vote accepted

The values were indeed inconsistent and went back to normal after an instance restart.

I recommend not using the sys.dm_os_sys_info for this kind of information since it's not reliable and use the performance counters instead.

share|improve this answer
add 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.