BUG: Using PINTABLE on SYSOBJECTS May Cause Memory Starvation

• Microsoft SQL Server version 6.5

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 version 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 

Additional query words: errorlog resultset hang hangs hung

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

Last Reviewed: March 12, 1999