Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Question about buffer pool usage

Author  Topic 

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-12 : 11:30:20
I'm currently running SQL Server 2012 Standard Edition which is limited to 64 gigs of RAM. I queried the buffer pool and found that it is using 51 gigs of space.

Does that mean that I'm not fully making use of the 64 gigs of RAM or is the remainder allocated to other SQL Server requirements?

I want to know if I would benefit from migrating to SQL Server 2014 to take advantage of 128 gigs of RAM and/or the extended buffer pool.

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-12 : 12:03:16
Look up what the Maximum Server Memory setting is (SSMS object explorer, right click server name, properties, Memory tab). If you have 64 Gigs of system memory, some of it needs to be kept away from SQL Server for other purposes such as OS, other services running on the system etc. Usually people recommend anywhere from 4 to 8 Gigs on a dedicate server for such purposes. So on a 64 bit system, you would want to set the maximum memory as about 56 to 60 Gigs.

If the max memory on your server has been set at some number such as 56 Gigs (the setting is in MB), and if buffer pool is taking up 51 Gigs, that seems reasonable. If you add more memory to the system, the max memory setting can then be increased to 64 Gigs. Buffer pool alone will still not reach 64 Gigs on standard edition (unlike pror versions, in SQL 2012, the maximum memory specified is not just for the buffer pool ), but would be close. In that case, upgrading to 2014 and adding more system memory would let you have a larger buffer pool.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-05-13 : 01:23:53
To view the memory used by SQL Server use the SQLServer:Memory Manager\Total Server Memory counter in Performance Monitor.
Don’t rely on Task Manager for the correct memory usage - some notes on this topic are here :
http://www.sqlserver-dba.com/2013/05/task-manager-not-showing-correct-sql-server-memory-usage.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -