Link to home
Start Free TrialLog in
Avatar of Eprs_Admin
Eprs_AdminFlag for Austria

asked on

LPIM on SQL2012R2

Hi Experts,

How to enable LPIM on SQL server 2012R2 ?
Or how to check if its on or off ?
Avatar of ste5an
ste5an
Flag of Germany image

See How to enable the "locked pages" feature in SQL Server 2012.

You need to assign the LPIM user right to the SQL Server account.
Avatar of Eprs_Admin

ASKER

Thanks but this link is just info.
Where to configure it and how ?
At the default place for service accounts:

How to: Enable the Lock Pages in Memory Option (Windows)
ok this is done.
But how to check if its really on and working ?
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok and which value ?
Locked Pages Allocated
ok, on my DB, this value is always 0.
Is it ok ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
we had performance issues and we changed something
first I did:

Read Committed Snapshot:
ALTER DATABASE <DBName> SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE <DBName> SET ALLOW_SNAPSHOT_ISOLATION ON
GO
second I did:

Locked Pages in Memory
https://support.microsoft.com/en-us/kb/2659143 

But I am still not sure if locked pages are active
LPIM is a permission that you need to give to an user. In this case to SQL Server account.

Anyway, if you're facing performance issues how do you know that giving this permission will solve it?
I messured it and now the average wait time is much more shorter, nearly factor 100.

from 200ms in busiy hours to 2 ms
You got those values after enabling LPIM?
Yes LPIM and Commited Snapshots like the TSQL commands before.