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).
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?
Got the KB Article for this updated (http://support.microsoft.com/kb/970070). The Books Online change will happen in due course of time.
Is the trace flag applicable to Windows 2003 and Windows 2008 (R2)
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
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.
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.