Improving Performance When Querying Multiple DMVs

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

Basic CTE

Is the same thing as:

Basic+Subquery

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

sys.availability_groups plan

sys.dm_hadr_availability_replica_states

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+Full+Plan
sp_GetAGInformation_slow – click to expand
sp_GetAGInformation

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+expanded
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.

One thought on “Improving Performance When Querying Multiple DMVs”

  1. Hi Nic,

    I’m pretty sure that all links to sp_GetAGInformation are broken. Thought you’d like to know.

    Really enjoy your blog —

    Ned Otter

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s