- 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.
- An error message that resembles one of the following may be logged in the SQL Server Error Log:
Error message 12013-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 22013-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 32013-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.
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.
SQL Server 2008To 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 and SQL Server 2005To 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 2005To 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:
- Click Start, click Run, type msconfig, and then click OK.
- In the System Configuration Utility dialog box, click the BOOT.INI tab.
- On the BOOT.INI tab, click Advanced Options.
- In the BOOT.INI Advanced Options dialog box, select the /NUMPROC= check box.
- 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.
- In the System Configuration Utility dialog box, click OK.
- Restart the server.
Article ID: 2901695 - Last Review: 28 Oct 2013 - Revision: 1