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:
- 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:
- How to reduce paging of buffer pool memory in 64-bit version of SQL Server
- Lock Pages In Memory… Do You Really Need It?
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 🙂