2

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?

6
  • 1
    What makes you think it needs to use more memory? What do the wait stats show? 64GB is the max for 2008 R2 Standard.
    – Tara Kizer
    Jan 2, 2018 at 21:25
  • How big are the databases? Are you querying enough data to fill the memory?
    – Greg
    Jan 2, 2018 at 21:33
  • I agree with both @TaraKizer and Greg. Can you give us some insight into why you threw hardware at your situation? Are you on spinning disks or solid state? Are your data files and log files on different partitions / disks? What is the real issue you are trying to resolve? Also, one last question.... Why are you still on a SQL 2008 R2 instance?
    – H.79
    Jan 2, 2018 at 21:41
  • Agree with everyone, and, why do you have MinServerMemory set to 48Gb (very high). Is there a particular reason? If Sql Server ever reaches that amount, it will hold and not release it (even to the OS if it needed it), which can cause some unexpected issues.
    – rvsc48
    Jan 2, 2018 at 21:47
  • Thanks all for your help. This server is dedicated to SQL Server and runs 3rd party software that requires SQL Server 2008 R2. Database size is 376 GB and mostly image data type.
    – Ken Kim
    Jan 2, 2018 at 22:05

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.