Sql-server – SQL Server 2008 R2 Standard Edition not using more than 32GB for BufferPool

sql serversql-server-2008-r2

I had SQL Server 2008 R2 Standard running on Windows Server 2008 R2 Standard with 32 GB of installed Memory.

Since SQL Server 2008 R2 Standard can use 64 GB memory, Windows was upgraded in-place to Enterprise Edition and memory increased from 32 GB to 64 GB.

SQL Server 2008 R2 Standard max memory was then increased from 28 GB to 52 GB.

SELECT name, value [mem_MB]
FROM sys.configurations
WHERE name IN('max server memory (MB)','min server memory (MB)')

Result:

name                    mem_MB
min server memory (MB)   49152
max server memory (MB)   53248

7 day after change, "Total pages" and "Target pages" show 32 GB.

select counter_name, cntr_value
from master..sysperfinfo
where [object_name]='SQLServer:Buffer Manager'
and counter_name in ('Total pages','Target pages')

Result:

counter_name    cntr_value
Total pages          4096000    <--32 GB
Target pages         4096000    <--32 GB

Why is SQL Server only using 32 GB for buffer pool?

Can someone confirm SQL Server 2008 R2 Standard can actually use more than 32 GB for buffer pool?

Best Answer

In case someone reads this article in the future, SQL Server 2008 R2 on Windows Server 2008 R2 Enterprise (upgraded in-place) can and does use 64 GB RAM. Windows was patched last week and rebooted.

After reboot:

select counter_name, cntr_value<br>
from master..sysperfinfo<br>
where [object_name]='SQLServer:Buffer Manager'<br>
and counter_name in ('Total pages','Target pages')

Result:

counter_name      cntr_value<br>
Total pages          7077888    <--54 GB
Target pages         7077888    <--54 GB
Related Question