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
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
sys.dm_os_sys_info reflect the information on the performance counters?
I'm running Microsoft SQL Server 2005 SP1 on Windows Server 2003.