Which one is useless? sa_flush_cache, CacheRead, CacheHits, or me?

[Breck_Carter]

In my experience the CacheRead and CacheHits property values are always (effectively) the same, even when the cache has been flushed, and this has been true for all versions extending from 12 back to (at least) 8. Here's a test in V12...

CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
CALL sa_flush_cache();
SELECT COUNT(*) FROM SYSCOLUMN CROSS JOIN SYSOBJECT;
SELECT DB_PROPERTY ( 'CacheRead' ), DB_PROPERTY ( 'CacheHits' );

DB_PROPERTY('CacheRead'),DB_PROPERTY('CacheHits')
'448796','448539'

Clearly, either I'm as dumb as a sack of rocks, or something isn't doing it's job.

Or both.

According to the Help, CacheRead and CacheHits properties are important http://dcx.sybase.com/index.html#1200en/dbusage/perform-s-5421283.html

Detecting whether memory is a limiting factor To detect whether memory (buffer pool size) is a limiting factor, check the CacheHits and CacheReads database statistics. If these statistics are not present on the graph, click the Add Statistics button and select CacheHits And CacheReads. If CacheHits is less than 10% of CacheReads, this indicates that the buffer pool is too small. If the ratio is in the range of 10-70%, this may indicate that the buffer pool is too small—you should try increasing the cache size for the database server. If the ratio is above 70%, the cache size is likely adequate. Note that this strategy only applies while the database server is running at a steady-state—that is, it is servicing a typical workload and has not just been started.

[johnsmirnios · Accepted solution]

Every CacheHit is also a CacheRead: "CacheRead" means that a task tried to look up a page in the cache and "CacheHit" means it was already there. SYSCOLUMN and SYSOBJECT are usually pretty small tables so once they are all in cache, all subsequent cache lookups for those same pages count as both CacheRead and CacheHit. I think what you are really seeing is that we do a lot of page lookups to execute that query and the cache, given that it only needs to hold a few pages for this query, is very effective.

[Breck_Carter · Accepted solution]

Are you saying the calls to sa_flush_cache() don't work?

[MCMartin · Accepted solution]

I think the question is under which circumstances occurs a cache read without a cache hit? @John: By the way, in a productive DB I also see a relation of 1.0005 between cache hit and cache read.

[johnsmirnios · Accepted solution]

@Breck, I'm saying that sa_flush_cache does work. After the flush, perhaps 50 or so CacheReads will not be Cache hits but hundreds or thousands afterward will be both. @Martin: the statistics are not SMP-safe -- at least on some versions though I think v11 & v12 should be fine. On older versions it's entirely possible that the counters will be a little inaccurate due to lost updates. It's also possible that there are conditions under which the stats are not updated correctly though Breck's situation seems largely reasonable to me.

[johnsmirnios · Accepted solution]

A quick test of Breck's case showed that the change in (CacheReads-CacheHits) from one invocation to the next of "flush;select" was larger than the change in DiskReads between invocations and that's a bit unexpected. I'll look to see if I can spot why that discrepancy exists.

[johnsmirnios · Accepted solution]

The discrepancy is due to 'DiskReads' being the number of IOs (contrary to the current documentation) and some IOs bring in multiple pages. 'PagesRead' at one time returned the number of pages but someone changed it to return the number of bytes at some point! Gah! That will need to be fixed up. In any case, the discrepancy is explained. 🙂

[Breck_Carter · Accepted solution]

@John: OK, I'm convinced... Going to change Foxhound to show "Cache Satisfaction" to two decimals, to see what happens in the wild; i.e., 99.70% may indicate a problem whereas 99.99 doesn't.

[Breck_Carter · Accepted solution]

@John: ...and now for some thread drift. I am not convinced sa_flush_cache() really does flush everything. I have anecdotal evidence that a Query From Hell has the worst performance after server startup, and thereafter performs better even if sa_flush_cache() is called. Of course, it performs a LOT better if the cache is not purged... and the behavior may be due to better stats. But I don't think so... this particular table is unchanging and has been pounded by a zillion queries lately so the stats must be good. I suspect sa_flush_cache()... version 11.0.1.2276.

[johnsmirnios · Accepted solution]

How much difference are we talking about here? sa_flush_cache does not throw table/index/procedure definitions out of memory or clear any cached plans -- just cached disk pages. The first invocation of a large query might grow the temp file and/or the cache and sa_flush_cache() won't undo those changes either. The disk or controller might cache data between runs too but if that's what is causing the difference I'd expect performance to be worst after a reboot. You could monitor the IO performed for each run and verify that it doesn't change (except for initializing the temp file perhaps).