On 14/04/2015 11:39, Tim Ward wrote:
If SET STATISTICS is done on one connection, when does it take effect?
What about a transaction that was already running at the time someone
else did SET STATISTICS, does that use the new statistics for any new
statements it runs (where all the code running is in stored procedures)?
What about a connection that was already in existence at the time
someone else did SET STATISTICS, does that use the new statistics for
any new transactions it runs (where all the code running is in stored
procedures)?
Nobody?
The scenario was
(1) Newly set up system, bits of data copied from elsewhere.
(2) System started up. One process in particular holds its connection
open forever (but the transactions it uses across that connection are of
finite length).
(3) Observed that one operation this process performed was taking about
80 seconds.
(4) After a few days of operation (millions of things happening to
various records in various tables) SET STATISTICS was run.
(5) Observed that this one operation was still taking about 80 seconds,
despite the fact that if its queries were run manually in another, new,
connection they took milliseconds.
(6) Stopped and restarted the process in question, so that it closed its
connection, opened a new one, and carried on doing exactly the same thing.
(7) Observed that the operation that used to take 80 seconds now took 4
seconds.
What I don't know is /why/ restarting that process instantly made that
particular procedure call 20 times faster, and I'm trying to get some
clues as to whether starting a new connection after the SET STATISTICS
had run could have had any effect.
--
Tim Ward
