Hi Kevin, yes, I agree with you, UDFs are a nightmare for performance. I have replaced them before and have looked like a hero. haha. And when I take out the UDFs from the query, the query runs in 2 seconds flat! With UDFs, it may run for 1 or 2 days! Horrible!

And your idea to compare the xml execution plan was right on. Most of the differences were too small (for example, EstimateCPU =0.000441494, EstimateCPU = 0.000440599), but the following sticks out:

Staging server:

<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="707788" EstimatedPagesCached="88473" EstimatedAvailableDegreeOfParallelism="2" />

Production server:

<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="353894" EstimatedPagesCached="176947" EstimatedAvailableDegreeOfParallelism="4" />

Do you see any problems with such large differences? Any idea what I can do to get these numbers to match?