Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Memory allocation Expand / Collapse
Author
Message
Posted Friday, November 1, 2013 12:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:28 AM
Points: 51, Visits: 340
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



Post #1510752
Posted Friday, November 1, 2013 12:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,840, Visits: 35,968
Is there free memory for SQL server to allocate?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1510771
Posted Friday, November 1, 2013 12:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:28 AM
Points: 51, Visits: 340
Yes, it shows Available Physical Memory MB 5,134
The resource monitor shows memory in stand by 4.3 GB and free 800 MB
Post #1510772
Posted Friday, November 1, 2013 1:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,840, Visits: 35,968
Does SQL need more than it's currently allocating? Is there any indication of internal memory pressure?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1510776
Posted Friday, November 1, 2013 1:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:28 AM
Points: 51, Visits: 340
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.
Post #1510779
Posted Friday, November 1, 2013 1:14 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:43 AM
Points: 335, Visits: 299
Rebuild all your indexes... That should create some memory pressure/data cache.
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.
Post #1510781
Posted Friday, November 1, 2013 1:15 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,840, Visits: 35,968
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 2008, MVP
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

Post #1510782
Posted Friday, November 1, 2013 1:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:28 AM
Points: 51, Visits: 340
Thanks.
Post #1510790
Posted Friday, November 1, 2013 3:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:28 AM
Points: 51, Visits: 340
Restart of the SQL service resolved the issue.

Thanks again.
Post #1510825
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse