Grant Fritchey (4/29/2016)

---

N_Muller (4/29/2016)

---

Grant Fritchey (4/29/2016)

---

N_Muller (4/28/2016)

---

Or is it a hit and miss in terms of queries that perform poorly.

I'll just pile on and agree.

The main issues I've seen are with multi-statement, table-valued, functions where the default row count was changed from 1 to 100. The other place is when the value passed falls outside of statistics. Instead of assuming one row, it assumes an average of the returned values for the existing rows. In some cases that causes issues.

That begs the question regarding table-valued functions. I have many of those. Would it be better to gather the data from the TVF into a temp table, and then join, or just keep it in the join.

One other point I noticed re:TVF is the location of the TVF (with cross apply) on the join. I had a query joining six tables and one TVF. The very first table on the join limited the number of calls to the TVF based on the cross apply. However, performance changed significantly as I moved the TVF across the join, from second to last, with last being the worst. Note I did not have option FROCE ORDER set, so SQL Server decided on the order of joins.

It really does depend on the query, the plan, whether or not the optimizer is timing out, but, the position of a JOIN or APPLY within the query shouldn't matter because the optimizer, assuming no timeout, will rearrange the order to optimize the performance.