This is my favorite script to work on

Getting stuff to work sp_BlitzCache is so rewarding because XML querying is so ridiculous. I wish Mikael Eriksson would write a book already; it’d make my life a whole lot easier.

Along the way, I see a lot of things that I would love to warn people about, but it’s either not present in cached plans, or not retained in the XML at all.

Here’s a list of my top 5 Query Plan XML wishes

Isolation Level

It would be great to be able to call out potentially harmful isolation levels.

Aside from read uncommitted, being able to warn about repeatable read or serializable would be nice. Especially when it’s set at the query level.

On a related note…

Locking Hints

There’s an open issue for this in the GitHub repo.

The best option so far is wildcard searching the query text for hints like ROWLOCK, PAGLOCK, HOLDLOCK, AND UPDLOCK. That stinks, especially for long queries.

This seems like really logical information to include. If someone didn’t put the hints in, the SQL Handle would be different, etc.

Partition Information

This is only available in actual plans! You can see which partitions were used, and which were eliminated.

At minimum it would be nice to tell people if they’re getting any partition elimination, but that’s a no go with cached plans.

TempDB Spills

Another actual-only piece of information. I understand why this isn’t in the XML. One execution might spill, another might not.

It would be cool to have aggregate information about spills available somewhere, like sys.dm_exec_query_stats though, since so much other related information is in there.

Cascading Foreign Keys

Did you know that when foreign keys perform cascading actions (update/delete), they take out serializable locks under the covers?

That stinks. And the actions just get tacked onto the plan without any notice. Catching these and triggers would be fun.

People might be surprised how much weight they can tack on.

Is there anything I’m missing?

If you take a look at the showplan schema, there’s tons of information to harvest. If there’s anything you’d like included in sp_BlitzCache, head on over to GitHub to make a request.

Just make sure it can be caught in cached plans, and that you can provide a repro script. There’s a lot of great stuff that’s actual only in all that mess, like the newly added wait stats.

Thanks for reading!