SQL Server 2008 R2 Max Memory Settings

Question

SQL Server 2008 R2 Max Memory Settings

Grizzly Bear

Ten Centuries

Points: 1192
More actions
May 16, 2011 at 1:11 pm

#238369

I have set the Max Memory Settings on SQL Server 2008 R2 to 61440 MB on a box with 64 GB available.
OS: Windows 2008 R2 Edition
But when I run this query (below) i see the physical_memory_in_use_kb more = 64373144. How is this possible?
BTW my server re-booted automatically and the SQL Dump says Memory Usage at 99%, because of which I am diggin into this problem.
Any help will be appreciated.
SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
page_fault_count, memory_utilization_percentage,
available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Max memory set the limit to the buffer pool. SQL does also allocate memory outside the buffer pool - the non-paged memory - for things like thread stacks, backup buffers, CLR, linked server drivers and a few other things.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

I'd suggest you start with 56GB out of 64GB for SQL Server max memory and work from there. There have been so many blog posts from the microsoft teams over the years about how much memory is used by SQL Server, I sometimes wonder why some of it doesn't get into training courses and BOL.

I run a number of sql servers with 64GB of ram, the most I allocate on max memory is 56GB - I do monitor memory in real time ( by a dashboard ) but even so there's just so many other uses for your server memory - the max memory setting is really just the data cache ( I believe in Denali this will be different )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Grizzly Bear Ten Centuries Points: 1192 More actions · Answer 3

Thanks Gila

Was aware of CLR, was not aware of others. But the CLR was only taking up 8 KB.

Reduced the SQL Server Memory to 58 GB, just to be on the safe side