SQLRNNR (3/26/2015)

---

dwain.c (3/26/2015)

---

ChrisM@Work (3/26/2015)

---

Where’s a plan showing huge Estimated Operator Costs when you need one? Have a look at the estimated plan for this conditional query i.e. the IF as well as the SELECT. You probably don’t want to execute the query in prod and you don’t need to – you only see the skew in the estimated plan. The explanation is surprisingly simple 😉

IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))

SELECT 'Anything' FROM sys.columns a

FULL OUTER JOIN sys.columns b

ON a.[precision] = b.[precision]

Quite a bizarre estimated plan to be sure. Facts:

- The issue goes away if you remove the IF

- The issue doesn't show in the actual execution plan

- The query itself runs reasonably quickly.

Edit: This query shows the same thing:

IF EXISTS (SELECT 1 FROM (VALUES(CAST(1 AS BIT))) d (n))

SELECT * FROM sys.all_columns;

Which means that it has something to do with the 100% cost calculated for the Constant Scan generated for the IF. The remainder of the query must be getting its costs rated against that.

Add this to the list of SQL Developer interviewee questions...

Interesting indeed.

I generally "manufacture" a plan if I really want to demonstrate something out of whack and quite get it to show with data or fascinating queries. It is all just xml afterall.