SQL Server not using available memory

Question

SQL Server not using available memory

stevey

SSC Veteran

Points: 212
More actions
June 26, 2013 at 11:39 am

#395045

I've got 64-bit Windows 2003, SQl Server 2005 Standard Edition. WE have 3 instances on this one server, 32 GB of ram total. All 3 instances have max server memory set to the default. My problem is that 2 of the instances are using 7 GB each, the other 5 GB. One of the instances needs additional memory. According to perfmon, there is 8 gb. available . Why can't the instance that needs memory use what is availble? Other counters - Target memory - 15 GB.

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

How have you determined that instance "needs" more memory?

Given that you have not set the max memory in SQL it seems that SQL doesn't believe it needs any more memory. SQL will use all the memory it wants up to the max memory setting (and a bit more that isn't controlled by that setting).

For example I had an app running in SQL 2008 that NEVER used more than about 600MB of memory, it was given access to 5 times that but under no circumstance did it use more than 600MB. My point being that it is entirely possible that your app's queries don't use all memory that is actually has access to, that SQL doesn't need that memory..

CEWII

stevey SSC Veteran Points: 212 More actions · Answer 2

SQL Memory Manager->Target Server Memory15 GB

SQL Memory Manager->Total Server Memory8 GB

SQL Memory Manager->Target Server Memory14 GB

SQL Memory Manager->Total Server Memory7 GB

Doesn't "Target Server Memory" represent what SQL would like to use?

Both instances seem to require more memory, based on those counters, and in SQL Profiler, I am also getting "Sort Warnings".

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

First things first, with multiple instances, you do not want max server memory at default, it's going to cause problems.

Figure out what memory to leave for the OS (I'm sure someone will post the link to Jonathan's troubleshooting book, see chapter 4)

As for sort spills, they're not a prime indicator of insufficient memory, due the shear amount of memory sorts need (> total size of data), they'll spill even if SQL has all the memory it needs.

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

stevey SSC Veteran Points: 212 More actions · Answer 4

Thanks Gail. I will change the "max" setting from the default. I'm just confused though. You would think that with all 3 instances set to the default that at least one of the instances would have grabbed all of the available memory. I don't get why there is still 8 GB available.