Inconsistent information from different DMVs

Question

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.

Answer 1

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.