sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

Should You Enable Large Pages on Your SQL Server?

Issue 6 Number 12 December 2016

 

I have answered that question many times. I think the reason I hear it so much is that ‘Large’ sounds better than ‘small’ and my questioner assumes that Larger Pages might improve performance on their SQL Server.

That is a dangerously flawed assumption. The most you can assume is that Large Pages MIGHT improve performance and HOPE that it will not make things worse.

I could just answer “NO” whenever I am asked the question and I would be correct about 90% of the time. There are relatively few SQL Servers that might profit by setting Large Page Allocations. Still I have to cover all cases, however lightly.

This may be a quick read for you because there are three conditions that must be met before you can even think about Large Pages:

  • You must be using SQL Server Enterprise Edition. Standard Edition does not support Large Pages.
  • The computer must have 8 Gb or more of physical RAM
  • The “Lock Pages in Memory” privilege must be set for the service account

OK, Are you still in the game?

How do You Lock Pages in Memory?

To enable the lock pages in memory option

  • On the Start menu, click Run. In the Open box, type gpedit.msc.
  • On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
  • Expand Security Settings, and then expand Local Policies.
  • Select the User Rights Assignment folder.

    The policies will be displayed in the details pane.

  • In the pane, double-click Lock pages in memory.
  • In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
  • In the Select Users, Service Accounts, or Groups dialog box, add an account with privileges to run sqlservr.exe.
  • Log out and then log back in for this change to take effect.

Still There?

In a way I am happy that enabling Large Pages is complicated. If Large Pages could be enabled with a click I think that eager SQL DBAs might enable it on servers that definitely should not have it.

Who Needs Large pages?

Typically, a candidate for Large Pages is heavily loaded, processing thousands of transactions/sec. It will have a bunch of memory, etc.

If you’re still in the game at this point I am going to give you to Bob Ward for the important technical stuff.

Bob Who?

Bob works for Microsoft and, in my experience he seems to know everything. There are plenty of differences of opinion about some aspects of Large Pages, especially about the role and necessity of Trace Flag 834. So, if there is a difference of opinion I would always go with what Bob says.

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

Should You Enable Large Pages on Your SQL Server?

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator