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