CTEs are a great way to do recursive work, and they can also greatly simplify reading code. A CTE without recursion is really nothing more than a subquery that is nicely wrapped.
For example:
Is the same thing as:
This can easily lead you down the path towards poor performance. It is quite easy to define a CTE once and use it multiple times, not realizing that every time you use the CTE then you are performing that subquery, meaning is has to be evaluated and executed.
For smaller queries this is not usually a problem, but for larger queries and more importantly when working with DMVs this can be a serious performance problem.
The original version of sp_GetAGInformation used CTEs exclusively, and it seemed to run ok. Once it was deployed I found that every once in a while it would perform horrendously. The usual execution time was around a second, but sometimes it would run for 5 minutes or longer. When I started to track down the performance details I came to discover that a lot of the DMVs are using Table Valued Functions under the covers. These can have inherent performance problems when it comes to joins as there are no associated statistics.
Continue reading on SirSQL.net.