Memory allocation

Question

Memory allocation

Greg Shinder

Mr or Mrs. 500

Points: 510
More actions
November 1, 2013 at 12:14 pm

#395761

Hello,
Until now I was able to find all my questions already asked and answered, but not today.
The issue:
I have a SQL 2008R2 Std running on virtual Win 2008R2 Enterprise. The server used to have 11GB of RAM, 8 dedicated to SQL.
I have got more memory for that server - a total of 18GB and I have increased "max server memory" to 14GB.
But the SQL does not take more than 11GB.
Total cached memory(MB)10819.0859375
Total SQL Server Memory (MB)11000.0000000
Total physical memory MB 18431.0000000
Available Physical Memory MB 5134.0000000
I have tried the next:
1. decrease Total SQL Server Memory to 6GB and increase it back to 14GB
2. set Total SQL Server Memor to unlimited (2147483647)
Please advice,
Greg

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Is there free memory for SQL server to allocate?

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

Greg Shinder Mr or Mrs. 500 Points: 510 More actions · Answer 2

Yes, it shows Available Physical Memory MB 5,134

The resource monitor shows memory in stand by 4.3 GB and free 800 MB

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

Does SQL need more than it's currently allocating? Is there any indication of internal memory pressure?

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

Greg Shinder Mr or Mrs. 500 Points: 510 More actions · Answer 4

The page life expectancy is going averaging around 100 sec. The proc cache hits are around 90%. And even if everything is ok, I would like to see more data cached.

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 5

Rebuild all your indexes... That should create some memory pressure/data cache. :w00t:

Seriously though this can do well, but beware if you have high transaction volumes and are not using Enterprise edition.

With Standard edition rebuilding a the Clustered Index for a table can cause blocking during the rebuild.

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

Maybe put max server memory back to 14GB and leave it for a while, monitor available MB, watch the ring buffer for memory notifications, watch total and target server memory.

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

Greg Shinder Mr or Mrs. 500 Points: 510 More actions · Answer 7

Greg Shinder

Mr or Mrs. 500

Points: 510

November 1, 2013 at 1:38 pm

#1663589

Thanks.

Greg Shinder Mr or Mrs. 500 Points: 510 More actions · Answer 8

Restart of the SQL service resolved the issue.

Thanks again.