Big News : Lock Pages in Memory for Standard Edition
After much feedback from customers (and some loud feedback from MVPs, led primarily by Maciej Pilecki, Microsoft's Bob Ward just announced at the European PASS Conference that they are going to allow the 'lock pages in memory' privilege for all the lowly peons running Standard Edition. Currently this has only been allowed on Enterprise Edition, even though it was probably needed more often on Standard Edition. Microsoft was touting this as an enterprise, high availability feature when, for all practical purposes, it is a stability feature. Bob says this will be a trace flag and will debut in SQL Server 2008 SP1 CU2 (May) and SQL Server 2005 SP3 CU4 (June). His official blog post about the announcement is here:
https://docs.microsoft.com/en-us/archive/blogs/psssql/sql-server-locked-pages-and-standard-sku
Someone pointed out that Books Online insists that locking pages in memory is not required when running 64-bit, however field experience suggests otherwise. Bob says that there will be a KB article with more details on when and how to utilize the trace flag, and hopefully x86 vs. x64 will be addressed at that time (or preferably earlier).
Bernd,
It is still important to set max server memory, even on a dedicated x64 / Itanium SQL Server. There is no question that it needs setting where SQL Server shares the box with other applications.
That goes double for any sort of multiple-instance or clustered configuration.
Slava Oks' weblog has everything you need to know and more, for example see http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx for a reasoning on the above statement.
I guess the question is, why would you *not* set lock pages in memory and appropriate min/max memory levels…?
Cheers!
PS the text at the supplied link, verbatim:
"Locking pages in memory is not required on 64-bit operating systems."
Bernd, the deal is that a lot of people (and a lot of the people using Standard) are not running dedicated database servers, but rather running SQL Server *and* other resource intensive apps on the same server (most notably SharePoint and IIS).
I think the "not required on 64bit" is regarding the AWE extension. The Lock Memory has multiple uses. From making a secure memory up to AWE windows as well as improving RT performance of certain apps.
I am not sure what the deal with SQL server is. If it is running alone on a host one could expect its cache to be paged in all the time?
Bernd
Sure. I was just responding to your previous comments which sounded quite authoritative. I assumed you had 'inside information'.
Enterprise customers do not need a safety net (at least according to MS) because those customers "know what they are doing" and already have an existing support relationship. (My answer to that was, there are plenty of stupid people with money to burn on Enterprise Edition… it is not always because they need it and know how to use it.)
Developer / Evaluation edition customers are not going to chew up support time under existing contracts; instead they will be paying per incident.
Anyway, I am just trying to speculate what might be the reason. You'll have to hit them up directly if you want to argue / debate it. 🙂
Aaron,
Yes I see – but my point was that Lock Pages In Memory is not in the server's process token by default.
It's also odd that Enterprise/Developer/Trial Edition users seem not to require such a 'safety net'…?
Hey, it's not a big thing by any means – it's still all good news – my brain just stuttered slightly when I saw 'trace flag'.
Anyone with a decent amount of memory on 32-bit will have enabled AWE anyway (which will have focussed minds on the max server memory setting already!)
Anyone else would have to explicitly add the Lock Memory right (not even Administrator accounts have Lock Pages In Memory by default).
Having done that without considering min/max memory properly, I don't see many people having second thoughts about turning the trace flag on too…?
Paul
It's a trace flag because they don't want to make it easy for every single Standard Edition user to just turn it on without a second thought. (A trace flag isn't all that hard to turn on, but I certainly think about it a lot more than most other settings.) Their fear is likely that this will raise support calls because there are potentially disastrous side effects to using the feature when you are running other memory consuming applications besides SQL Server.
That is fantastic news. It surprises me a little that a trace flag is necessary. The lock pages in memory right can always be removed from the service account instead…? Awesome stuff though, and good on you Microsoft. Now, if we could just have intellisense back for 2K5 servers… :c)
Paul
WOW!
There was some definite emotional pleas out there to do this. I think in the end, MSFT made the right move to do the right thing.
That is GREAT news
Now I wait for people to produce performance test results
Would one say it's ALMOST always better to ENABLE it regardless?
This is great news. I am glad that Microsoft changed their mind on this. I am glad that Maciej pushed this at the MVP Summit.