SQLServerCentral / Article Discussions / Discuss content posted by Amit Kumar Singh / Article Discussions by Author / Poor Man's Enterprise Feature - Lock Pages in Memory / Latest Posts

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

brown-415622 — Sun, 26 Sep 2010 12:26:44 GMT

Thanks for your help, it worked.

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Paul White — Sat, 25 Sep 2010 18:46:51 GMT

[quote][b]brown-415622 (9/25/2010)[/b][hr]I have read all the documentation my issue is I'm not sure where to put the -T845 parameter at startup.Thanks[/quote]Well excuse me! You didn't give that impression by quoting the wrong number :-PIt goes at the end.edit: exactly as shown in the picture in the link I gave: [url]http://blogs.msdn.com/b/psssql/archive/2010/02/19/did-you-start-your-sql-server-engine-correctly.aspx[/url]:doze:

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

brown-415622 — Sat, 25 Sep 2010 18:44:42 GMT

I have read all the documentation my issue is I'm not sure where to put the -T845 parameter at startup. -ll:\masterdb\mastlog.ldf;-el:\LOG\ERRORLOG;-dl:\masterdb\master.mdfDoes it go at the begining? I keep getting an error when restart SQL service.running 64 bit standard 2005 with cumm patch 11.Thanks

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Paul White — Sat, 25 Sep 2010 17:46:37 GMT

[quote][b]brown-415622 (9/25/2010)[/b][hr]When adding the -T846 where exactly do add the parameter at the very begining? Can you please show an example, it would be well appreciated.[/quote]The trace flag is [b]845[/b] not 846.You need 2005 64-bit Standard at least SP3 + Cumulative Update 4, or 2008 64-bit Standard at least SP1 + Cumulative Update 2:[url]http://support.microsoft.com/kb/970070[/url]Here is a link showing how to set it as a start-up option:[url]http://blogs.msdn.com/b/psssql/archive/2010/02/19/did-you-start-your-sql-server-engine-correctly.aspx[/url]How to know your system is now using Locked Pages successfully:[url]http://blogs.msdn.com/b/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx[/url]How to configure start-up options (general info):[url]http://msdn.microsoft.com/en-us/library/ms345416.aspx[/url]Using start-up options:[url]http://msdn.microsoft.com/en-us/library/ms190737.aspx[/url]

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

brown-415622 — Sat, 25 Sep 2010 16:34:14 GMT

When adding the -T846 where exactly do add the parameter at the very begining? Can you please show an example, it would be well appreciated.Thank you

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Paul White — Mon, 20 Sep 2010 11:07:11 GMT

[quote][b]Charles Hottle (9/20/2010)[/b][hr]Is this update required for an environment running SQL Server 2008 SP1 Standard 32-bit using AWE and PAE in order for Lock Pages in Memory to be functional? I only see 64-bit referenced in the various posts that I've seen. Thanks.[/quote]No the update is for 64-bit only.See [url]http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx[/url]

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Chuck Hottle — Mon, 20 Sep 2010 10:59:44 GMT

Is this update required for an environment running SQL Server 2008 SP1 Standard 32-bit using AWE and PAE in order for Lock Pages in Memory to be functional? I only see 64-bit referenced in the various posts that I've seen. Thanks.

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Kristy Lanter — Wed, 28 Oct 2009 07:26:14 GMT

Just researching issues we have that really are not related to the article but came across this article and got me thinking. I was able to lock the issue down to a peice of code that when submitted locked the floor down (casino) and disabled it. Things are good now, just need to fix that code. Good article though.

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Paul White — Tue, 27 Oct 2009 15:25:16 GMT

[quote][b]Kristy Lanter (10/27/2009)[/b][hr]Yeah, but what I was wondering was if it was really needed. Back a few years ago I read someplace it wasn't necessary to set it up but I'm doing it anyhow. Just recently experiencing I/O messages. Thanks.[/quote]Personally, I always enable it. You don't say what the I/O messages say...?This article was about lock pages in memory on Standard Edition and the message that can appear to say that a significant part of SQL Server's allocated memory has been paged out. Is that what you're seeing?

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Kristy Lanter — Tue, 27 Oct 2009 15:19:29 GMT

Yeah, but what I was wondering was if it was really needed. Back a few years ago I read someplace it wasn't necessary to set it up but I'm doing it anyhow. Just recently experiencing I/O messages. Thanks.

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Paul White — Tue, 27 Oct 2009 14:53:18 GMT

[quote][b]Kristy Lanter (10/27/2009)[/b][hr]How about Enterprise edition 64 bit?[/quote]Enterprise Edition has always had the ability to lock pages in memory.

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Kristy Lanter — Tue, 27 Oct 2009 08:25:21 GMT

How about Enterprise edition 64 bit?

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Paul White — Fri, 16 Oct 2009 17:42:07 GMT

