3

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.

1
  • Those messages confirm lock pages are enabled
    – SqlACID
    Oct 25, 2010 at 16:01

3 Answers 3

2

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.

4
  • 1
    "No, the SQL Server will by default use all the memory on the server starving other applications on the system" This isn't entirely true as sql will respond to extreme memory pressure. Of couse by that time your server is usually hosed anyway. Also realize that setting sql server max mem does NOT limit the upper bounds of sql server memory. This only configures the buffer memory (see KB321363) and Effects of min and max server memory in BOL.
    – Jim B
    Oct 24, 2010 at 23:41
  • More info, I did install SQL 08 to run as administrator. I have updated my question with more info from error log, it looks like locked pages is on here!? Do you all agree? I did no other config here. I will put a sensible limit of SQL memory (including the overflow indicated by the KBs) and report back on if it helps the page faults on my application. Thank you everyone.
    – WickedW
    Oct 25, 2010 at 10:08
  • Yep, locked pages is enabled based on the output from your error log. Limiting the amount of memory that the SQL Server can use should fix the paging that the other application is doing.
    – mrdenny
    Oct 25, 2010 at 15:42
  • Jim is correct in that the setting only effects the buffer pool. However unless you have something crazy going on the private bytes (MAPI, CLR, extended procedures, etc) shouldn't be very large.
    – mrdenny
    Oct 25, 2010 at 15:45
1

To verify, check the SQL logs from the last startup, if it is not enabled, you'll see:

Cannot use Large Page Extensions: lock memory privilege was not granted.

..and as MrDenny said, SQL does not really play nice with other applications, it assumes it owns the server unless told otherwise..

1
  • Thanks, see error log update, I think it is on. I will see if I can get them to play nice with a sensible limit and report back.
    – WickedW
    Oct 25, 2010 at 10:12
0

According to this article, the option is not active by default: http://msdn.microsoft.com/en-us/library/ms190730.aspx.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .