March 26, 2010 at 8:07 am
Hello,
I have a SQL Server 2005 Enterprise Edition with 8GB of RAM. The server is in database mirroring set up with another another server. I set it up with High safety without automatic failover mode. Transactions are being inserted into the database in the real time. Users are connected to the database through applications during business hours. I also have partitioning implemented on transaction tables.
Strangely, SQL Server is taking over 90% of RAM. It's taking little more than 7.2 GB of 8GB of RAM. I recently upgraded RAM to 16 GB. The SQL Server is starting taking 15.3 GB of RAM.
Any idea of why it is taking that much RAM? Is it because Database Mirroring or Partitioning?
Thanks,
Satish
March 26, 2010 at 8:40 am
Presumably this is 64bit? SQL loves memory and is designed to take whatever it needs. As data is read in it remains in memory and SQL will only release it if the OS requires it. You are seeing normal behaviour, its nothing to do with mirroring.
What you should do is set max memory for SQL to something like 14GB on a server with 16GB
---------------------------------------------------------------------
March 29, 2010 at 7:51 am
Thanks for the reply. I will set the max memory limit.
March 29, 2010 at 8:12 am
Are you using Physical box only for sql server ? then you can go for MAX option but if not then i think it might not be a good idea coz when we set MAX then sql doesnt release memory for OS.
refer this link http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx
You are on 64 bit, so don't worry about AWE.
By default, SQL server will dynamically allocate memory. This is configurable to a window of dynamic memory (set a min and max) or you can set it to allocate a specific amount of memory.
SQL 2005 does it rather well (MS made big improvements here).
Every system is different. It is best to start with dynamic memory allocation and do some performance testing to see if you need to set limits or specify memory allocation manually.
BUT also refer this article http://msmvps.com/blogs/omar/archive/2007/09/19/a-significant-part-of-sql-server-process-memory-has-been-paged-out-this-may-result-in-performance-degradation.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 29, 2010 at 9:03 am
its the lock pages in memory that prevents SQL releasing memory to OS, not the max setting. that just sets an upper limit.
See how much memory is available via task manager to check any signs of shortage of memory overall having set the value for SQL.
---------------------------------------------------------------------
March 29, 2010 at 9:54 am
I went through my SQL logs. Once or twice a day, i am seeing the following message:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 52076, committed (KB): 15021608, memory utilization: 0%.
Based on the message, please let me know what setting should i be changing to prevent the paging issue?
From Task Manager, sqlservr.exe is showing 15.2GB of Mem Usage, 15.62 GB of VM Size.
Please route me in the correct direction here....Appreciate all the help.
March 29, 2010 at 10:23 am
you should set a max memory limit of 14GB at the most.
then assign the 'lock pages in memory' right to the service account running SQL.
That will require a restart to take effect.
---------------------------------------------------------------------
March 29, 2010 at 10:27 am
My questions are:
How can i assign the 'lock pages in memory' right to the service account running SQL?
I'm running SQL 2005 Enterprise Edition on Windows Server 2003 x64 OS box...Why am i seeing this behavior?
March 29, 2010 at 10:29 am
Satish-219904 (3/29/2010)
How can i assign the 'lock pages in memory' right to the service account running SQL?
You have to assign that right through the Active Directory GPO. If the the service account is not a domain account, though, you have to assign it through the GPO on the local box only.
March 29, 2010 at 10:41 am
1.On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
2.On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3.Expand Security Settings, and then expand Local Policies.
4.Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
5.In the pane, double-click Lock pages in memory.
6.In the Local Security Policy Setting dialog box, click Add.
7.In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
As you are not setting a max memory SQL is eating up memory until it starves the OS, at that point it is forced to give up memory. Hence you need to limit what SQL will take allowing enough for other processes, (espec. OS), and then prevent SQL from having to give up its memory.
If you start seeing application freezes or other odd behaviour, revisit max memory, so don't go too high to start with.
---------------------------------------------------------------------
March 29, 2010 at 10:54 am
Thanks for the information. Is there any drawbacks of doing this on a production box?
March 29, 2010 at 10:57 am
Also the other person Bhuvnesh send this article:
George, What do you think about it?
March 29, 2010 at 12:57 pm
depends how often you are seeing the message and if you see the exact same symptons.
If you are at SP3 you should have this hotfix.
try the setup I suggested and see how it goes.
---------------------------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply