“SQL Server has encountered “X” occurrences of I/O requests taking longer than 15 seconds to complete on file “Y”…”.
All of a sudden today, our system has been flooded with this error from one of our clients – so I jumped on to have a look. What I found was a slightly controversial debate on the use of “Local Pages In Memory”.
The error message occurs when the OS recognises that it is running low on memory, so it takes some of the data that SQL Server is holding in the buffer, and writes it to the page file. This can have a negative performance impact on SQL Server, since this data will have to be re-read from disk if queried. Glenn Berry has written a good blog on this, SQL Server and the “Lock pages in memory” Right in Windows Server, that gives provides good background to the debate.
Interestingly, the server that these alerts were coming from was a Windows 2003 Server with SQL Server 2005 installed on it. So in this case, it seems to be almost standard practice to enable the Lock Pages in Memory setting. However, the bottom line is that enabling this setting is not necessarily addressing the root cause of this problem. In this case, there simply is not enough memory to support the OS and SQL Server. So, in this case, I would recommend increasing the RAM to a level that can practically support the level of performance required by SQL Server on this host.