process memory has been paged out

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

process memory has been paged out

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2008-06-09 : 08:29:09

Hi all

I frequently see the following message on SQL Server log

2008-06-09 07:46:18.17 spid3s 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): 1079156, committed (KB): 17156388, memory utilization: 6%.

What does it indicates and what appropriate action has to be taken to fix it.

The database runs on

SQL 2005 Dev 64-bit SP2 9.00.3042.00
Win 2003 standard x64 SP2 16GB RAM

Thanks.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-06-09 : 15:26:57

we eliminated this error by configuring Max Server memory (MB). in short, SQL Server detects that some of the memory it is using is in fact in the pagefile. This is bad for performance, so this warning is written to the errorlog.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-09 : 22:46:58

Ensure sql service account has 'lock pages in memory' user rights on the server.

venkath
Posting Yak Master

202 Posts

Posted - 2008-06-10 : 10:52:39

In one of the article I found it works only for Enterprise edition

Can I set this for Dev edition also?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-10 : 19:28:34

Yes only Enterprise edition supports that. Well, for temporary purpose, Rebooting the server will solve the issue. They have new hot fixes for this issue and try applying that. Try to limit SQL server memory with (max and min memory).

venkath
Posting Yak Master

202 Posts

Posted - 2008-06-11 : 04:12:50

Thanks sodeep

I have 16GB of RAM, No other applications are running on this 64-bt machine. How much I can set for Min and Max memory

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-11 : 13:17:14

Its on Properties of SQL server- Memory .

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-11 : 23:22:52

>> How much I can set for Min and Max memory

You should leave at least 2gb memory for os, so set max memory to 14 gb if it's dedicated sql server.

venkath
Posting Yak Master

202 Posts

Posted - 2008-06-12 : 06:56:19

Thanks rmiao & Sodeep for your info.

I need a final clarification on this

Since We have 64 bit dev edition, We can't go for lock pages in memory setting right? But simply setting min and max memory will eliminate this error?
If yes, Do we need to restart the SQL instance after setting min and max memory?

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-12 : 11:10:18

No .As I told u restarting server will solve temporarily. You need to apply hot fix.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-14 : 17:36:00

You can set lock page in memory for sql account, it's windows stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources