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 01, 2013 12:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:03 PM
Points: 49, Visits: 309
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 01, 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 @ 9:59 AM
Points: 41,508, Visits: 34,425
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 01, 2013 12:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:03 PM
Points: 49, Visits: 309
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 01, 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 @ 9:59 AM
Points: 41,508, Visits: 34,425
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 01, 2013 1:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:03 PM
Points: 49, Visits: 309
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 01, 2013 1:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 12:18 PM
Points: 253, Visits: 216
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 01, 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 @ 9:59 AM
Points: 41,508, Visits: 34,425
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 01, 2013 1:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:03 PM
Points: 49, Visits: 309
Thanks.
Post #1510790
Posted Friday, November 01, 2013 3:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:03 PM
Points: 49, Visits: 309
Restart of the SQL service resolved the issue.

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

Add to briefcase

Permissions Expand / Collapse