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
asked 26 Oct '10, 20:59 Breck Carter |
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. answered 27 Oct '10, 01:02 John Smirnios Are you saying the calls to sa_flush_cache() don't work? 1
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. 1
@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. 1
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. 2
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. :) @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. @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. 2
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). More comments hidden
|