SQL Server Lazy Writer Issue Warning No Free Buffers Found

By: Matteo Lorini | Updated: 2010-02-15 | Comments (5) | Related: More > Database Administration

Problem

This tip is intended to help someone who is or will be facing a no free buffer found issue with the SQL Server LazyWriter. Recently, we experienced some unknown issues with our SQL Server and after inspecting the error log file we noticed the following errors: "SQL Server has encountered 50 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file..." and "LazyWriter: warning, no free buffers found." These error messages were followed by a SQL Server memory dump file like SQLDump0030.mdmp in the file system.

Solution

The first error (SQL Server has encountered 50 occurrence(s) of I/O requests..) is quite simple to understand. It is basically an indication that the I/O subsystem is not performing as it should. The second error (LazyWriter: warning, no free buffers found) is less intuitive. Unfortunately, the exact meaning of "no free buffers found" warning is harder to figure out.

Since we were receiving these error messages and performance seemed to degrade on our production SQL Server we decided to engage Microsoft Premier Support. To our surprise, our issue was escalated automatically from a level I to a level III support engineer. After the SQL Dump files were analyzed we received an explanation of what was wrong with our environment.

By examining the SQL Dump files, Microsoft support found that SQL Server was running out of Buffer Pool memory. There was not enough memory to handle the data pages for the queries to process these efficiently. At peak or close to peak load, this forced the SQL Server lazy writer to run more often to clean out dirty pages from memory to disk. This in turn led to a higher disk I/O and the reporting of disk I/O processes taking longer than 15 seconds to complete. Eventually, SQL Server ended up with a scenario of a non-yielding IOCP (I/O completion port), because the thread is having to wait a long time for the disk I/O to complete. This is what caused the SQL Server dumps.

The buffer pool memory is for the storage of compiled query plans. In our circumstance, there were many queries with relatively large query plans that were stealing memory from the buffer pool. The longer SQL Server ran, the more memory what was stolen from the buffer pool and the more the lazy writer had to run. Eventually, SQL Server runs out of memory and the lazy writer cannot run. This results in the message "LazyWriter: warning, no free buffers found."

Next Steps

I hope this tip will help someone. I think is interesting to share a real world issue which was debugged and resolved by Microsoft premier Support.
Review the processes running in SQL Server. Then find out which queries are generating large query plans. Finally, figure out how to tune tuning those queries.

Check out these related tips:

About the author

Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2010-02-15

Comments For This Article

Tuesday, February 16, 2010 - 5:19:58 AM - dmining06

SQl was running a 3rd party app. It used only store procedure. Server was set to manage memory dynamically. It was a SQL 2005 32 bits. We have to enable AWE, Grant "Lock Page in Memory" local security policy right to the SQL Server Service account and set the MAX Memory to 4GB. Total server memory was 6GB.

Thanks

Tuesday, February 16, 2010 - 5:17:38 AM - dmining06

It was a SQL 2005 32 bits. We have to enable AWE, Grant "Lock Page in Memory" local security policy right to the SQL Server Service account and set the MAX Memory to 4GB. Total server memory was 6GB.

Was not able to rewrite code because is a thrid party application.

Thanks

Monday, February 15, 2010 - 1:51:55 PM - aspiringgeek	Back To Top (4904)
Kalen Delaney et al speak of the so-called Lazy Writer & free buffers in SQL Server 2008 Internals. Very cool topic.

Monday, February 15, 2010 - 11:53:47 AM - aprato

Which version and service pack of SQL Server did you experience this? Do your applications run a lot of non-prepared, ad-hoc SQL? This can also flood the buffer pool with a plans that may have little chance of being re-used.

I'd be interested in the solution also. If SQL Server was being allowed to manage memory dynamically, then I suspect more memory was added.

Monday, February 15, 2010 - 7:58:27 AM - --cranfield

Hi Matteo. Thank you for writing up this real world problem.

You don't mention what the solution was. You say that there was not enough memory but how did you go about addressing this problem? Did you re-write some code, tune the memory settings or add more memory?

thanks