x

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

Ravi Kumar V gravatar image

Ravi Kumar V
43 1 1 1

(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][1]

[1]: http://sqlbits.com/Sessions/Event6/Inside_the_SQL_Server_Query_Optimizer
more ▼

answered Feb 24, 2011 at 08:18 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

(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

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(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/][1], though it will handle them quite nicely in Enterprise edition.

[1]: http://www.sqlservercentral.com/articles/Indexed+Views/63963/
more ▼

answered Feb 24, 2011 at 10:41 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

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][1]

[1]: http://r20.rs6.net/tn.jsp?llr=shoqy6bab&et=1104621256114&s=55936&e=00169PBJ4JLFna19u074q31F1TAypFKINGQ59BCc7113uvhdMsQxhwp7QSFHAHMU1jPGvnU_krD-O6ZaVfdF6F5l13cufE10xYGQHrQXF9d00xHupEeXAb-FLuK0ljO4xsu6rHR7kvdZ4BJg9rAeaubBJLw7qg3zoDBB_awi-KKUs1CWqpBcXRgaynUNvlTZ-lFz11hdeX6Dp7nPFG-X7LbInZ8zDRYNDFcPUGH-hFEwbq4QwSU6sALZPew2u5f-QqRl27CvxiHn5E=
more ▼

answered Feb 24, 2011 at 11:44 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x369
x36

asked: Feb 24, 2011 at 08:10 AM

Seen: 1349 times

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