INF: Explanation of Cache Column in Sysobjects & Related Command (171387)


The information in this article applies to:

  • Microsoft SQL Server 6.5 Service Pack 3 and later
This article was previously published under Q171387

SUMMARY

Starting with SQL Server 6.5 Service Pack 3, the cache column in the sysobjects system table can be used to indirectly control the cache hit ratio on a per-table basis. This is an advanced configuration technique, and it is not necessary to achieve good performance. It is strongly recommended that sysobjects.cache not be adjusted except in highly controlled situations where a methodical series of tests are run to carefully evaluate the effects. In addition, the current and projected locality of reference for a given table should be well understood before using this technique.

MORE INFORMATION

Specifying a non-zero value for sysobjects.cache may, under some circumstances, improve cache performance of tables that are accessed non-uniformly. If a non-zero value is specified for this table, any page that is referenced at least that many times is given an extra trip through the cache least recently used (LRU) chain (and the reference count is changed to zero).

This is similar to the effect of trace flag 1081, except trace flag 1081 applies to index pages of all tables and the reference-count threshold is one (that is, a single reference is enough to grant an extra trip), whereas reference-count favoring applies to all pages of specified tables. If a reference-count threshold is specified for a table, it overrides the effect of -T1081, except for the root index page. All other index pages for that table must be referenced the specified number of times to receive an extra trip through the cache.

If sysobjects.cache is set too low for a table, too many pages will receive an extra trip, and no benefit will be realized. The same is true if all pages of a table are referenced approximately the same number of times in an interval. On the other hand, if fewer than half of the pages are referenced significantly more often than the others, setting the threshold correctly may improve the overall cache hit ratio. The "DBCC SQLPERF(tabstats,<dbid>)" statement displays the average reference count ("hit count") and the number of pages that will get a second trip through the cache ("favored pages") on a per-table basis. Set the reference count threshold slightly above the average reference count, and note the effect of that change on the number of 'favored' pages versus the total number of pages of that table in the cache, as well as on the per-table cache-hit ratio (also displayed by tabstats). Changes to the reference-count threshold do not take place immediately; you must shut down and restart SQL Server. Change the reference-count threshold by setting the "allow updates to system tables" sp_configure option and using the SQL Server UPDATE statement (you must execute the UPDATE statement in the database in which the table resides). For example, use the following command: 
   update sysobjects set cache=6 where id=object_id('customer')

The SQL Server 6.5 cache algorithm can be considered a derivative of the 'clock' algorithm in which the rate at which the hands of the clock sweep is equal to the rate that free buffers are generated by the lazywriter, and the distance between the hands of the clock is the size of the cache. If a page is referenced at least as many times as the specified threshold between the time it is touched by the two hands, it will remain in the cache forever. That time can be determined by dividing the number of pages on the LRU chain by the number of lazywrites plus clean steals per second. All three of those values are supplied by the "DBCC SQLPERF(lrustats2)" statement.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbinfo kbusage KB171387
©2003 Microsoft Corporation. All rights reserved.