INF: Explanation of Cache Column in Sysobjects & Related Command
ID: Q171387
|
The information in this article applies to:
-
Microsoft SQL Server, version 6.5 Service Pack 3
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.
Additional query words:
SP3 SP
Keywords : kbusage SSrvGen
Version : 6.5
Platform : winnt
Issue type : kbinfo