Two settings that I always enable when I install SQL Server 2005 or 2008 on an x64 production database server are “Lock pages in memory” and “Perform volume maintenance tasks”. These are actually User Rights Assignments in Windows that you grant to the SQL Server Service account in Local Group Policy Editor, which you can get to by typing gpedit.msc at a command prompt.

The “Lock pages in memory” right allows SQL Server to better control when its memory gets paged out to virtual memory, rather than letting the operating system decide when to do it. If you enable this, it is very important to set MaxServerMemory to an appropriate value as I discussed here. Otherwise, SQL Server can take so much RAM that it can cause issues for the operating system. Until recently (Build 2714), this setting only worked with SQL Server 2005/2008 Enterprise Edition, but now it also works with SQL Server 2008 Standard Edition (with Build 2714 or higher). Microsoft is also supposed to allow this setting to work with a future cumulative update of SQL Server 2005 Standard Edition. You have to restart the SQL Server service for this setting to take effect.

The “Perform volume maintenance tasks” right allows Windows to not have to “zero out” files when they are created or extended. This lets you create, grow, and restore databases much, much more quickly. This is extremely important when you are trying to establish a database mirror, or if you are trying to restore a large database, or even if you have to grow a data file by multiple gigabytes. This only works for data files, not log files. There is a very slight security risk in doing this, but I think the risk is very well justified for the benefits you receive. You also have to restart the SQL Server service for this setting to take effect.

Here is how you enable these rights:

Once you have Local Group Policy Editor open, you need to navigate to Computer Configuration, Windows Settings, Security Settings, Local Policies, User Rights Assignment in the left hand tree view. On the right, you will see “Lock pages in memory” and “Perform volume maintenance tasks”

Double-click on the right you want to assign in the list view on the right, then add the SQL Server service account.

If you click on the Explain tab for “Lock pages in memory” (in Windows 7 at least), you will see this somewhat scary explanation:

This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM).

The Explain tab for “Perform volume maintenance tasks” has this equally scary text:

This security setting determines which users and groups can run maintenance tasks on a volume, such as remote defragmentation.

Use caution when assigning this user right. Users with this user right can explore disks and extend files in to memory that contains other data. When the extended files are opened, the user might be able to read and modify the acquired data.

Don’t let these scary warnings discourage you from enabling these two settings.