SQL Server DBA

SQL Server & Lock Pages In Memory

“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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s