I know, if the statistic is not updatad, it is possible, that the optimizer doesn't choose the optimal execution plan?
Does any other reason/reasons exist?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Fisrt optimizer takes one of best plans - not the best. This is because when the optimizer works the time for calculating the best plan can be bigger than the time it saves during the execution. And yes, optimizer works based on statistics. So if your statistics is old enough optimizer will create plan suiatble for that statistics - i.e. for the moment of time when the statistics was last recalculated. As far as I know the plan also depends on the amount of data that you have.
The optimizer strives to get a plan that is "good enough", and this is not always the optimal one.
A very common reason is a too complex query. Breaking it down to a few queries helps the optimizer choose a better plan.
In some cases, too many indexes on a table can also cause this, as the optimizer might use an index that is not the best one because as we said, it doesn't look for the best plan but rather a plan that is good enough.
Other reasons can be the use of local variables (the optimizer doesn't know their values), non SARGABLE arguments, missing indexes, improper use of hints and plan guides, and more..