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-cachehits-or-me

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.

asked 31 Oct '10, 12:40

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

edited 31 Oct '10, 12:57


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?

permanent link

answered 01 Nov '10, 13:48

John%20Smirnios's gravatar image

John Smirnios
9.0k377114
accept rate: 39%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×246
×107

question asked: 31 Oct '10, 12:40

question was seen: 702 times

last updated: 01 Nov '10, 13:48