[quote][b]Jeff Moden (10/16/2009)[/b][hr]Heh... wouldn't a reasonable alternative be to only have SQL Server on the SQL Server box? ;-)[/quote]On the face of it, yes. And, most of the time, this will work well. However, you are then at the mercy of Window's memory manager - any sudden requests for memory from any other process are likely to result in some of SQL Server's address space being paged out. It is an inviolable law of the universe that it will occur at the most inconvenient time.There are many things that can cause sudden demands for memory from Windows, even if you lock the server down and carefully control who has access and what processes can run. Not being able to lock memory for SQL Server on 64-bit Standard Edition just introduced a whole new set of avoidable problems.Paul

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

alen teplitsky — Fri, 16 Oct 2009 13:32:57 GMT

few years ago Emulex and Windows 2003 x64 SP2 had an I/O bug and it caused one of our servers to lock up and reboot. MS released a series of hotfixes and everyone from Emulex to HP released updated drivers for HBA's and RAID controllers

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

GilaMonster — Fri, 16 Oct 2009 13:19:57 GMT

[quote][b]Jeff Moden (10/16/2009)[/b][hr]Heh... wouldn't a reasonable alternative be to only have SQL Server on the SQL Server box? ;-)[/quote]Unfortunately not. I've heard of a case where SQL was the only thing installed on the box (other than OS) and and it was still getting paged out if max memory was set about about 60% of the total memory. This is a server with >16GB.Suspicion was a driver bug, but couldn't be sure.

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

alen teplitsky — Fri, 16 Oct 2009 13:14:48 GMT

we always install the HP management software, anti-virus, Emulex HBA Anywhere. The HP software also installs a light version of Apache or Tomcat, forget which one. a lot of people will also install SSIS and other SQL components other than the database engine as well as other database vendors' drivers

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Jeff Moden — Fri, 16 Oct 2009 13:09:44 GMT

Heh... wouldn't a reasonable alternative be to only have SQL Server on the SQL Server box? ;-)

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Paul White — Fri, 16 Oct 2009 09:45:14 GMT

[quote][b]ta.bu.shi.da.yu (10/15/2009)[/b][hr]All the Microsoft links point to info about the 64-bit versions of Windows. Is this feature restricted to this platform?[/quote]The problem only affects 64-bit Standard Edition.In 32-bit Standard Edition, we could grant 'lock pages in memory' and enable AWE to lock buffer pool pages.In 64-bit Standard Edition, prior to SP3 CU4 (2005) or SP1 CU2 (2008), there was no way to achieve the same effect.This was a shocking omission by Microsoft - it is a shame it has taken them so long to correct it. Using a trace flag to enable it seems overly conservative: a trace flag to disable it would seem more sensible, but never mind.Not sure why anyone should regard this as more useful or necessary on 64-bit systems (which tend to have more memory, if anything) compared to 32-bit systems, but hey.

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

wasntme — Thu, 15 Oct 2009 14:59:48 GMT

according to microsoft this is supprtedin standard 32 bit edition prior to sp3cu4i have seen the errors you get in the log when you need to add the lock pages in mem policy to the id running sql server and adding stopped the error messages. this is for 32 biut standard edition. i am not so sure I believe any of this blog.Locked pages in memory operating system (OS) privilege (Allows locking physical memory, preventing OS paging of the locked memory.)4 SQL Server Standard, Enterprise, and Developer editions: Required for SQL Server process to use AWE mechanism. Memory allocated through AWE mechanism cannot be paged out.Granting this privilege without enabling AWE has no effect on the server. http://technet.microsoft.com/en-us/library/ms187499(SQL.90).aspx

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

george sibbald — Thu, 15 Oct 2009 09:44:30 GMT

the definitive blog on this:[url]http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx[/url]which has a link to blog Gail posted..................last line added by edit

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

GilaMonster — Thu, 15 Oct 2009 09:37:25 GMT

[quote][b]Steve Vassallo (10/15/2009)[/b][hr]What about SQL 2008? Is this an option there?[/quote]Yes. SQL 2008 SP1 CU2 allows locked pages on standard edition. Good reference for this topic is the PSS Engineers' blog[url]http://blogs.msdn.com/psssql/archive/2009/05/19/an-update-for-standard-sku-support-for-locked-pages.aspx[/url]

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

alen teplitsky — Thu, 15 Oct 2009 09:34:26 GMT

we had this when we first went to x64 and there was almost nothing on the internet to document the differences except a MSDN blog post by a Microsoft developer who now works in another department.we ended up enabling it and all the problems went away. Funny thing is that out of our 3 main servers we only saw the problem on one of them. even on the server that does a lot of huge batch processing we didn't see this. the problem server was tens of millions of queries daily all with different parameters and variables

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

Steve Vassallo — Thu, 15 Oct 2009 09:21:41 GMT

What about SQL 2008? Is this an option there?

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

GilaMonster — Thu, 15 Oct 2009 06:16:09 GMT

RE: Poor Man's Enterprise Feature - Lock Pages in Memory

ta.bu.shi.da.yu — Thu, 15 Oct 2009 06:00:52 GMT

All the Microsoft links point to info about the 64-bit versions of Windows. Is this feature restricted to this platform?

Poor Man's Enterprise Feature - Lock Pages in Memory

Amit Singh — Wed, 14 Oct 2009 21:06:02 GMT

Comments posted to this topic are about the item [B]Poor Man's Enterprise Feature - Lock Pages in Memory[/B]