MSSQLSERVER_17890

Applies to: SQL Server

Details

Attribute Value
Product Name SQL Server
Event ID 17890
Event Source MSSQLSERVER
Component SQLEngine
Symbolic Name SRV_WS_TRIMMED
Message Text A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: %d seconds. Working set (KB): %I64d, committed (KB): %I64d, memory utilization: %d%%.

Explanation

You might encounter the following error message in the SQL Server error log or the Windows Application event log.

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 3383250, committed (KB): 9112480, memory utilization: 37%.

You might also notice a sudden performance degradation with query execution and all other operations on the SQL Server.

Cause

SQL Server monitors the various memories related information about the SQL Server process. In this case, it has detected that the working set of the process is less than 50% of the committed process memory. As a result this warning is printed. The normal causes of this warning are:

  • The operating system pages out large portions of the SQL Server committed memory to the paging file.
  • This could be due to sudden increased demand for memory from other applications or operating system needs.
  • This could also happen when certain device drivers request contiguous memory allocations for their needs.

User action

You can prevent the Windows operating system from paging out the buffer pool memory of the SQL Server process by locking the memory that is allocated for the buffer pool in physical memory. You lock the memory by assigning the Lock pages in memory user right to the user account that is used as the startup account of the SQL Server service. But before you implement this solution, review the sections What causes SQL Server memory to be paged out and Important considerations before you assign the "Lock pages in memory" user right for an instance of SQL Server

Note

Using Lock Pages in Memory ensure that the memory managed by SQL Server is not paged out. However, thread stacks, the EXE and any DLL images, heap memory, CLR memory can still be paged out by the OS.

Starting with SQL Server 2008 SP1 Cumulative Update 2, both SQL Server Standard and Enterprise editions can use the Lock pages in memory user right. For more information about support for locked pages, view KB970070 - Support for Locked Pages on SQL Server Standard Edition (64-bit) systems.

To assign the Lock pages in memory user right, follow these steps:

  1. Click Start, click Run, type gpedit.msc, and then click OK.
  2. Note The Group Policy dialog box appears.
  3. Expand Computer Configuration, and then expand Windows Settings.
  4. Expand Security Settings, and then expand Local Policies.
  5. Click User Rights Assignment, and then double-click Lock pages in memory.
  6. In the Local Security Policy Setting dialog box, click Add User or Group.
  7. In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
  8. Close the Group Policy dialog box.
  9. Restart the SQL Server service.

After you assign the Lock pages in memory user right and you restart the SQL Server service, the Windows operating system no longer pages out the buffer pool memory within the SQL Server process. However, the Windows operating system can still page out the nonbuffer pool memory within the SQL Server process.

You can validate that the user right is used by the instance of SQL Server by making sure that the following message is written in the SQL Server Error Log at startup: "Using locked pages for buffer pool"

This message applies only to SQL Server. For more information about this message in the ERRORLOG, visit the following: Do I have to assign the Lock pages for Memory privilege in Local System

When the Windows operating system pages out the nonbuffer pool memory, you may still encounter performance issues. However, the error messages that are mentioned in the "Explanation" section are not logged in the SQL Server error log.

What causes SQL Server memory to be paged out

There are three broad categories of problems that can cause this issue:

  • Application-Related Issues: All applications together have exhausted the available physical memory and the OS must free some memory for new application requests for resources. Typically, the approach here is to find what applications are exhausting the memory and take necessary steps to balance the memory among them without leading to RAM exhaustion.
  • Device Driver Issues: Device Drivers may cause working set paging of all processes if the driver calls a memory allocation function incorrectly.
  • Operation System Issues

Below, you can find information on each of these categories

Important considerations before you assign the "Lock pages in memory" user right

You should make additional considerations before you assign the Lock pages in memory user right. If you assign this user right on systems that are configured incorrectly, the system may become unstable or experience a performance decrease of the whole system. Additionally, event ID 333 may be logged in the event log.

