The following msg appears in the log.  What does it mean?



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): 40820, committed (KB): 91192, memory utilization: 44%.



Server Config:



Windows 2003 Server - Std Edition 64

SQL Server 2005 SP2

32GB Ram

4 Dual Core Proc





I have SQL Server Instance Mem Min\Max set to 8000MB (32GB Ram) and the

above message appears in the log.  Reducing the Min\max to 6500 removes the

message.



Thoughts would be appreciated...

"Covi" <Covi@discussions.microsoft.com> wrote in message

news:2154D445-95C8-4B68-BE65-4083ECBC5A07@microsoft.com...

> The following msg appears in the log.  What does it mean?

>

>    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): 40820, committed (KB): 91192, memory utilization: 44%.

>

> Server Config:

>

>    Windows 2003 Server - Std Edition 64

>    SQL Server 2005 SP2

>    32GB Ram

>    4 Dual Core Proc

>

>

> I have SQL Server Instance Mem Min\Max set to 8000MB (32GB Ram) and the

> above message appears in the log.  Reducing the Min\max to 6500 removes

> the

> message.

>



If the SQL Server Service account has the "lock pages in memory" OS

privilege, then SQL Server will allocate physical memory directly, which is

not subject to being paged out.  If the SQL Service account lacks the "lock

pages in memory" OS privilege, then SQL Server will allocate virtual memory,

which Windows might page to disk in the face of memory pressure by other

applications.



The best practice is to grant "lock pages in memory" and configure SQL

Server max memory to reserve sufficient memory for non-SQL workloads running

on the server.



See

Slava Oks's WebLog

Be Aware: Using AWE, locked pages in memory, on 64 bit

[url]http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx[/url]



David

Thanks David,



The following yields the same message,

Lock Pages In Mem - ON - Service Account

AWE - Off

Mem Min 0

Mem Max 7000MB



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):

40824, committed (KB): 91192, memory utilization: 44%.



Fresh install, no user databases and SQL Server only.



Checked out AWE so to ON.  Still get the message.





"David Browne" wrote:



>

>

> "Covi" <Covi@discussions.microsoft.com> wrote in message

> news:2154D445-95C8-4B68-BE65-4083ECBC5A07@microsoft.com... 

>

> If the SQL Server Service account has the "lock pages in memory" OS

> privilege, then SQL Server will allocate physical memory directly, which is

> not subject to being paged out.  If the SQL Service account lacks the "lock

> pages in memory" OS privilege, then SQL Server will allocate virtual memory,

> which Windows might page to disk in the face of memory pressure by other

> applications.

>

> The best practice is to grant "lock pages in memory" and configure SQL

> Server max memory to reserve sufficient memory for non-SQL workloads running

> on the server.

>

> See

> Slava Oks's WebLog

> Be Aware: Using AWE, locked pages in memory, on 64 bit

> [url]http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx[/url]

>

> David

>

>

>

"Covi" <Covi@discussions.microsoft.com> wrote in message

news:BEE14A1E-E0C8-40DC-AA04-3C75BA240598@microsoft.com...

> Thanks David,

>

> The following yields the same message,

>  Lock Pages In Mem - ON - Service Account

>  AWE - Off

>  Mem Min 0

>  Mem Max 7000MB

>

> 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):

> 40824, committed (KB): 91192, memory utilization: 44%.

>

> Fresh install, no user databases and SQL Server only.

>

> Checked out AWE so to ON.  Still get the message.

>

>



SQL Server Service account has the "lock pages in memory" OS privilege?



In your SQL Server log, near the beginning, do you see an entry that says

"Using locked pages for buffer pool"?



David

Yes,



SQL Server Service Account has 'Lock Pages In Memory' via domain service

account.



"Using locked pages for buffer pool" No message like this...



This message appears though

Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and

5000 Lock Owner blocks per node.





"David Browne" wrote:



>

>

> "Covi" <Covi@discussions.microsoft.com> wrote in message

> news:BEE14A1E-E0C8-40DC-AA04-3C75BA240598@microsoft.com... 

>

> SQL Server Service account has the "lock pages in memory" OS privilege?

>

> In your SQL Server log, near the beginning, do you see an entry that says

> "Using locked pages for buffer pool"?

>

> David

>

>

On Feb 26, 5:13 pm, Covi <C...@discussions.microsoft.com> wrote:

> Yes,

>

> SQLServerService Accounthas'Lock Pages InMemory' via domain service

> account.

>

> "Using locked pages for buffer pool" No message like this...

>

> This message appears though

> Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and

> 5000 Lock Owner blocks per node.

>

>

>

> "David Browne" wrote:

> 

> 

> 

> 

> 

> 

> 

> 

>

> - Show quoted text -



Just an fyi, the "lock pages in memory" permission is ignored in

SQL2005 Std Ed. x64.  I've already gone down this path with MS support

and multiple MS support people have verified this claim.  This can

also be confirmed by looking at the KB article below:  Notice how the

KB article only applies to Ent. Ed. SQL2005.



[url]http://support.microsoft.com/kb/918483[/url]



The only difference now in SP2 is SQL actually tells you it's ignoring

the lock pages in memory feature with the error above.  Prior to SP2

you received no warning, you just had to stumble upon this great

'find' yourself.  As a side note, we've tested queries in a lab

enviornment on identical hardware between SQL2005 Std. and Ent.

editions (both at build 2153) and the query performance difference is

drastic with Ent. Ed. providing a 2-3x performance gain over Std. Ed.

My team and I verified the query plans were identical between

platforms.  When questioned about this drastic performance difference

an engineer with MS said it was due to the lock pages in memory

permission that exists only for Ent. Ed.  Whether that's true or not I

don't know.  I do know that lock pages in memory is ignored in Std.

Ed. and probably provides degraded performance (says so in the error

log).  Hope this helps.



LC

Hi Leoniv,



We are getting this error message on our 32-bit installation.   This article

only seems to relate to 64-bit.  Do you know this 'Lock Pages InMemory'

permission change works on 32-bit?



Also, if this doesn’t work in Standard edition, are we basically just stuck

with this performance degradation?





Thanks

Wendy









"leoniv@hotmail.com" wrote:



> On Feb 26, 5:13 pm, Covi <C...@discussions.microsoft.com> wrote: 

>

> Just an fyi, the "lock pages in memory" permission is ignored in

> SQL2005 Std Ed. x64.  I've already gone down this path with MS support

> and multiple MS support people have verified this claim.  This can

> also be confirmed by looking at the KB article below:  Notice how the

> KB article only applies to Ent. Ed. SQL2005.

>

> [url]http://support.microsoft.com/kb/918483[/url]

>

> The only difference now in SP2 is SQL actually tells you it's ignoring

> the lock pages in memory feature with the error above.  Prior to SP2

> you received no warning, you just had to stumble upon this great

> 'find' yourself.  As a side note, we've tested queries in a lab

> enviornment on identical hardware between SQL2005 Std. and Ent.

> editions (both at build 2153) and the query performance difference is

> drastic with Ent. Ed. providing a 2-3x performance gain over Std. Ed.

> My team and I verified the query plans were identical between

> platforms.  When questioned about this drastic performance difference

> an engineer with MS said it was due to the lock pages in memory

> permission that exists only for Ent. Ed.  Whether that's true or not I

> don't know.  I do know that lock pages in memory is ignored in Std.

> Ed. and probably provides degraded performance (says so in the error

> log).  Hope this helps.

>

> LC

>

>

I am also getting this error on SQL 2005 Std Ed 64-bit SP2.



Does anyone know categorically if it is true that Std Ed ignores the

Lock Pages in Memory permission?