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:
Matteo is currently a DBA for one of the largest global electronic payment service providers in the U.S. He has been working in IT since he graduated from college in 1993. Matteo has a Master’s degree in Computer Science, and specializes in MSSQL with a good working knowledge of MySQL as well.
- MSSQLTips Awards: Rising Star (50+ tips) – 2021 | Author Contender – 2020
