THE SQL Server Blog Spot on the Web

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

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.

SQL Server 2008 R2 still requires a trace flag for Lock Pages in Memory

Almost two years ago, I blogged that Lock Pages in Memory was finally available to Standard Edition customers (Enterprise Edition customers had long been deemed smart enough to not abuse this feature).  In addition to applying a cumulative update (2005 SP3 CU4 or 2008 SP1 CU2), in order to take advantage of LPIM, you also had to enable trace flag 845.

Since the trace flag isn't documented for SQL Server 2008 R2, several of us in the community assumed that it was no longer required (since it was introduced before 2008 R2 went RTM, and a trace flag is typically only introduced to change behavior after a product has been released).  We don't expect to see documentation stating that something *isn't* required, just like we don't expect to find a page on the SQL Server site that lists all of the features not supported by SQL Server.

As it turns out, the trace flag is still required for SQL Server 2008 R2.  Hopefully they will update R2's Books Online to reflect this (and Denali's, if it continues to be true for the next version of SQL Server).

 

Published Monday, February 07, 2011 12:32 PM by AaronBertrand

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

 

Twitter Trackbacks for Aaron Bertrand : SQL Server 2008 R2 still requires a trace flag for Lock Pages in Memory [sqlblog.com] on Topsy.com said:

February 7, 2011 1:13 PM
 

Denny Cherry said:

Sadly many standard edition customers have proven themselves as not knowing when to use this trace flag.  Many turn it on by default and some MVPs even are telling people (or at least they have in the past) that it should be turned on for every instance.

February 7, 2011 2:15 PM
 

AaronBertrand said:

True, and I'm not saying that all Standard customers should use it (though I will dispute that only standard customers are capable of doing the wrong thing).  I'm just saying the original decision to give it to Enterprise customers only was flawed.

February 7, 2011 2:24 PM
 

Neil Hambly said:

We have mix of Std & EE versions (SQL 2005 & 2008 / R2)

I also had to "inform" our DBAs of this LPIM & TF845 requirement (for std editions), and how to validate the LPIM was "on" for that instance

I keep a TF chart for each instance and why it is enabled on that instance

February 12, 2011 4:31 PM
 

Vinayaka Holla said:

Is the trace flag applicable to Windows 2003 and Windows 2008 (R2)

February 15, 2011 9:40 AM
 

Amit Banerjee said:

Got the KB Article for this updated (http://support.microsoft.com/kb/970070). The Books Online change will happen in due course of time.

April 7, 2011 10:30 AM
 

ashish said:

I do have one question. Like SQL server 2008 standard edition lock pages in memory is supported from sp1+cu2.Does SQL server 2008 R2 needs the same (sp1+cu2) to get he benefit of lock pages in memory or only sp1 is ok?

May 1, 2012 6:54 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About AaronBertrand

...about me...

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement