FIX: Deferred Updates, Triggers and READ UNCOMMITTED May Leak Memory (237686)
The information in this article applies to:
This article was previously published under Q237686 BUG #: 18793 (SQLBUG_65) SYMPTOMS Triggers on tables that have deferred updates performed
while at the READ UNCOMMITTED isolation level may cause a leak of a page out of the SQL Server
cache. As the operation is repeated and cache is depleted, it leads to poor
performance and excessive lazywriter activity. You may eventually see the
following message in the errorlog: 1999/07/12
09:46:04.12 spid2 Lazywriter: WARNING, LRU list is empty (409 free bufs, 3267
total bufs) 1999/07/12 09:47:06.81 spid2 Lazywriter: WARNING, LRU list is empty
(0 free bufs, 3267 total bufs) To experience the leak, ALL the following conditions must be met:
- A deferred update is performed that results in an index
modification.
- The trigger is loaded in cache by a connection at the READ UNCOMMITTED isolation level. Other connections at different isolation levels
may also reference the cached trigger and can also cause the leak.
- The trigger references the inserted or deleted tables.
- The trigger includes a query with IF EXISTS, IF NOT EXISTS, or a subquery.
- Database modifications are sufficiently slow so that the
query against the inserted or deleted tables reads the page that is currently the end of the
transaction log.
CAUSE When the trigger is brought into cache while at the READ COMMITTED isolation level, the inserted and deleted tables are set up to
read without locking. When this is done, a copy of the log page is actually
made to avoid issues with data changing while the page is being read. If this
page is the end of the log, it is not put back on the LRU list for reuse by
other processes. WORKAROUND Do not use the READ UNCOMMITTED isolation level when performing modifications that would load the
trigger, or avoid use of subqueries or IF [NOT] EXISTS in the trigger. Many subqueries can be expressed as joins, which
are often more efficient. For the EXISTS query, consider performing the
operation in two steps, first obtaining a count of the qualifying rows, then
using a simple IF statement to conditionally perform the desired action.
STATUS Microsoft has confirmed this to be a problem in SQL Server
version 6.5. This problem has been corrected in the Post Service Pack 5a Update
for Microsoft SQL Server version 6.5. To install the Post Service Pack 5a
Update, you must have either SQL Server 6.5 SP5 or SP5a installed.
For information about how to download and install the SQL Server 6.5 Service
Pack 5a, refer to the following article in the Microsoft Knowledge Base: 197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
If you already have SQL Server 6.5 SP5 or SP5a installed, you can
download the Post SP5a Update from the following article in the Microsoft
Knowledge Base: 274036 INF: How to Obtain SQL Server 6.5 Post Service Pack 5a Update
For more information, contact your primary support provider.
MORE INFORMATION Lazywriter activity can be monitored by using Performance
Monitor. Under the SQL Server object, watch IO - Lazy writes/sec. If you see a
steady increase in this particular counter, it indicates that the lazywriter is
having to work harder to maintain the specified 'free buffers' configuration
value.
SQL Server Service Pack 2 also introduced a DBCC SQLPERF(LRUSTATS2) command that can be used to monitor cache effectiveness. The LRU
pages counter in this output can be used to monitor the number of pages on the
LRU list. When SQL Server first starts, this value will be low, and will
increase as the cache is slowly filled. Once the cache is filled and the server
reaches a steady state, the LRU pages number should remain fairly consistent.
If you should see a consistent decrease in this value over the course of time,
it may be indicative of some type of buffer leak. See the Readme.txt file in
the Service Pack for more details about this command.
Note that use
of DBCC PINTABLE may also cause the same behavior, as this command forces the
server to keep any page in cache once it has been used. DBCC PINTABLE should not be used if the table size is greater than the
configured SQL Server memory.
Modification Type: | Major | Last Reviewed: | 9/6/2006 |
---|
Keywords: | kbBug kbfix KB237686 |
---|
|