Adding a single row union dramatically reduces performance on a CTE query

[JasonMcD (Jason McDermott)]

I’m no expert, but I think I have seen this before myself and it is pretty bewildering.

I will say, I have learned of a trick that may help - specifically the OPTION(RECOMPILE) thing in a BAQ:

BAQ 100x slower in Production than Dev/test (Vote for this Idea for an easier fix in the future)

So your statistics may be out of date @JasonMcD I would suggest you run a quick statiscs update on all tables involved in this BAQ in Production and see what happens. 9/10 times it flies right after. I’m not sure how you are doing maintainence on your DB but statistics are as important if not more than indexes sometimes. Try this (this takes a few seconds to run even on the largest of tables and it shouldn’t hurt much maybe a small performance hit for a little while but perfectly fine in my ey…

But heed the statistics advice, too.