May 11, 2011 at 2:37 am
We have an 32 bit windows server 2008 Enterprise Edition SP2 with 32GB memory with on it installed a version of SQL Server 2008 standard edition (32 bit).
I enabled AWE in SQL Server and set max server memory on 30720MB so SQL Server can use almost all memory.
If I check the memory used, it slowly takes more memory untill it reaches 22216704 and stays constant.
the query for this I found in another post:
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)','Logins/sec','Logouts/sec')
Also the total server memory with the above given query stays on 22216704.
object_name: SQLServer:Memory Manager
counter_name:Target Server Memory (KB)
cntr_value: 22216704
cntr_type: 65792
What can be the cause that SQL server does not take more memory?
Thanks in advance!
May 11, 2011 at 2:44 am
Possibly that it doesn't need more at present. The target memory is the amount SQL thinks it wants at the moment, so if it has that, it has what 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
May 11, 2011 at 2:47 am
Did you enable lock pages in memory?
oh yeah, sorry thats ~22GB 🙂
__________________________
Allzu viel ist ungesund...
May 11, 2011 at 3:00 am
It is possible that SQL Server does not need more memory, but I find i curious that the memory usages stays exactly the same each time, while the databases are growing.
Yes, lock pages is enabled?
May 11, 2011 at 4:03 am
Select * from sys.dm_os_performance_counters
where counter_name = 'Page life expectancy'
Send result of this query and check OS performance monitor as well CPU utilization
Select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.status !='sleeping'
order by sp.cpu desc
check the result of this query if any SOS_* type wait is appearing or not in any query
if above first query result cntr_value is above 300 secs then there is no need of Extra RAM
if above second query result is NO SOS_* wait type then there is no need of Extra RAM
what is the size of Database ?
check these stuff then further more details I will provide to you
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 11, 2011 at 4:10 am
Edit: Actually I don't want to know...
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
May 11, 2011 at 4:29 am
Thanks for the responses so far. Here are some answers.
Select * from sys.dm_os_performance_counters
where counter_name = 'Page life expectancy'
Returns:
object_name counter_name instance_name cntr_value cntr_type
SQLServer:Buffer Manager Page life expectancy 98640 65792
SQLServer:Buffer Node Page life expectancy 000 98640 65792
Memory usage system 23,2 GB (72% used)
Hard Faults/min SQL Server approximately 0
CPU utilization for sqlservr.exe is on average 15-20% with spikes from 0 up to 100%
for the total server the CPU utilization is only 2% higher.
Select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.status !='sleeping'
order by sp.cpu desc
Returns sometimes SOS_SCHEDULER_YIELD as 'lastwaittype' for queries.
There are multiple databases on the server. Three of them are approx. 15GB and several smaller databases. The total size will be 60/70 GB.
Hopefully I have provided you with enough information.
May 11, 2011 at 4:48 am
Basically it looks like SQL is not under any form of memory pressure and for the moment doesn't need more memory than it has.
The max server memory is not a value that SQL will allocate up to. It's just the maximum allowable size of the buffer pool.
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
May 11, 2011 at 5:02 am
there is no memory pressure on your server,memory allocation based on the load of the Server,your page life is 1644 secs that is good and no need of memory,now come to SOS_* wait
Are you using Linked Server or heavily joins table queries ?
Total physical or logical processors ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 11, 2011 at 5:43 am
Good to know that the amount of memory is sufficient.
The queries that are running on the databases are heavily joined. In peak hours those queries are executed all the time. Those queries run on tables of 3GB with many rows.
This is a known issue and we are investigating if this can be minimized in the near future.
There are 2 physical and 8 logical processors (intel xeon 3.0GHz) on the machine.
Regards,
Royke
May 11, 2011 at 5:57 am
Apply first the Service Pack 2 if this is not R2 and apply CU 7 if this is R2,this problem has resolved in these updates
SQL Server 2008 R2 Service Pack 1 CTP also available
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 11, 2011 at 6:07 am
Syed Jahanzaib Bin hassan (5/11/2011)
Apply first the Service Pack 2 if this is not R2 and apply CU 7 if this is R2,this problem has resolved in these updates
What problem?
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
May 11, 2011 at 8:18 am
Many thanks for your insights and comments.
It is good to know that SQL Server does not take more memory because it doesn't need it. I was afraid that there was something wrong with the settings which caused that SQL Server could not allocate more memory than the approx. 22 GB.
The databases are growing and now I know, that there is still memory available for SQL Server to use.
SP2 has been installed and the other updates will be as soon as possible.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply