Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































SQL Server Lazy Writer Issue Warning No Free Buffers Found

MSSQLTips author Matteo Lorini By:   |   Read Comments (5)   |   Related Tips: 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



Last Update: 2/15/2010


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, February 15, 2010 - 7:58:27 AM - --cranfield Read The Tip

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


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

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 - 1:51:55 PM - aspiringgeek Read The Tip

Kalen Delaney et al speak of the so-called Lazy Writer & free buffers in SQL Server 2008 Internals.  Very cool topic.


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

 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


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

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

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.