Why Query Optimizer Fails?

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
1. Why query optimizer fails to produce correct execution plan?
2. What are secnarios in which query optimizer fails?

Thank you in advance for your answers.

more ▼

asked Feb 24, 2011 at 08:10 AM in Default

avatar image

Ravi Kumar V
43 1 1 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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

more ▼

answered Feb 24, 2011 at 08:18 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left

Grant Fritchey is the king on that one.

Basically some of the ways it can fail are with:

  • parameter sniffing issues

  • outdated statistics

  • inefficient/non-existing indexes

  • parallel operations

  • user defined hints

more ▼

answered Feb 24, 2011 at 08:24 AM

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 24, 2011 at 10:41 AM

avatar image

15.6k 22 55 38

Excellent point, it's not a failure its a balanced/calculated decision to stop evaluating further

Feb 25, 2011 at 01:41 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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:

David DeWitt Keynote PASS Summit 2010

more ▼

answered Feb 24, 2011 at 11:44 PM

avatar image

26.2k 18 37 48

An excellent choice. I love this presentation.

Feb 25, 2011 at 09:15 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 24, 2011 at 08:10 AM

Seen: 1745 times

Last Updated: Feb 24, 2011 at 08:16 AM

Copyright 2018 Redgate Software. Privacy Policy