SQL Server – Does Server Calculate Views on Each Query?

I isolated the problem to one line in the query. Keeping in mind that the query is 160 lines long, and I'm including the relevant tables either way, if I disable this line from the SELECT clause:

COALESCE(V.Visits, 0) * COALESCE(ACS.AvgClickCost, GAAC.AvgAdCost, 0.00)

...the run time drops from 63 minutes to five seconds (inlining a CTE has made it slightly faster than the original seven-second query). Including either ACS.AvgClickCost or GAAC.AvgAdCost causes the run time to explode. What makes it especially odd is that these fields come from two subqueries which have, respectively, ten rows and three! They each run in zero seconds when run independently, and with the row counts being so short I would expect the join time to be trivial even using nested loops.

Any guesses as to why this seemingly-harmless calculation would throw off a TVF completely, while it runs very quickly as a stand-alone query?

Good question. While it is true that view definitions are expanded into the query text very early on in the compilation and optimization process, all sorts of information is tracked with the cached plan that is not directly visible e.g. statistics that were considered 'interesting' for plan exploration, so that we get the expected recompilation if those statistics change. This is true even if the final plan shape chosen did not directly make use of the changed statistics - we should still recompile because there may now be a better plan available.

In a similar vein, a user would reasonably expect that 'using' a view should block any concurrent attempt to change the definition of that view. This should hold true even though the implementation expands view text before any real compilation or optimization occurs. To that end, it makes sense that the view's object_id should be part of the cached plan and an otherwise-very-compatible Intent-Shared lock taken each time that plan is executed. Holding this lock prevents another session acquiring Sch-M (schema modification) on that object and modifying the view while we are logically referencing it (even though it was expanded). Obviously locks on the objects referenced by the view would not be enough.

To the question of whether this sort of thing should be documented or not. The shape of Books Online over the years suggests that documenting something means it has to go through a cycle of deprecation before it can be changed, so unless that apparent policy changes so that 'documentation' is expanded to include 'interesting details that nevertheless might change at any time', it seems unlikely that this sort of internal implementation detail would ever make it into the formal documentation.