Unlocking “Lock Pages in Memory” Option

Consider this scenario; Your production database server is new and with super configuration of 32 CPUs, 64G RAM, SAN Storage, like wow!. SQL Server 2008 R2 64 bit with SP2 running on Windows Server 64 bit Enterprise R2. Database migrated from SQL 2000. You completed the migration process without any fuss. A third party tool for performance monitoring is installed and running without issues. Another third party tool for audit purposes is also installed and running without any issues. Then you come in one morning and see that performance suddenly is slow for no apparent reason, users complaining saying the website is hanging and error returned is connection time out.  OK, time to cut the morning coffee break and get back to business.

It is for scenarios like this that I would consider unlocking the “Lock Pages in Memory” option for Windows Server. This option is turned off by default in Windows and i also read somewhere that not all DBAs automatically turn this on when they are configuring production database servers. This policy is actually granted by default to the local administrative account but can be explicitly granted to other user accounts, in this case your SQL service account.

As i described in my scenario above, the performance degradation suddenly happening in one of our servers can happen when Windows OS will need more physical memory to run applications, thus requesting for more memory from SQL Server. SQL Server, in turn, will grant this request and give some of it’s memory and swap with Windows OS.

To unlock the “Lock Pages in Memory” option, you are instructing SQL Server to refuse Windows’ request for swapping physical memory. But before doing this in your production server, make sure that you have properly configure your server as this can cause critical performance changes once this option is turned on. As with Microsoft best practices for proper configuration make sure you have set the max server memory option and the min server memory option correctly. Also check that there are absolutely no other unnecessary services running in your server. It also helpful if you consult with the development team and verify that the application is indeed memory friendly.

To grant this access to the SQL Service account, use the Windows Group Policy Tool (gpedit.msc) and follow below:

On the Group Policy console,

  • expand Computer Configuration,
  • expand Window Settings,
  • expand Security Settings,
  • and then expand Local Policies. Select User Rights Assignment. You should be in the same location as below:

Image

  • From the right pane where the policies are displayed, look for Lock pages in memory policy.
  • Double click on Lock pages in memory.
  • Click Add User or Group button
  • Add your SQL Service account, the account you use to run sqlservr.exe. Make sure this account is configured to have the least privileges that you need.

For more reference, please check the following:

Also it is best to test this in another staging server somewhere before actually implementing in your production server or else you will get that “It’s that DBA” look for causing other problems 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with thee Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment