February 27, 2012 at 10:14 pm
Comments posted to this topic are about the item Configuring Service Account Privileges for SQL Server
February 28, 2012 at 5:58 am
February 28, 2012 at 6:10 am
Hello Ole,
what's about this MSDN post regarding 64-bit operating systems?
"How to: Enable the Lock Pages in Memory Option (Windows)"
SQL Server 2008 R2
The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.
Regards,
Winfried Terlinden
February 28, 2012 at 6:44 am
I've wondered about that with 64-bit systems myself. And having to use the trace flag on Standard edition is a new one on me.
Looks like I've got some SQL services to restart!
Edit: Also; thanks Ole, good article 🙂
February 28, 2012 at 8:54 am
Great Article, I didn't know about the traceflag for standard edition.
February 28, 2012 at 9:40 am
Winfried Terlinden (2/28/2012)
This privilege must be enabled to configure Address Windowing Extensions (AWE). (...) On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.
Hi Winfried.
First of all, yes Lock Pages in Memory may cause issues on 32-bit systems without AWE. As I understand this issue, this is mainly since SQL Server will use and lock as much memory as it can in the first 4GB. With AWE enabled, this memory may be allocated above 4GB, which is less likely to affect other programs. Some have experienced similar issues with Lock Pages in Memory on 64-bit systems if Max Server Memory is not configured.
Lock pages in Memory is not required for allowing 32-bit applications (as SQL Server x86 Editions) to allocate memory above 4GB. It is still required for preventing the OS from paging the buffer pool however.
February 28, 2012 at 2:41 pm
Hello,
Thank you kindly for the great article.
One question. (On a 64bit 2008 R2 Standard Server w/ paging file on much slower disk than data) Lets say the service account is set to lock pages in memory in attempt to avoid page swapping. The hope is that all pages will be read from memory to avoid paging file reads on slower disk. However, if the SQL Server is already under memory pressure and can't offload buffer to paging file could that potentially cause more issue than allowing the slower reads?
I have a prod server that is already under a great deal of memory pressure (causes already known). My concern is that if the instance can't offload to the paging file it might further increase this memory pressure.
Is it that when lock pages in memory is set SQL Server will handle / reuse / free up space more efficiently than if it was off, therefor not actually further increasing memory pressure? Apologies if my question is silly or misguided.
Thank you,
GP
February 28, 2012 at 3:15 pm
First of all, I want to thank everyone for the good feedback.
No matter if Lock Pages in Memory is enabled or not, SQL Server will attempt to release memory on request by the OS. I say attempt, since there is no absolute guarantee that SQL Server will be able to, but normally SQL Server will be able to release memory. As I wrote in a previous comment, some have experienced instability if Lock Pages in memory is used without limiting the buffer pool by setting the Max Server Memory for the instance as well.
In your scenario, you could configure MAX Server memory to leave enough memory for the OS and other applications. For instance, if your Server has 32GB of memory, you may configure 28GB as max server memory, leaving approximately 4GB for the OS and other applications.
February 28, 2012 at 4:46 pm
Last year Nov, our company had a review with Microsoft on SQLRAP results. They highlighted SQL Service account granted "perform volume maintenance tasks" as a risk. I just close my ear and as they blah blah blah about the us impose such strict securities but leaving this security hole. I tried to explain the merit of instant file initialization, but they argued we should've performed more testing, etc. I told them our databases are very large up to 1TB, what'll happen if we need to restore this somewhere, they replied you test it out first and then decide if you really need this functionality.
They might be true but I still reckons the merit outweights the risk.
Wonder if M$ has recommended the same for someone's SQLRAP and someone has actually taken the time to validate M$ claims. I know so many expert recommends granting SQL privs to instant file initialisation, is it really a big security risk as M$ claim?
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008
February 29, 2012 at 12:03 am
First of all, I think the security risk of instant file initialization is a bit misunderstood. Let me try to explain how it works.
On a single instance server, any members of sysadmin may use DBCC PAGE to retrieve raw data which has not been erased. This may be an issue if the server has been reused for other purposes, without wiping the disks, or if a volume holding your data pages has been extended from a shared storage. Note though, the data in this instance is not at risk, it is the data which used to be stored on the same location which is at risk.
On a multi instnace server, it is the same. But in addition, data from a different instance may be at risk as well, as there may be different members of sysadmin on the two instances.
If you have a group of SQL Server sysadmins which controls ALL your SQL Servers, and you don't share storage array with anyone else, I'd say there is no security risk. Otherwise, I'd actually not be very concerned about the secure servers, they're just fine. The problem is the servers with looser security, where syadmins on this server potentially may reveal data which used to be stored on the more secure servers.
I hope this explanation is satisfactory.
February 29, 2012 at 4:49 am
Nice article..
March 8, 2012 at 9:21 am
Great posting. What if SQL Svr is running on a Windows 7 platform?
April 20, 2012 at 1:51 pm
Nice one. Will add to my kitty. "instant file initialization"
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
May 15, 2012 at 6:42 am
okbangas (2/28/2012)
First of all, I want to thank everyone for the good feedback.No matter if Lock Pages in Memory is enabled or not, SQL Server will attempt to release memory on request by the OS. I say attempt, since there is no absolute guarantee that SQL Server will be able to, but normally SQL Server will be able to release memory. As I wrote in a previous comment, some have experienced instability if Lock Pages in memory is used without limiting the buffer pool by setting the Max Server Memory for the instance as well.
In your scenario, you could configure MAX Server memory to leave enough memory for the OS and other applications. For instance, if your Server has 32GB of memory, you may configure 28GB as max server memory, leaving approximately 4GB for the OS and other applications.
I did this at my last client, set MAX Server memory to 28GB however after reboot SQL Server (2005) ignored it. I wanted to try Lock Pages in Memory/AWE but it was high use prod server so never got the chance. First time I'd seem SQL Server ignore a max mem setting.
qh
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply