Memory Usage Issues

Last Post 07 Mar 2008 04:48 AM by TRACEYSQL. 18 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
BlakeK
New Member
New Member

--
12 Oct 2007 10:20 AM
Our SQL Server 2005 machine recently started getting windows messages that "Your system is running low on memory, the virtual page file has been increased".
And in the SQL ERRORLOG file, we are getting the following message:

2007-10-12 12:33:25.41 spid1s 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): 48164, committed (KB): 12630920, memory utilization: 0%.
2007-10-12 12:38:53.87 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 2695816, committed (KB): 8334336, memory utilization: 32%.
2007-10-12 12:44:21.94 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 656 seconds. Working set (KB): 2608056, committed (KB): 5440552, memory utilization: 47%.
2007-10-12 13:09:30.22 spid1s 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): 320312, committed (KB): 15898384, memory utilization: 2%.
2007-10-12 13:27:30.53 spid1s 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): 380052, committed (KB): 15129608, memory utilization: 2%.
2007-10-12 14:01:35.09 spid1s 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): 1255772, committed (KB): 14611952, memory utilization: 8%.
2007-10-12 14:09:34.64 spid1s 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): 420660, committed (KB): 15384008, memory utilization: 2%.
2007-10-12 14:27:35.46 spid1s 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): 232272, committed (KB): 16150840, memory utilization: 1%.
2007-10-12 14:53:36.88 spid1s 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): 45220, committed (KB): 15687312, memory utilization: 0%.

Our server has been running with 12GB of RAM for the last year or so, and these messages just started about a month ago.
So, we decided to add more RAM to our server. We doubled it, so the server now has 24GB of RAM.
The server is also running 4 3.66Ghz Xeon 64-bit processors
OS is Windows Server 2003 R2 Standard x64
SQL version Standard Edition 64-bit - 9.0.3054

Also, this server runs nothing except SQL Server, no other applications are installed or running.
At this point, I am out of ideas.
Is it possible that SQL really needs more than 24GB of RAM?!
What can be done to try and determine what is using all the memory, or if a leak exists?

The only information I have found about the SQL errorlog message above was this:
http://support.microsoft.com/kb/918483

And that article talks about locking pages in memory and such, but the problem is that that article only applies to the Enterprise Edition of SQL 2K5. Since we are running Standard Edition, the information in that article does help.

If any additional information about our server would help diagnose the problem, let me know and I will provide it.
I appreciate any thoughts on what may be causing this, and what might be the best course of action to correct it.
BlakeK
New Member
New Member

--
12 Oct 2007 11:41 AM
Can you tell me which "Performance Object" and "Counter" I need to select in perfmon?
I looked under "Memory" and "SQLServer:MemoryManager", but I didn't see a counter for how much is in use by SQL.
The MDF is currently 487GB and it is stored on a 1TB RAID array.
The LDF is on a seperate RAID array, and the BAK and TRN files are stored on a 3rd RAID array.

Thanks
BlakeK
New Member
New Member

--
12 Oct 2007 05:10 PM
gunney,
On the "General" tab of the "Server Properties" window reports "Memory" as 24574 (MB).
On the "Memory" tab the "Maximum Server Memory (in MB)" was set to 23500. I have changed it to 22500.
I will let you all know if this helps.
I am running maintenance tonight, so I won't have stats until normal processing resumes on Monday.
In the meantime, if anyone has any other ideas to try, let me know.

Thanks
BlakeK
New Member
New Member

--
14 Oct 2007 07:00 PM
quote:

Originally posted by: rm
Possible if only part of data are actively used. For original issue, double check if sql service account has 'lock pages in memory' user rights on the server.


If you check the KB article I linked in my original post, the "lock pages in memory" feature is only applicable if you are running Enterprise Edition, and since we are running Standard Edition that is not an option.
BlakeK
New Member
New Member

--
15 Oct 2007 09:38 AM
Really? That's interesting.
SQL Server is running under "Local System Account".
When I goto add a user, I don't see one named "Local System Account".
Would the appropriate account be "SYSTEM" or "LOCAL SERVICE" or something else?

I am following these instructions to set the privileges...

How to lock pages in memory for an instance of SQL Server 64-bit
In SQL Server 64-bit, you can improve performance by locking memory that is allocated for the buffer pool in physical memory. To enable this capability in SQL Server 64-bit, you must grant the SQL Server startup account the Lock pages in memory permission. When you enable this option, you must restart the computer. To do this, follow these steps:
1. Click Start, click Run, type gpedit.msc, and then click OK. The Group Policy window appears.
2. In the left pane, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Click User Rights Assignment. The policies appear in the right pane.
5. In the right pane, double-click Lock pages in memory.
6. In the Local Security Policy Setting dialog box, click Add User or Group.
7. In the Select Users or Groups dialog box, add the account that has permissions to run Sqlservr.exe, and then click OK.
8. Close the Group Policy window, and then restart the SQL Server service.

Also, what other policies should the account be granted access to, as you mentioned above there are several others, aside from the lock pages in memory?

Thanks again.
BlakeK
New Member
New Member

