January 19, 2012 at 3:31 pm
Hi all,
I have a query about setting the Target Server Memory (KB) and Total Server Memory (KB) values based on some recent behaviour on our SQL 2008 server.
It seems that both Target Server Memory (KB) and Total Server Memory (KB) eventually end up being equal to whatever I set as the minimum amount of memory on the server - and never more.
If I leave the server at default settings (i.e. a minimum memory value of 0), SQL appears to assign a very low amount of Total Server Memory (less than 1GB) which invariably leads to a painfully low PLE (50 or less).
I assume this is due to the fact that the server only has 4GB of RAM and the OS is competing with SQL server for memory - otherwise I would have thought SQL server should assign more memory.
Is my thinking correct? Any thoughts would be welcome.
Ben
January 19, 2012 at 6:04 pm
Give some more info:
1. Your Windows version.
2. Windows...is it 32-bit or 64-bit?
3. Your SQL Server version.
4. Is SQL Server 32-bit or 64-bit?
5. Have you enabled AWE?
6. If 32-bit Windows, which setting(s) have you set to enable SQL Server to grab more RAM?
7. What min. RAM figures have you used for SQL Server? 2GB? 3GB?
8. How much free RAM do you have (per Task Manager) when the server is busy. And how much when it is not busy?
9. Do you have any other apps on this server or is it dedicated to SQL Server?
10. Have you noticed any other applications using up a lot of RAM?
Windows shouldn't need 3GB of RAM to run.
January 20, 2012 at 2:13 am
Many thanks for your prompt reply.
Please let me know if you need more information.
1. Your Windows version.
Windows Server 2008
2. Windows...is it 32-bit or 64-bit?
64-bit
3. Your SQL Server version.
SQL Server 2008 Standard Edition
4. Is SQL Server 32-bit or 64-bit?
64-bit
5. Have you enabled AWE?
No
6. If 32-bit Windows, which setting(s) have you set to enable SQL Server to grab more RAM?
NA
7. What min. RAM figures have you used for SQL Server? 2GB? 3GB?
For now I have set it to a minimum of 2GB, max 3.2 GB given the PLE issue.
As I mentioned when I set the minimum to 0, SQL server uses under 1GB of RAM and the PLE drops to around 50 (basically everything grinds to a halt).
8. How much free RAM do you have (per Task Manager) when the server is busy. And how much when it is not busy?
According to task manager there is 0 free physical memory - this is the same whether peak time or not.
PerfMon shows that there is an average of 50 MB available MBytes.
9. Do you have any other apps on this server or is it dedicated to SQL Server?
It's dedicated to SQL although a backup program runs that eats up around 50 MB RAM.
10. Have you noticed any other applications using up a lot of RAM?
No. In task manager only SQL server appears to be using a considerable amount.
January 20, 2012 at 2:15 pm
Strange that SQL Server uses only 1GB of RAM and there is 0 free space. Just to be sure, use MS's Process Explorer to see how much RAM different processes are using, it's more accurate than Task Manager. If you find the same thing, then you should look into the possibility of having memory leak.
January 22, 2012 at 9:49 pm
bathawes 16438 (1/19/2012)
It seems that both Target Server Memory (KB) and Total Server Memory (KB) eventually end up being equal to whatever I set as the minimum amount of memory on the server - and never more.
SQL Server is responding to low-memory notifications from Windows and trimming itself back to the min server memory setting.
If I leave the server at default settings (i.e. a minimum memory value of 0), SQL appears to assign a very low amount of Total Server Memory (less than 1GB) which invariably leads to a painfully low PLE (50 or less).
Again, this is an indication that SQL Server is responding to server-wide memory pressure.
I assume this is due to the fact that the server only has 4GB of RAM and the OS is competing with SQL server for memory - otherwise I would have thought SQL server should assign more memory.
Yes 4GB is not very much, and there are circumstances where the OS will grab much more memory than you might like - for example for caching pages for files transferred over the network.
Any thoughts would be welcome.
It is quite likely the server simply needs more memory for the workload you have assigned to it. That said, I would still consider setting SQL Server to use the Lock Pages in Memory feature. This protects SQL Server from over-aggressive trimming by the OS. To use this on Standard Edition requires start-up trace flag 845: see http://blogs.msdn.com/b/suhde/archive/2009/05/20/lock-pages-in-memory-now-available-for-standard-edition-of-sql-server.aspx and http://support.microsoft.com/kb/970070. Note that the Windows account SQL Server starts under must have the Lock Pages in Memory privilege (see the second link).
January 23, 2012 at 12:47 am
Hi all,
Many thanks for all of your input.
Over the weekend we added an additional 8GB RAM to the server and increased the SQL max memory setting to 8000 MB.
The server is now happily using all 8GB which to me suggests that the memory was desperately needed. Performance stats (in particular paging) are significantly improved :-).
Paul - you were absolutely spot on.
Thanks again,
Ben
January 31, 2012 at 8:42 am
Hi all,
It seems that this case isn't quite solved.
The server now have 12GB of RAM in it and I assigned a "max server memory (MB)" of 8000.
I have been monitoring the SQL server's performance this afternoon and noticed rather erratic Total Memory, Target Memory and Page Life Expectancy counters.
Even though according to SSMS and sp_configure I have set min server memory (MB) to 2000, "Total Server Memory (KB)" appears to drop to 0 at certain points during the day.
At the same time, target server memory also drops to 0, along with PLE and site performance suffers significantly.
Is this possible, or could it be a PerfMon glitch?
If so, what could explain this behaviour?
February 7, 2012 at 7:43 am
Try setting min and max to the same value to disable dynamic memory management by SQL Server. I'm just curious if it fixes your random issues with performance monitor.
If you deem it a perfmon issue, you may want to rebuild the performance counters.
February 7, 2012 at 10:25 am
Thanks for the response,
I think I will give that a try. I believe it to be a PerfMon issue rather than a problem with SQL.
For now, I have set PerfMon to run on the SQL server as opposed to a remote server to see if the problem crops up again.
Ben
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply