Please implement sa_flush_cache_with_real_attitude()

[Breck_Carter]

Please implement a method of returning the server to a "just started" state as far as the RAM cache and temporary file are concerned... something with real attitude, something a former drill sergeant would be comfortable with...

http://www.youtube.com/watch?v=JhlWddAXSRA

Background: Here is John Smirnios' comment on this Q&A: http://sqlanywhere-forum.sap.com/questions/1276/which-one-is-useless-sa-flush-cache-cacheread-cacheh...

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). – John Smirnios

Justification: When testing and retesting and retesting (yawn) changes to a Query From Hell, it would be very convenient to restore the server to its starting state for each test, rather than have to... sigh... shut it down... start it up... reconnect with dbisql... or, heaven forfend! reestablish an Application Profiling or Database Profiling session! Egad! Zounds!

---

It does occur to me this might be impossible without dropping all connections... but that would still eliminate the need to shut down and start up the server.

[johnsmirnios]

You could run your test using auto-start but actually implementing a statement to take a running server to the start-up state would be an immensely complex undertaking, would still only be an approximation at best, and is a problem that has an easy work-around: stop and restart the server 🙂 Admittedly, though, I have no idea how autostart fits in with application profiling -- I've never used it myself.

Is startup truly representative of how you want to run your query? In some cases, I'm sure it can be but then you need to accept that there is a lot of unavoidable work done at startup that gets well amortized over the lifetime of the server.

In your previous posting, you said there was a time difference between first-run and after sa_flush_cache() but didn't elaborate on how big that difference was. Do you think the factors I listed could account for the difference you are seeing?