January 29, 2010 at 8:21 am
We are running SQL 2005 Standard edition (Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
on Windows 2003 Enterprise edition.
The OS has been allocated 15 GB of RAM
SQL we have enabled AWE
Min and max memory have been set both to 12GB
We have also given lock pages to memory permission to SYSTEM account which is running SQL server.
Still when I see performance counters for SQL memory manager/target server = 10GB and total server = 3.8 GB
The task manager shows pagefile usage around 14GB
We are seeing this warning message in the SQL log
“01/26/2010 04:33:43,spid2s,Unknown,Warning: unable to allocate 'min server memory' of 12000MB.”
Why are we getting this error? Why is SQL not able to allocate min memory
January 29, 2010 at 8:31 am
Do you have the /PAE switch in your boot.ini file on the server?
January 29, 2010 at 3:14 pm
You should not set the min memory equal to the max memory. This prevents SQL Server from managing memory correctly and can cause some problems (as you are seeing).
Drop the min memory setting to 0 if you are only running SQL Server on this box. If you are running SSIS/SSRS or other applications, then I would set the min memory to no more than 10GB.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 29, 2010 at 9:23 pm
Yes we have /PAE enabled for this server
January 29, 2010 at 11:46 pm
Can you please confirm if you are mentioning about Max memory as 10GB or Min memory as 10GB
January 30, 2010 at 5:53 am
Drop the min memory value - leave the max where it is.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2010 at 10:47 pm
I have now configured it as follows, minimum memory 5GB and maximum memory 12GB.
Min memory per query is 6MB.
Page file size usage shows around 14.5GB
When i check performance counter for SQL memory manager
Target memory : only 5GB
Total memory : 3.8 GB
I am not able to understand why is SQL consuming only small amount of memory and not exceeding 4GB
where as at the same time page file usage is increasing. This page file size gets down once we restart the SQL but as we start running queries and due to some heavy loaded queries i believe the page file is increasing. I would have had no issues but once the page file goes beyond 15.5GB the SQL starts responding slow and we have SQL time out.
January 31, 2010 at 11:00 pm
Why did you change the memory per query option? That generally does not need to be changed and should be left at the default value.
On x86 systems, task manager has a problem with displaying how memory is being used above 4GB. It shows up as PF usage even when it is not being used that way. You need to use perfmon to determine the actual usage.
Now, with that said - are you sure you are seeing target memory and total memory in the counters as those values? Are you sure the values you have defined as min/max values are in MB's and not KB?
When setting those values for SQL Server, the setting is in MB - so you would set the value as 5120MB for min memory setting and 12288MB for the max setting of 12GB.
If you are seeing 14.5GB of PF usage, but SQL Server's total memory (from perfmon) is only 3.8GB then it cannot be SQL Server that is taking that memory. There has to be something else going on.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 2, 2010 at 12:03 am
Jeffrey Williams-493691 (1/29/2010)
You should not set the min memory equal to the max memory. This prevents SQL Server from managing memory correctly and can cause some problems (as you are seeing).
Can you please provide a reference for that statement Jeffrey?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2010 at 1:45 am
TheSQLGuru (2/2/2010)
Jeffrey Williams-493691 (1/29/2010)
You should not set the min memory equal to the max memory. This prevents SQL Server from managing memory correctly and can cause some problems (as you are seeing).Can you please provide a reference for that statement Jeffrey?
From Books Online:
Optimizing Server Performance Using Memory Configuration Options
"Do not set min server memory and max server memory server configuration options to the same value, thereby fixing the amount of memory allocated to SQL Server. Dynamic memory allocation gives you the best overall performance over time."
Paul
edit: added the relevant extract
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2010 at 2:25 am
kakapuri.naveen (1/29/2010)
The OS has been allocated 15 GB of RAM
What do you mean by this? In what sense have you allocated 15GB to Windows? Is this a VM?
kakapuri.naveen (1/29/2010)
SQL we have enabled AWE
Do you see the message "Address Windowing Extensions is enabled" in the SQL Server Error Log? This should be logged as the SQL Server starts up.
kakapuri.naveen (1/29/2010)
Min and max memory have been set both to 12GB
It sounds like you have done this in an attempt to 'force' SQL Server to reserve more memory. What else have you tried? Any trace flags set? Any other changes in server settings from default?
kakapuri.naveen (1/29/2010)
We have also given lock pages to memory permission to SYSTEM account which is running SQL server.
How did you manage that? The Local System account has the 'lock pages in memory' privilege by default. Do you mean some other account?
kakapuri.naveen (1/29/2010)
Still when I see performance counters for SQL memory manager/target server = 10GB and total server = 3.8 GB
Is the server dedicated to this one instance of SQL Server, or are there other instances or applications running?
Finally:
Have you enabled the /3GB switch in boot.ini? This is not recommended unless you have received specific guidance from Microsoft Customer Support. It is not required if AWE is enabled, and usually does more harm than good.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2010 at 2:37 am
Hi Jeffery,
I have checked again and again and whatever values i have put are accurate, as per you the taskmanager does not show correct memory usage, that is fine but it does show the PF usage. and SQL is only using 3.8GB hence i am not sure why the pagefile would increase without SQL paging. The other reason behind it is because when i restart the server the pagefile size is low as 586 MB
and as soon as i start running queries it starts growing.
thanks,
naveen
February 2, 2010 at 5:48 am
kakapuri, you have gone back and forth several days on this forum to try to figure out your issue. Time for me to yet again recommend that you get a professional tuner to remote into your system to determine what is really going on with it, make recommendations, and to teach you how to monitor it in the future. This should not take more than a few hours. Or you can spend another week or more here and still not get to the root cause(s)...:cool:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2010 at 5:57 am
About min = max memory - See here: http://msdn.microsoft.com/en-us/library/ms178067.aspx
Setting the Memory Options Manually
There are two principal methods for setting the SQL Server memory options manually:
In the first method, set min server memory and max server memory to the same value. This value corresponds to the fixed amount of memory to allocate to the SQL Server buffer pool after the value is reached.
In the second method, set min server memory and max server memory to span a range of memory values. This method is useful where system or database administrators want to configure an instance of SQL Server in conjunction with the memory requirements of other applications that run on the same computer.
That says to me it is acceptable to set them equal. And not having to wait for memory grants can be beneficial in some cases.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 2, 2010 at 10:35 am
TheSQLGuru (2/2/2010)
That says to me it is acceptable to set them equal. And not having to wait for memory grants can be beneficial in some cases.
Oh it is - don't get me wrong, I was just providing the reference asked for. People who know what they're doing can certainly set them the same for any number of good reasons. Personally, I tend to default to setting min lower than max - but it's not a rule I'm married to by any means 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply