 <p>I find that typically a repeated CTE gets no performance improvements.</p> <p>So for instance, if you use a CTE to populate a table and then the same CTE to join to in a later query, no benefit. Unfortunately, CTEs are not snapshots and they literally have to be repeated to be used in two separate statements, so they tend to be evaluated twice.</p> <p>Instead of CTEs, I often use inline TVFs (which may contain CTEs), which allows proper re-use, and are not any better or worse than CTEs in my SPs.</p> <p>In addition, I also find that the execution plan can be bad if the first step alters the statistics such that the execution plan for the second step is always inaccurate because it is evaluated before any steps are run.</p> <p>In this case, I look at manually storing intermediate results, ensuring that they are indexed properly and splitting up the process into multiple SPs and adding WITH RECOMPILE to ensure that later SPs have plans that are good for the data which they are actually going to operate on.</p>