January 3, 2012 at 5:09 am
hi,
i read artical on
Great SQL Server Debates: Lock Pages in Memory
12 December 2011
by Jonathan Kehayias
but i m still confuse to enable or disable this feature in my server.
i m also using 64 bit sql server 2008.
currently it is disable but the process of sql server, using near about 8GB memory.
Please help
:hehe:
January 3, 2012 at 5:13 am
It depends. Seriously there's no one answer, that's why Jonathan wrote that article. Read over it again, weigh up the pros and cons based on what you know of your environment and make a decision based on that.
If you do enable it, make sure you set server max memory to a sensible value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2012 at 5:42 am
Personally, I like to have it enabled as I've previously been stung in the past
January 3, 2012 at 5:43 am
MysteryJimbo (1/3/2012)
Personally, I like to have it enabled as I've previously been stung in the past
Likewise, but it's not an automatic decision.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2012 at 5:54 am
GilaMonster (1/3/2012)
MysteryJimbo (1/3/2012)
Personally, I like to have it enabled as I've previously been stung in the pastLikewise, but it's not an automatic decision.
The problem I see here is, there are two groups of MVPs supporting different opinions (On/Off). It’s very difficult for novices (like me) to choose one. One more interesting thing, if SQL Server’s behaviour is constant (assumed to be) then why we are not able to come up to a conclusion. The discussion ends with diplomatic ‘it depends’ quote.
January 3, 2012 at 5:55 am
ya dev.
i m agreed with u...
January 3, 2012 at 6:02 am
Personally, I've never had any problems with having lock pages in memory configured, but I've reduced the paging quite a bit, and thus got improved the performance of the server. One of the servers I'm using a lot these days is a 4 core server with 8GB of memory, which does have lock pages in memory enabled.
January 3, 2012 at 6:35 am
Dev (1/3/2012)
The problem I see here is, there are two groups of MVPs supporting different opinions (On/Off).
Nothing wrong with that. If everyone was a robot parroting the One True Answer, the community would be that much poorer. They both have valid reasons for their view point. It's up to you to read over them and make a decision based on your environment, your servers, your applications, your usage patterns.
One more interesting thing, if SQL Server’s behaviour is constant (assumed to be) then why we are not able to come up to a conclusion.
SQL's behaviour is consistent, but the environments it's installed in are not. There's huge differences between an instance running on desktop level hardware with a database of maybe a few GB at most, 5 users and 6 other applications on the server, and a multi-terabyte 24x7 mission critical server with a few hundred GB of memory been used by thousands of users.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2012 at 6:37 am
okbangas (1/3/2012)
Personally, I've never had any problems with having lock pages in memory configured
I've had a server crash (and it was a mission critical, etc) because locked pages was enabled without due consideration of the memory limits, other apps, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2012 at 7:10 am
Dev (1/3/2012)
The problem I see here is, there are two groups of MVPs supporting different opinions (On/Off). It’s very difficult for novices (like me) to choose one. One more interesting thing, if SQL Server’s behaviour is constant (assumed to be) then why we are not able to come up to a conclusion. The discussion ends with diplomatic ‘it depends’ quote.
MVPs, like everyone else, have differing levels of technical knowledge in different areas of the product. JK has a very high level of knowledge in the areas discussed in that article, Brent knows a lot about VMs, and Glenn knows his hardware. That's all I'll say there.
For what it's worth, and as someone who had reasonably long discussions with Jonathan when he went through his LPIM-is-no-longer-needed-on-Win2K8R2 phase (happily now passed) I find very little to disagree with him on in that summary article. For me, LPIM is on by default (regardless of installed RAM) with an initially conservative max server memory setting. There would have to be compelling reasons not to use it, and that makes it a good default in my opinion.
That said, any recommendation is dangerous in the hands of a DBA that has really no idea what he or she is doing, so it is vital to understand what LPIM does and does not mean and how important it can be to set max server memory and monitor usage. The article does a good job of that, and comes to the right conclusions, in my opinion.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 10:27 am
i think i have to enable this feature and set max server memory.
January 4, 2012 at 7:36 am
I have it enabled on all of our SQL Servers but set a max memory on all of them as well leaving plenty of memory free on the server. I have not seen any problems at all here. After reading that article I am going to take it on a case by case basis going forward.
January 4, 2012 at 8:02 am
Apart from the general issues in Windows 2003 etc this issue I got in 2008r2 convinced me to configure it manually after taking everything into consideration
http://support.microsoft.com/kb/979149
My symptoms werent 100% in line with this KB as the support engineer said the pages were likely being move in many small batches giving the impression of minor stalls. E.g. I could rerun an identical query 50 times in a row and at random points it would take anything up to 1500% longer before returning its results.
I spent 6 days working with microsoft to fix this issue. I never applied the hotfix in the end as I dont like them, I'd prefer to wait until its bundled. Enabling LPIM fixed the issue. Prior to this server I would have enabled it anyway but as MS said it was not really required anymore I left it off.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply