Lock pages in Memory

Question

Lock pages in Memory

Rem70Rem

SSCertifiable

Points: 6166
More actions
July 15, 2010 at 2:22 pm

#144006

HI,
I have a Windows Server 2003 R2 64b, running with 16GB of RAM. As of now, on this lovely machine I have SQL 2005 (Microsoft SQL Server 2005 - 9.00.3282.00 (X64) -Build 3790: Service Pack 2) running with a minimum of 6GB and a maximin og 10GB.
Should I lock the page in memory? Will this improve the general performance of my SQL server?
Thanks.

Viewing 15 posts - 1 through 15 (of 18 total)

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

Tara Kizer SSCommitted Points: 1789 More actions · Answer 1

Yes you should. And yes it can and probably will improve performance.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

Hi REM,

Lock pages in Memory will lock the SQL Server pages in the memory and as a result SQL Server pages will not be paged out to the disk. ON 64 bit machine AWE is not required. The only option for the perf boosting is Lock pages in Memory. Of course, the performance gain is observed during heavy server activity.

Thank You,

Best Regards,

SQLBuddy.

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 3

As of now, the user running the SQL process is SYSTEM. Should I apply this policy to this user? Will this be OK?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Hi

I think that you can do that. But I am not sure of the implications.

Best Regards,

SQL Buddy

Tara Kizer SSCommitted Points: 1789 More actions · Answer 5

I've only ever used a domain account for the service when I've used that setting.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog

Leo.Miller SSChampion Points: 13050 More actions · Answer 6

You don't say what edition of SQL Server 2005 you are using, Standards or Enterprise.

Note that if you are using Standard Edition Lock Pages In Memory was only available from SP3 Cumulative Update 4.

Have a look at this article for more details. http://www.sqlservercentral.com/blogs/sqldbauk/archive/2010/06/25/lock-pages-in-memory.aspx

It's good practice to run the SQL Server Service (or most of the SQL related services) under the System account. Best practice is to use a normal domain account, and then allocate permissions as appropriate. I must admit I have no idea what will happen if you allow everything that uses the System account to Lock Pages In Memory. Better to configure the Service account correctly first, make sure you are on the correct SP & CU, then configure LPIM.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

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

Rem-487422 (7/15/2010)
As of now, the user running the SQL process is SYSTEM. Should I apply this policy to this user? Will this be OK?

The Local System account has the 'lock pages in memory' privilege by default. For user accounts, you must grant the account this privilege explicitly.

See http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

I should also mention that even if you are running 2005 SP3 CU4 (or 2008 SP1 CU2) onwards, you will need to enable trace flag 845 as a start-up option, if you are running x64 Standard Edition.

This is the Microsoft Knowledge Base article that covers all that:

http://support.microsoft.com/kb/970070

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Rem70Rem SSCertifiable Points: 6166 More actions · Answer 9

I am running the Standard Edition with SP2. So I need to add the trace flag, right?

Should I stay with SYSTEM as account running the service?

Rem

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

Rem-487422 (7/16/2010)
I am running the Standard Edition with SP2. So I need to add the trace flag, right?

No, you need to upgrade to SP3, then again to Cumulative Update 4 (CU4) for Service Pack 3.

Then enable the trace flag. All the details are there in the links.

Should I stay with SYSTEM as account running the service?

It's not a best practice, but only you have all the information necessary to make that decision.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 11

SQLkiwi (7/16/2010)
I should also mention that even if you are running 2005 SP3 CU4 (or 2008 SP1 CU2) onwards, you will need to enable trace flag 845 as a start-up option, if you are running x64 Standard Edition.
This is the Microsoft Knowledge Base article that covers all that:
http://support.microsoft.com/kb/970070

I am runnning sql 2005 64 bit std edition with Sp3 + CU10 ..do i still need to enable the trace flag?

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

sqldba_icon (1/12/2011)
I am runnning sql 2005 64 bit std edition with Sp3 + CU10 ..do i still need to enable the trace flag?

Yes.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sachnam SSCrazy Points: 2317 More actions · Answer 13

Ideally it should get a seperate Windows domain Account created with limited previleges and then you should grant the lock pages in memory right to that user.

This will help you define the accessibility Limits of your sql server . you should never use a default or any system based user account for Running sql server services. Infact you should get at least 2 domain Account with different set of Rights Granted to them like a) SqlService login b) SqlAgent login

to run your Sql Server Engine and Sql server Agent services respectively using these accounts for better security purpose and of course it helps you some way or the other to sheild your OS from your Sql server.

sachnam SSCrazy Points: 2317 More actions · Answer 14

sqldba_icon (1/12/2011) I am runnning sql 2005 64 bit std edition with Sp3 + CU10 ..do i still need to enable the trace flag?

To Avail the benefits of Lock Pages in Memory in Sql server Standard Edition it is Mandatory to Enable

the Trace Flag 845 REgardless of which Version of sql server you are running.