If you contact Microsoft Customer Support Service (CSS) for these problems, CSS engineers may ask you to revoke this user right for the user account that is used as the startup account of the SQL Server service. This step may be necessary to collect important performance data that CSS engineers can use for necessary configuration of the various options for SQL Server and for other applications that are running on the system. After CSS engineers collect the performance data, you can assign the Lock pages in memory user right to the startup account of the SQL Server service.

Before you assign the Lock pages in memory user right, make sure that you capture a Performance Monitor log to determine the memory requirements of various applications and services that are installed on the system. These applications also include SQL Server. To determine the memory requirements, collect the following baseline information:

  • Make sure that you set the max server memory option and the min server memory option correctly. These options reflect only the memory requirement of the buffer pool of the SQL Server process. These options do not include the memory that is allocated for other components within the SQL Server process. These components include the following:

    • The SQL Server worker threads
    • Various DLLs and components that the SQL Server process loads within the address space of the SQL Server process
    • The Backup and restore operations
  • The DLLs and components include various OLE DB providers, extended stored procedures, Microsoft COM objects that are used for the sp_OACreate stored procedure, linked servers, and SQL Server CLR. Memory that is allocated for these components falls under the nonbuffer pool region of the address space of the SQL Server process. To ideally determine the maximum amount of memory that the whole SQL Server process can use, you must subtract the memory that is allocated for components that do not use the buffer pool from the total memory that you want the SQL Server process to use. Then, you can use the remainder value to set the max server memory option. Before you set the max server memory option and the min server memory option, you should carefully review the "Setting the memory options manually" topic in SQL Server Books Online.

  • Determine the memory requirement of other applications and of the Windows operating system components. Applications may include other SQL Server components, for example, SQL Server Agent, SQL Server Replication Agents, SQL Server Reporting Services, SQL Server Analysis Services, SQL Server Integration Services, and SQL Server Full Text Search. Applications that perform Backup operations and file copy operations may use lots of memories. Consider operations such as bulk copy and the Snapshot Agent that generate file IO. You must consider the memory requirement of all these applications when you determine the value of the max server memory option and of the min server memory option. You can use the Private Bytes counter and the Working Set counter under the Process object for every process to determine the memory requirement for a specific process.

  • By default, the Lock pages in memory user right have already been assigned to the built-in Local System account. For more information, visit the following Microsoft Web site: Do I have to assign the Lock pages in Memory privilege for Local system?

  • If you use a Windows user account globally for all SQL Server processes in a domain, determine the user rights that are assigned by using a Group Policy configuration. A 32-bit SQL Server process may use this account as the startup account. However, this account requires the Lock pages in memory user right to enable the Address Windowing Extensions (AWE) feature. For more information, see the "Providing the maximum amount of memory to SQL Server" topic in SQL Server Books Online.

  • Before you configure the max server memory option and the min server memory option for multiple SQL Server instances, consider the memory requirements of the nonbuffer pool for each instance of SQL Server. Then, configure these options for each instance of SQL Server.

Ideally, you collect this baseline information during peak loads. Therefore, you can determine the memory requirements for various applications and components to support the peak load. The memory requirements vary from one system to another system, depending on the activities and the applications that are running on the system. You can query the information that is provided in the dynamic management view sys.dm_os_process_memory to understand whether the system is encountering low memory conditions. For more information, see sys.dm_os_process_memory (Transact-SQL).

Improvements added in Windows Server 2008 and R2 version

Windows Server 2008 and Windows Server 2008 R2 improve the contiguous memory allocation mechanism. This improvement lets Windows Server 2008 and Windows Server 2008 R2 reduce to a certain extent the effects of paging out the working set of applications when new memory requests arrive.

The following is an explanation of the improvements from the Microsoft whitepaper "Advances in Memory Management in Windows":

In Windows Server 2008, the allocation of physically contiguous memory is greatly enhanced. Requests to allocate contiguous memory are much more likely to succeed because the memory manager now dynamically replaces pages, typically without trimming the working set or performing I/O operations. In addition, many more types of pages—such as kernel stacks and file system metadata pages, among others—are now candidates for replacement. Consequently, more contiguous memory is generally available at any given time. In addition, the cost to obtain such allocations is greatly reduced.

For more information, view SQL Server Working Set Trim Problems.

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.