BUG: Using PINTABLE on SYSOBJECTS May Cause Memory Starvation (221978)


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q221978
BUG #: 18617 (SQLBUG_65)

SYMPTOMS

When either DBCC NEWALLOC or DBCC CHECKALLOC is executed on a database in which the sysobjects table has been pinned in memory, many pages from other tables may also become pinned, causing the data cache to become stressed. SQL Server may appear to be slow or to stop responding and the following errors may be reported in the error log:
spid21 Lazywriter: WARNING, LRU list is empty (1 free bufs, 3945 total bufs)

bufwait: timeout, BUF_IO, bp 0xf2c220, pg 0x1049, stat 0x801100/0x6, obj 0, bpss 0xfbe468

WORKAROUND

Do not pin the sysobjects table. Use DBCC UNPINTABLE to mark the sysobjects table as no longer being resident in RAM.

For more information on DBCC PINTABLE and DBCC UNPINTABLE, see the SQL Server Books Online.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5.

MORE INFORMATION

The documentation on the use of DBCC PINTABLE 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 sysobjects table is clearly smaller than the data cache, other pages are pinned in memory, which may then consume a large part of the data cache.

You can use DBCC SQLPERF(LRUSTATS), as well as DBCC SQLPERF(LRUSTATS2) introduced in SQL Server 6.5 Service Pack 2, to monitor the cache usage; for more information, see the SQL Server Books Online (and Service Pack Readme.txt).

You can use the statement below to identify whether a system table has been pinned in the current database.
NOTE: The schema and meaning of columns in system tables may change between product versions. 
select name, id from sysobjects where type = 'S' and sysstat & 0x200 = 0x200
go

The result set will list the names of any system tables that have been pinned and the object ID that should be used in the DBCC UNPINTABLE statement.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB221978
©2003 Microsoft Corporation. All rights reserved.