Making The Query Plan Profile Hint More Useful

Way Back When

I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name — query_plan_profile.

There are currently some rather unfortunate limitations to the event in the GUI. Right now, there’s no query plan tab so you can easily view the plan, you have to collect the text of the query separately, the session doesn’t collect any plan-identifying hashes by default, and the Select operator is missing from the plan.

Plus, in typical Microsoft fashion, information is presented alphabetically, instead of grouping related attributes.

If one undertook the task of deciphering the different memory portions of the query plan, one would find themselves quite disappointed.


If You Wanna Get Some Basic Data Out

And you created the same Event that I did, you can do some of this:

And some of this:

In case you were wondering — yes, this is the weirdest bit of XQuery I think I’ve ever written:

So weird that I asked Mikael Eriksson about it, and he Swede-shamed me into doing things the right way:

If you want to aggregate things a bit, you can do this:

If you want to check other useful DMV data, you can do this:

Will This Take Off?

Well, I don’t know.

Part of me hopes so, because one of my wish list items has always been having the additional “actual plan” information to analyze in BlitzCache, and this looks like a close bet.

It’s tempting, but I think I’m going to hold off to see if it gets any traction first. I tried to aid traction with sp_BlitzQueryStore, but I don’t think that really worked, both because of the slow trickle to 2016+, and the weariness of turning the feature on for various reasons.

I’m hopeful that it’ll get better someday, but I think I’d have to run into a few clients using this before I invest a lot of time in it.

Thanks for reading!

Previous Post
SQL Server Management Studio 18’s Execution Plans Will Change The Way You Look At Plan Tuning
Next Post
[Video] Office Hours 2018/10/3 (With Transcriptions)

2 Comments. Leave new

  • Great post, Erik.

    A (somewhat) on-topic question, what is the difference between “Granted_memory_kb” and “used_memory_kb”? In simpler terms, it seems like SQL Server has “given” “Granted_memory_kb” of RAM while the query itself has used only “used_memory_kb”. But would this mean the unused “Granted_memory_kb” would be unavailable for any other SQL use?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.