Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Performance or memory problems when you run SQL Server 2008 or SQL Server 2005 on systems that have more than 64 logical processors

Performance or memory problems when you run SQL Server 2008 or SQL Server 2005 on systems that have more than 64 logical processors

View products that this article applies to.

Symptoms

Consider the following scenario:
  • You install Microsoft SQL Server 2008 or Microsoft SQL Server 2005.
  • The computer on which SQL Server is installed has more than 64 logical processors.
  • Non-uniform memory access (NUMA) is enabled.
In this scenario, you experience problems that affect performance or memory usage. For example, you experience one or more of the following problems:
  • An error message that resembles one of the following may be logged in the SQL Server Error Log:
    Error message 1
    2013-07-17 05:10:31.02 Server IO Completion Listener (0x27a0) Worker 0x00000000516A41A0 appears to be non-yielding on Node 3. Approx CPU Used: kernel 0 ms, user 14991 ms, Interval: 15001
    Error message 2
    2013-07-17 05:15:02.83 spid707 A time-out occurred while waiting for buffer latch -- type 4, bp 000000008BFEF300, page 1:6908, stat 0x35c0000b, database id: 4, allocation unit Id: 72057594041860096, task 0x000000005166D048 : 0, waittime 300, flags 0x1a, owning task 0x0000000005C4F4C8. Not continuing to wait.
    Error message 3
    2013-07-17 05:44:18.71 spid690 Error: 701, Severity: 17, State: 42.
    2013-07-17 05:44:18.71 spid690 There is insufficient system memory in resource pool 'default' to run this query.
  • The SQL Server stops responding to user queries.
  • Unexplained growth in memory usage occurs in the affected SQL Server installation.
  • Operations that are performed by the LazyWriter background thread do not finish as expected. For example, the following operations may not finish:
    • The current time stamp is not updated. Therefore, all database pages have the same time stamp, and the LazyWriter thread cannot evict any buffers.
    • Buffer pool counters in Performance Monitor (Perfmon.exe) are not updated and may always display "0" (zero).
    • The minimum memory and maximum memory server settings cannot be changed dynamically.
    • Long-running input/output detection does not work as expected.
    • The buffer pool does not switch to a memory steady state, and it keeps "away" buffers.

↑ Back to the top

Cause

These problems may occur because SQL Server 2008 and SQL Server 2005 are not processor group (K-group settings) aware. The principal LazyWriter thread that is created on NUMA node 0 is responsible for many housekeeping activities that are related to the SQL Server memory manager. However the LazyWriter thread may not start if the process is running on a processor group other the first group on computers that have more than 64 logical processors and that also have NUMA configured.

Note Out of Memory (OOM) messages are common when the housekeeping activities that are typically performed by the Lazywriter thread do not occur. This problem can occur even if there is sufficient available free memory. This may cause a "non-yielding IOCP listener" error, latch timeouts, and other issues.

↑ Back to the top

Resolution

SQL Server 2008
To resolve this issue in SQL Server 2008, download and install Microsoft SQL Server 2008 Service Pack 3 (SP3). To obtain the download package, and for more information about SQL Server 2008 SP3, go to the following Microsoft Download Center website:

SQL Server 2008 Service Pack 3

↑ Back to the top

Workaround

SQL Server 2008 and SQL Server 2005
To work around this problem in SQL Server 2008 and SQL Server 2005, disable NUMA. To disable NUMA, enable trace flag 8015 when the SQL Server service starts.

Note After you disable NUMA, you can use all the processors in the system. However, performance may be less than optimal because there will be single LazyWriter/IOCP listener for all processors.
SQL Server 2005
To work around this issue in SQL Server 2005, we recommend that you limit to 64 the number of processors that are available to the operating system. To limit the number of available processors, follow these steps:
  1. Click Start, click Run, type msconfig, and then click OK.
  2. In the System Configuration Utility dialog box, click the BOOT.INI tab.
  3. On the BOOT.INI tab, click Advanced Options.
  4. In the BOOT.INI Advanced Options dialog box, select the /NUMPROC= check box.
  5. In the list that is next to the /NUMPROC= check box, select a value that is less than or equal to 64, and then click OK.
  6. In the System Configuration Utility dialog box, click OK.
  7. Restart the server.

↑ Back to the top

References

For more information about the issues that are fixed in SQL Server 2008 SP3, including the NUMA issue that is described in the "Cause" section, click the article number to view the article in the Microsoft Knowledge Base:

2546951 List of issues that are fixed by SQL Server 2008 Service Pack 3
For information about how to assign SQL Server or SQL Server Agent to a processor group, go to the following MSDN website:

Assigning SQL Server, SQL Agent to a Processor Group (OOM, Hang, Performance Counters Always Zero for Buffer Pool, …)
For more information about NUMA configurations, go to the following MSDN website:

SQL Server: Clarifying The NUMA Configuration Information
For information about NUMA configurations for foreign pages, got to the following MSDN website:

How It Works: SQL Server 2008 NUMA and Foreign Pages

↑ Back to the top

Keywords: kbprb, kbtshoot, kbentirenet, kb

↑ Back to the top

Article Info
Article ID : 2901695
Revision : 3
Created on : 11/20/2019
Published on : 11/20/2019
Exists online : False
Views : 368