FIX: Pintable, NOLOCK, and JOIN Cause LRU/LazyWriter Errors

• Microsoft SQL Server version 6.5

SYMPTOMS

Using the sp_tableoption system stored procedure or DBCC PINTABLE to pin a table in memory can incorrectly lead to LRU and LazyWriter problems if the table is used in a JOIN query and the NOLOCK optimizer hint is also included.

The following warnings may be recorded in the SQL Server error log:

• Lazywriter: WARNING, LRU list is empty (766 free bufs, 6769 total bufs)



• Lazywriter: WARNING, No free buffers found within the last 5 minutes.

WORKAROUND

To work around this problem, run the sp_tableoption system stored procedure and set the pintable option to false.

For more information on sp_tableoption or DBCC PINTABLE, see the SQL Server Books Online.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

The documentation on the use of sp_tableoption with the pintable option states that if the table exceeds the data cache size it can lead to data cache depletion. The symptoms that occur with this bug indicate that even though the table is clearly smaller than data cache, WARNING messages are still written in the error log.

Additional query words: sp sp5prodsql errorlog

Keywords          : SSrvGen SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: March 31, 1999