FIX: Deferred Updates, Triggers and READ UNCOMMITTED May Leak Memory

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

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.

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.

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: 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: For more information, contact your primary support provider.

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.