A couple of days ago I posted a stored procedure (sp_GetAGInformation) which queried multiple DMVs to pull together a bunch of AvailabilityGroup information. If you took a look at the code you would see that it used a couple of CTEs (Common Table Expressions).

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:

Basic CTE

Is the same thing as:

Basic Subquery

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.

sys.availability_groups plan

sys.dm_hadr_availability_replica_states plan

A rewrite to use Table Variables made a significant performance improvement and eliminated the one off crazy plans. To understand why let’s take a look at the final plans comparing the Table Variable version of sp_GetAGInformation and the CTE based sp_GetAGInformation_Slow  (all plans pulled using the free SQL Sentry Plan Explorer)

sp_GetAGInformation_slow – click to expand

sp_GetAGInformation – click to expand

As you can see, the CTE based plan is large. It actually take a 3MB memory commit to run, and when it does so there are a large number of TVFs in there that get joined to other TVFs.

sp_GetAGInformation_slow more details – click to expand

I encourage you to pull the procs and take a look at the plans on your local test systems, and to test performance with CTEs versus Table Variables/Temp Tables. The difference might surprise you. But for sure, avoid using DMVs within CTEs in your queries.