Lock pages in Memory

Question

Post reply

Lock pages in Memory

pshaship

SSCertifiable

Points: 6125
More actions
March 20, 2010 at 2:45 pm

#156805

Hi,
We have a 2 node a/p cluster setup and I have enabled lock pages in memory on both nodes and restarted the SQL server services. We have 16 GB RAM & set Max memory to 12GB & Min memory to 0 (default value)
I have noticed the below messages in error log.
Large Page Extensions enabled.
2010-03-13 12:11:39.28 Server Large Page Granularity: 2097152
2010-03-13 12:11:39.28 Server Large Page Allocated: 32MB
2010-03-13 12:11:39.33 Server Using locked pages for buffer pool.
2010-03-13 12:11:39.38 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
Could you please explain me what exactly the above message referring to
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

pshaship (3/20/2010)
Large Page Extensions enabled.
Large Page Granularity: 2097152
Large Page Allocated: 32MB

See http://blogs.msdn.com/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx

Using locked pages for buffer pool.

Lock pages in memory is working!

Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

Totally normal. SQL Server is managing memory for locks dynamically.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 2

SQLkiwi,

What if I only can see:

Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

Does that mean that lock pages in memory works? It doesnt I guess.. What if trace flag 845 is not enabled but you use as a startup parameter? Any difference?

How do I make sure that lock pages in memory is working? thanks.

yeah, I know it's a 1 yr old thread..

__________________________
Allzu viel ist ungesund...

SQL Rob Say Hey Kid Points: 698 More actions · Answer 3

Hi Mr Holio,

I know your question is a year old on a thread that's 2 years old, but it still came up in my search so I thought I should answer it. After 45 minutes or so of googling this I figured I might as well just actually test it out so spun up a default instance(SQL 2008 R2 developer).

After adding the SQL Server service account to the lock pages in memory policy in secpol.msc I got the message you mention:

Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

But not the desired message:

Using locked pages for buffer pool.

To get that you still need to add the trace flag -T845. Adding the trace flag and restarting SQL server both messages appear in the startup messages of the error log.