Sql-server – Does SQL Server 2008 R2 lock memory pages by default

memorysql serversql-server-2008sql-server-2008-r2

Does anyone know if the locked pages of SQL 2008 ENT R2 x64 operates as ON as default (I assume it does as my SQL memory usage does not show up in task manager) on Server 2008 R2 x64.

And if so, could this cause a .net app competing for some memory to have excessive page faults?

And therefore is it wise to limit SQLs memory on the machine?

Or, should they all just play nice?

Thanks
Matt

Edit 25/10/2010 –

From ERRORLOG

More info –

I installed my machine to run as administrator service.
I installed it side by side as a new instance with an existing SQL 05 Ent database.

Here is a part of the log –

Large Page Extensions enabled.

2010-10-22 21:32:38.78 Server Large Page Granularity: 2097152

2010-10-22 21:32:38.79 Server Large Page Allocated: 32MB

2010-10-22 21:32:38.79 Server Large Page Allocated: 32MB

2010-10-22 21:32:38.80 Server Using locked pages for buffer pool.

2010-10-22 21:32:38.86 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.

2010-10-22 21:32:38.86 Server Lock partitioning is enabled. This is an informational message only. No user action is required.

I assume this confirms locked pages is on? see comment about administrator below.

Best Answer

The setting is not enabled on the server by default. It would need to be granted to the account running the SQL Service, but this could be done via a GPO automatically.

could this cause a .net app competing for some memory to have excessive page faults?

Yes, if lock pages is enabled it could.

And therefore is it wise to limit SQLs memory on the machine?

Yes, you should always limit the upper limit of memory that SQL Server can use. This is done either by editing the server's properties within the Object Explorer, or by using the sp_configure system stored procedure.

Or, should they all just play nice?

No, the SQL Server will by default use all the memory on the server starving other applications on the system.