THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help

Home

Blogs

Downloads

Opml

Aaron Bertrand

Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.

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:

http://blogs.msdn.com/psssql/archive/2009/04/24/sql-server-locked-pages-and-standard-sku.aspx

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

Published Friday, April 24, 2009 9:07 AM by AaronBertrand

Filed under: SQL Server 2008, SQL Server 2005, trace flags, lock pages in memory

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

	Glenn Berry said: 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. April 24, 2009 12:24 PM
	jerryhung said: 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? April 24, 2009 1:37 PM
	RickHeiges said: 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. April 24, 2009 3:47 PM
	Paul White said: 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 April 25, 2009 6:02 AM
	AaronBertrand said: 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. April 25, 2009 11:17 AM
	Paul White said: 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 April 25, 2009 11:53 AM
	AaronBertrand said: 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. :-) April 25, 2009 12:03 PM
	Paul White said: Sure. I was just responding to your previous comments which sounded quite authoritative. I assumed you had 'inside information'. April 25, 2009 12:24 PM
	Bernd Eckenfels said: 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 April 25, 2009 5:55 PM
	AaronBertrand said: 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). April 25, 2009 6:00 PM
	AaronBertrand said: PS the text at the supplied link, verbatim: "Locking pages in memory is not required on 64-bit operating systems." April 25, 2009 6:04 PM
	Paul White said: 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! April 25, 2009 9:28 PM
	Log Buffer #144: a Carnival of the Vanities for DBAs \| Pythian Group Blog said: PingBack from http://www.pythian.com/news/2292/log-buffer-144-a-carnival-of-the-vanities-for-dbas May 1, 2009 12:19 PM
	Aaron Bertrand said: It seems this came out on April 20th, but I did not catch it at the time. Better late than never, I guess. May 16, 2009 3:13 PM
	The Rambling DBA: Jonathan Kehayias said: SQL Server users love the new features inside of the platform and each new release brings more exciting June 21, 2010 10:32 PM
	Aaron Bertrand said: Almost two years ago, I blogged that Lock Pages in Memory was finally available to Standard Edition customers February 7, 2011 11:41 AM

Name (required)
Your URL (optional)
Comments (required)
Remember Me?
Submit

About AaronBertrand

...about me...

Aaron Bertrand

Big News : Lock Pages in Memory for Standard Edition

Comment Notification

Comments

Glenn Berry said:

jerryhung said:

RickHeiges said:

Paul White said:

AaronBertrand said:

Paul White said:

AaronBertrand said:

Paul White said:

Bernd Eckenfels said:

AaronBertrand said:

AaronBertrand said:

Paul White said:

Log Buffer #144: a Carnival of the Vanities for DBAs | Pythian Group Blog said:

Aaron Bertrand said:

The Rambling DBA: Jonathan Kehayias said:

Aaron Bertrand said:

Leave a Comment

About AaronBertrand

This Blog

Syndication

Recent Posts

Tags

Archives