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.
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 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.