Erik Darling explains that abstraction can be the cause of performance woes in SQL Server:
There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.
Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.
Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.
It’s a logical separation, not a physical one.
In addition, as your query gets more and more complex, the optimizer eventually gives up and gives you what will likely be an ugly version of its implementation because there are too many potential solutions.