--
15 Oct 2007 02:26 PM
Thanks rm.
I have verified all those services have rights for the local Administrators group.
I will keep you all up to day on whether this alleviates the problem.

Thanks all.
SQLUSA
New Member
New Member

--
22 Oct 2007 03:21 PM
Lock pages in memory is pretty standard.

I assume you don't want to pay for Enterprise?

Question: what changed? DB got bigger? More sprocs?

It is also possible that Microsoft wants you on the Enterprise.

Kalman Toth
SQL Server Training - http://www.sqlusa.com
TRACEYSQL
New Member
New Member

--
05 Mar 2008 09:22 AM
Im going to piggy back of this thread.
Same thing here new box.

Target Server Memory 60,919,536
Total Server Memory 10,70,384

Paging File is 10 gig.

So perhaps i set the MAX Memory to 45 GIG.
Right now it is set to default.

TRACEYSQL
New Member
New Member

--
05 Mar 2008 10:57 AM
Drive C 132 gig space
All other data on SAN.

So the paging should be larger.
I set Max Memory in SQL to 51 Gig so its fixed now.
Did reboot the cluster and so far not got the message - only doing simple 1 gig and 10 gig inserts right now nothing too crazy.



So does the Paging File have to be increased to 51 GIG perhaps ....
TRACEYSQL
New Member
New Member

--
05 Mar 2008 01:19 PM
Pagefile should be same as i set SQL 51 GIG?

Really.
TRACEYSQL
New Member
New Member

--
05 Mar 2008 02:02 PM
wow i was not aware of that, i will make the change on the new server.

Our last servers only had 10 gig so i am exicted about the more memory but did not like the error message i received. Putting it to a max setting seemed to have fixed that.


Thank you
SQLUSA
New Member
New Member

--
05 Mar 2008 11:39 PM
Does Windows really paging at those memory levels, 64GB?

Can this feature be turned off altogether?

This is from the time (Late stone age period of Computer Science) when the PC-s did not have enough memory, so Microsoft reinvented the virtual memory by paging.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC - March 17-20: http://www.sqlusa.com/order2005bitr...008031720/
TRACEYSQL
New Member
New Member

--
06 Mar 2008 03:57 AM
Whoops the paging file was 102 GIG not 10 Gig i must have read it wrong yesterday.

Does Windows really paging at those memory levels 64 GB ?

Not sure but i do not i go this
A significant part of sql server process memory has been paged out in SQL and no one using except me right now.

I did not have MAX Memory set just left it out of the box setups defaults.

So set MAX to 51 GIG not got the message again.

I will continue doing more testing - thanks for you help guys.
TRACEYSQL
New Member
New Member

--
06 Mar 2008 04:46 AM
So this morning i log in

Still getting something, and that was at 6 in morning..

Nothing is running on server there is only one database that has 1 gig file in it.

No backups, nothing

Still getting below.

Date 3/6/2008 6:13:15 AM
Log SQL Server (Current - 3/6/2008 8:27:00 AM)

Source spid1s

Message
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 57636 seconds. Working set (KB): 92840, committed (KB): 369192, memory utilization: 25%.

Not sure what else to go check now.
TRACEYSQL
New Member
New Member

--
06 Mar 2008 05:09 AM
I have not got the AWE ticked nor have i locked pages in memory for an instance of 64 bit.
Should i do both of these.

TRACEYSQL
New Member
New Member

--
06 Mar 2008 06:07 AM
Intel Xeon X7350 2.93 GHZ 64 GB Ram 8 processors.

SQL build is 9.03054 SP2 64bit

Microsoft Server 2003 R2 Enterprise X64 Edition SP2

Cluster environment


(Tested this in Test environment all modules same build nothing found - was on 32 bit mind you on test)

Following along this thread http://support.microsoft.com/kb/918483

Process
Private Bytes 7,205,281,792
Working Set 6,898,059,696

SQL Server Memory Manager
Target Server Memory 52,428,800
Total Server Memory 6,289,032 (not sure if this is supposed to be around 52 gig or not)

On thread it says when the Working Set counter is less than the value of the Total Server Memory(KB) counter ?

Still trying to understand it all to tell you the truth
TRACEYSQL
New Member
New Member

--
07 Mar 2008 04:48 AM
Thanks gunneyk we just did this yesterday and rebooted cluster.
I will see if i get any more messages.

I do some one io testing and large updates processes.

I appreciate your help guys.




TRACEYSQL
New Member
New Member

--
07 Mar 2008 01:47 PM
Everything is looking great my end. No messages appearing.

Thanks for your time and help.

Just did a 40 gig Restore and it too 7 minutes thats amazing - on old production servers it be 1 hour plus.

I did see another thread that had same issue and forward this reply to them so hoping that knowledge is helping others too.

Its good to share and i appreciat your guidance.


Have great weekend.
TRACEYSQL
New Member
New Member

--
18 Mar 2008 05:49 AM
The problem i had was it was still Paging regardless of service pack in SQL

I have now resolved this by putting the Min Memory of 8196 and Max 50 Gig
and now after issuing DBCC CHECKDB the PF does not go crazy.

You are not authorized to post a reply.

Acceptable Use Policy