|
In most cases, the SQL Server Query Optimizer will correctly evaluate a query and run it as optimally as possible. But on occasion the Query Optimizer will fail, producing a less than optimal execution plan, and query performance will suffer because of it. This is correct, but now the questionn which comes to my mind is Thank you in advance for your answers.
(comments are locked)
|
|
The optimiser will produce a 'good enough' plan as fast as it can. There is little point checking every possible plan and maybe taking minutes to do it when a good enough one will have taken less effort to actually complete and the optimal plan would only be a tiny % faster. Its the law of diminishing returns in effect. watch this video of Conor Cunningham presenting at SQLBits conference all about the optimiser. http://sqlbits.com/Sessions/Event6/Inside_the_SQL_Server_Query_Optimizer
(comments are locked)
|
|
Grant Fritchey is the king on that one. Basically some of the ways it can fail are with:
(comments are locked)
|
|
Fatherjack and Blackhawk have excellent answers. Also, while I would not call it a failure per se, the optimizer for standard edition will not take advantage of indexes on views unless the noexpand hint is supplied, as mentioned at http://www.sqlservercentral.com/articles/Indexed+Views/63963/, though it will handle them quite nicely in Enterprise edition. Excellent point, it's not a failure its a balanced/calculated decision to stop evaluating further
Feb 25 '11 at 01:41 AM
Fatherjack ♦♦
(comments are locked)
|
|
You can also see an outstanding video of David DeWitt's keynote at the PASS Summit in 2010 explaining how the QO actually arrives at a specific plan. Basically, there are a mind-boggling number of possible plans, even for a simple query with 2 joins. You have to be a registered PASS user (free of charge) to view the video, but here is the link: An excellent choice. I love this presentation.
Feb 25 '11 at 09:15 AM
Grant Fritchey ♦♦
(comments are locked)
|
