There are two privileges which in general should be granted to the SQL Server service account, lock pages in memory and perform volume maintenance tasks. Lock pages in memory allows SQL Server to prevent paging of buffered pages, and perform volume maintenance tasks allows SQL Server to use instant file initialization. Before we dig in to the configuration and testing details, let us have a look at how SQL Server’s buffer manager is managing your buffer memory, and how SQL Server is growing your files.

Buffer Memory

As SQL Server is executing a query, the execution engine is requesting data pages from the buffer manager, which manages the pages stored in memory and keeps track of their usage. If the pages already exist in the buffer pool, they are returned immediately to the execution engine, otherwise the data has to be read into the buffer pool from disk first. All pages are stored as long as possible in the buffer pool, but ultimately there is a great chance that some pages will be removed from the buffer pool to make room for other pages. SQL Server uses an algorithm based on usage count and time to determine which pages are less likely to be used again, and based on this removes pages from the buffer pool as needed.

The buffer manager has a limitation though; it cannot know whether pages are in physical or virtual memory. When external processes cause memory pressure, Windows may move some of SQL Server’s memory to the paging file, a process commonly known as swapping or paging. The buffer manager is not aware of this, and will still see the pages that have been moved to the paging files as being in memory. The next time the execution engine request these pages, the buffer manager picks the pages from what it believes is memory, triggering Windows to retrieve them from your paging file. Most commonly, the paging file is stored the system (or boot) drive, which tends to be the servers’ slowest drive. So, when we access a page which has been swapped by Windows, not only do we have to retrieve that page from disk, but there is a great chance that we have to retrieve it from a disk which is slower than our data disk. When this happens, we can experience a severe performance loss.

What we can do, is preventing Windows from moving pages from the buffer pool to the paging file, by granting SQL Server the lock pages in memory privilege. This is supported in Enterprise Edition of SQL Server at least since SQL Server 2000. In addition, support for this has been added Standard Edition for SQL Server 2005 SP3 CU4 (9.00.4226), SQL Server 2008 SP1 CU2 (10.0.2714) and SQL Server 2008 R2 and later, by using trace flag 845.

Data Files

In most configurations, SQL Server is configured to grow the database (and transaction log) files automatically. By default, all allocated disk space is zeroed out to erase any old data which was stored in the same location on the physical disk. For data files, SQL Server uses GAM (Global Allocation Map) pages to indicate whether extents (8 contiguous pages) are allocated or free. Thus, SQL Server does strictly speaking not need to zero out the newly allocated disk space. Log files however do not have a GAM structure, and thus allocated log space will always to be zeroed out before use.

If we grant SQL Server the perform volume maintenance tasks privilege, this will allow SQL Server to use instant file initialization for data files. Instant file initialization is a Windows feature which allows us to allocate disk space without wiping it first. In some cases, for instance if you have a server hosting data for multiple customers, this may be a security issue though. When a data file grows, it may be allocated disk space which previously has been allocated to a different database, possibly even from a different instance on the same server. It may be possible for members of the sysadmin role to disclose data from the previous database by reading the pages with DBCC PAGE. Do not configure SQL Server to use instant file initialization in cases where this risk is unacceptable. If you later turn off instant file initialization, this will not cause already allocated but unused pages to be zeroed out.

Granting Privileges

The privileges described in this article can be configured through Local Security Policy (secpol.msc), or through a group policy. In the latter case, the settings will be found under Computer Configuration | Policies | Windows Settings | Security Settings | Local Policies. In a domain environment, the administrative overhead can be greatly reduced by implementing these settings in a group policy object (GPO). In that case, keep in mind that the GPO is to be applied to the computer accounts for the SQL Servers, and that the privileges must be granted to the service accounts. For simplified administration, it is recommended to create an active directory group for all your SQL Server service accounts, and grant the privilege to this active directory group. If this is a universal group, you may actually use a single GPO across all the domains in your forest.

If you cannot grant privileges to an active directory group due to security policy, you may have to create several GPOs or configure it locally on each server. In the following example, SQL Server is running as NETWORK SERVICE, and we see that both lock pages in memory and perform volume maintenance tasks has been granted to this account:

If you are doing this on a supported version of SQL Server Standard Edition, you’ll need to configure SQL Server to start with trace flag 845 too. This is best done with SQL Server Configuration Tool:

Please ensure to include the semicolon (;) and ensure that there is no space surrounding it. Failing to do so may cause the SQL Server service to fail to start.

Final Testing

Both these settings require a restart of the SQL Server service to take effect. If lock pages in memory is correctly configured, SQL Server will log that it is using locked pages for the buffer pool in the SQL Server Error Log. This can be verified by executing the following command:

exec master.dbo.xp_readerrorlog 0, 1, 'Using Locked Pages For Buffer Pool';

To check whether perform volume maintenance tasks is correctly configured, you can set trace flag 3004 to trace information regarding instant file initialization (or actually lack of it), and 3605 to direct this trace to the SQL Server Error Log. If you create a new database with these two trace flags set, the SQL Server Error Log should show the log file(s) being zeroed out, but not the database files. The following little script does all this magic for you, and tells you whether instant file initialization is being used for data files or not:

dbcc traceon(3004,-1);
dbcc traceon(3605,-1);
create database InstantFileInitialization;
go
drop database InstantFileInitialization;
go
create table #TempLog (
  LogDate datetime,
  ProcessInfo varchar(20),
  [Text] varchar(max)
);
declare
  @Count int;
insert into #TempLog
exec master.dbo.xp_readerrorlog 0, 1, 'InstantFileInitialization';
select @Count=Count(*) from #TempLog where [TEXT] like 'Zeroing %.MDF'
drop table #TempLog
if @Count = 0
select
  case
    when @Count = 0 then 'Using Instant File Initialization for data files'
    else 'Instant File Initialization is not being used for data files'
  end as [Status]
go
dbcc traceoff(3004,3605,-1);