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."
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.
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.
Tuesday, February 16, 2010 - 5:17:38 AM - dmining06
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?