Small confusion for point a
July 07, 2020 - 7:09 am UTC
Reviewer: Alok Kumar from UK
Thanks Tom for quick reply. Appreciate your help.
I have a small double for point a.
When you say Auto_Invalidate will spread out the hard parsing for all the 1000 queries , how? Default value of auto invalidate is 5 hours. So my understanding is after 5 hours of running the stats gather job, hardparsing will start immediately for all these queries. So, for any reason if my stats gather job has produced some bad plans it’s effect and system slowness will be visible after 5 hours. Please correct me if i am wrong.
July 08, 2020 - 6:40 am UTC
Think of it like a "raffle" or "lottery".
- We have an SQL with an existing plan.
- We gather stats which mean (theoretically) that plan should ditched and the query re-parsed
- We take that SQL and "roll the dice" that comes up between 1 and 18000. If the number of seconds elapsed since gather stats is more than our number, then we parse, otherwise we keep using the old plan.
Thus over the course of 5 hours, we'll slowly parse everything again (assuming its requested to run again).