Lock Pages in Memory

Question

Lock Pages in Memory

Krasavita

SSC-Insane

Points: 23138
More actions
December 8, 2009 at 1:38 pm

#218615

When my optimization job runs I have Lock pages in Memory, what is the solution for this?

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the solution to what?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

Krasavita (12/8/2009)
When my optimization job runs I have Lock pages in Memory, what is the solution for this?

Hi,

Read the Jeff Moden article added in my signature.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Krasavita SSC-Insane Points: 23138 More actions · Answer 3

Can I avoid not to have Lock Pages in Memory or what should I set on SQL server box for better permormance?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Why do you want to avoid having it? Your questions are very vague. What is the problem?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

Locking pages is pretty normal for a SQL Server system. It's part of how it maintains data integrity. You really need to drill down on what you think your problem is & provide a lot more detail.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Grant Fritchey (12/9/2009)
Locking pages is pretty normal for a SQL Server system. It's part of how it maintains data integrity.

Hmm, I interpreted this as the security setting 'Lock pages in memory' that prevents Windows from paging out SQL Server memory.

Who knows at this point.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Yeah, it was a guess... I didn't have a clue what they were talking about, but... tried to be helpful. Probably wrong.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Krasavita SSC-Insane Points: 23138 More actions · Answer 8

I was told there is a switch on sql server that needs to be turn on to increase perfomance on sql server

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 9

Krasavita (12/9/2009)
I was told there is a switch on sql server that needs to be turn on to increase perfomance on sql server

Again, a lot more specific information needs to be supplied.

There isn't a "run faster" switch, although I wish there was.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 10

As much as most of us would like a switch like that, as Grant mentioned, there isn't one.

I'd also stop taking advice from whoever told you that.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

If you are referring to a server option for sql server to page memory out, you may want to read the below article. This is not necessarily a go faster switch though.

http://blogs.technet.com/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Krasavita SSC-Insane Points: 23138 More actions · Answer 12

It is a lock page setting in memory, If I turn on, it will increase our performance

GSquared SSC Guru Points: 260824 More actions · Answer 13

Krasavita (12/9/2009)
I was told there is a switch on sql server that needs to be turn on to increase perfomance on sql server

Yes. It's usually located on the front of the server box, with a sort of a circle with a line through part of it, on the switch. If it's turned off, the server won't run very well at all. Turning it on makes all the difference in the world.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 14

The first thing that comes to mind from what you just posted is the ability to pin a table. This means that the table is pinned into memory to elminate the need to read it from disk. It can result in improved performance (it can also mess up the server). Is that what you're looking for?

Or wait, do you mean that you want to enable AWE (Address Windowing Extensions) on a 32bit server? That will extend the memory available to SQL Server up to 64gb, depending on your OS. You do have to set the LOCK PAGE IN MEMORY permission inside the OS to enable AWE. There's an entry in the Books Online on this topic. Maybe that's what you mean?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning