Give this article a Google +1!
Issue 6 Number 12 December 2016Tweet This Article.
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:
To enable the lock pages in memory option
The policies will be displayed in the details pane.
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.
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.
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.
On this page:
Elsewhere on this site: