Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a server that I'm coming onto performance tune and for the life of me I can'g get these rights enabled. So I ran

PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami
nofault\administrator

Then

PS SQLSERVER:\SQL\NOFAULT2010\DEFAULT+sa\> whoami /priv

PRIVILEGES INFORMATION
----------------------

Privilege Name                  Description                               State
=============================== ========================================= ========
SeLockMemoryPrivilege           Lock pages in memory                      Disabled
SeIncreaseQuotaPrivilege        Adjust memory quotas for a process        Enabled
SeSecurityPrivilege             Manage auditing and security log          Disabled
SeTakeOwnershipPrivilege        Take ownership of files or other objects  Disabled
SeLoadDriverPrivilege           Load and unload device drivers            Disabled
SeSystemProfilePrivilege        Profile system performance                Disabled
SeSystemtimePrivilege           Change the system time                    Disabled
SeProfileSingleProcessPrivilege Profile single process                    Disabled
SeIncreaseBasePriorityPrivilege Increase scheduling priority              Disabled
SeCreatePagefilePrivilege       Create a pagefile                         Disabled
SeBackupPrivilege               Back up files and directories             Disabled
SeRestorePrivilege              Restore files and directories             Disabled
SeShutdownPrivilege             Shut down the system                      Enabled
SeDebugPrivilege                Debug programs                            Enabled
SeSystemEnvironmentPrivilege    Modify firmware environment values        Disabled
SeChangeNotifyPrivilege         Bypass traverse checking                  Enabled
SeRemoteShutdownPrivilege       Force shutdown from a remote system       Disabled
SeUndockPrivilege               Remove computer from docking station      Disabled
SeManageVolumePrivilege         Perform volume maintenance tasks          Disabled
SeImpersonatePrivilege          Impersonate a client after authentication Enabled
SeCreateGlobalPrivilege         Create global objects                     Enabled
SeIncreaseWorkingSetPrivilege   Increase a process working set            Disabled
SeTimeZonePrivilege             Change the time zone                      Disabled
SeCreateSymbolicLinkPrivilege   Create symbolic links                     Disabled

No prob I go Local Security > Local Policies > User Rights Assignment grant the LockPages in Memory to Network Administrator and perfom volume maintenance tasks. No Good.

I tried giving the privileges to everyone ( I know bad Idea but just to test ) still no good. And yes I restarted SQL server after each try. Has anyone had any similiar issues with this, real cheap performance gain and I usually have no problem with this.

Thanks

share|improve this question
4  
Why are you enabling lock pages in memory? In most cases this won't do anything to help performance. –  mrdenny Aug 16 '11 at 18:27
1  
I'm assuming nofault is the machine name. And nofault\administrator is the service account of SQL? –  Eric Humphrey - lotsahelp Aug 16 '11 at 19:51
2  
To add to @mrdenny comment: bradmcgehee.com/2011/03/do-you-enable-lock-pages-in-memory Has a good link to Windows Performance Team post on using it. –  Shawn Melton Aug 16 '11 at 19:57
    
@mrdenny it is a noted best practice however I'd be happy to know why you say otherwise. 2Richard 64bit 2008 standard with SQL Enterprise 2008R2 . 2Eric no nofault is the domain ... –  bumble_bee_tuna Aug 16 '11 at 19:59
1  
Lock pages shouldn't be enabled by default, ever. All lock pages does is tell SQL Server to not return memory to Windows when Windows needs it. This can starve the OS for resource hurting performance. It should be enabled if you hit a bug that causes SQL to dump ALL memory until you get a patch that fixes that bug then disable it again. Especially in a virtual environment you should have it disabled as if the balloon driver can't get memory it needs the host can start paging the guests memory. –  mrdenny Aug 16 '11 at 22:43

2 Answers 2

up vote 1 down vote accepted

Try setting the services to run as domain/machine account, and give the permissions to that account

share|improve this answer
    
Yeah that's just what I was thinking Thiago ... I have to wait till off hour to restart but I think that may have been the issue. –  bumble_bee_tuna Aug 16 '11 at 20:06
    
and everyone else ... I have not been able to verify because I can't restart till maintenance window, however I think this was the root of the issue soI will mark this as the answer. Thank you all for the help ... much appreciated. –  bumble_bee_tuna Aug 16 '11 at 20:19

Per here, try rebooting or logging back in:

By default, SeLockMemoryPrivilege is not granted to any user, don't forget that you will have either to reboot your server after granting the privilege if your application is running under system account (Local Service or Network Service) or logoff/logon if your app is running under dedicated account

Edit: a couple more notes

It should be noted that this is only needed on 32-bit versions of Windows (and, only for AWE at that.)

Also, regarding getting it setting this up for SQL Server, you might need to add the -T845 parameter to the startup parameters. (Granted, you were asking about the windows permission, not how it relates to SQL Server.)

share|improve this answer
    
That's the thing I have rebooted to no avail. SQL Server is running as a Local System Account. –  bumble_bee_tuna Aug 16 '11 at 18:52
    
It should be noted that this only works on 32-bit versions of Windows. ??? Really do you have a reliable source for this ? Because I am trying to get this working on a 64 bit 2008 edition ? This would explain it however it seems very odd ... –  bumble_bee_tuna Aug 16 '11 at 19:57
    
Good point. It works, it's just "Not Required". Other sites also say that it's "not needed" Post edited. –  Richard Aug 16 '11 at 20:11
    
Yeah Its not required it just add a major performance improvement to growth of log files and data files ... Granted you should try to avoid this but it happens to the best of us. –  bumble_bee_tuna Aug 16 '11 at 20:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.