The New Lightweight Query Plan Profile Hint

Recent Updates

To SQL Server 2016 and 2017 introduced a new USE HINT that lets you direct an actual execution plan to a new Extended Event, called query_plan_profile.

The hint by itself doesn’t do anything, and the XE by itself doesn’t do anything. You need to have both.

Alright then, let’s do that.

Sessions

Assuming you’re on a supported version, and you have a c:\temp directory, you should be able to just F5 this.

With that set up, let’s run a query that will trigger our event. We’ll start simple

Now let’s view our event data. Yeah, I know. The words you’ve been waiting to hear.

Okay, little weird. In other Events that collect query plans, there’s a little tab that has the query plan in it.

Fwhaaaa

For example, the query_post_execution_plan Event looks like this.

Details, details

Well, maybe that’s coming in the next version of SSMS, or something.

Or maybe we can click on the XML and get a plan to open up. Let’s try that.

Not JSON

Alright, so that didn’t work, but it did open the XML, so we can save it and then open it as a graphical plan.

I’m still excited! Alright! Let’s take a look.

A plan at last

If we click around, it has all the stuff you’d expect a plan to have.

Except a SELECT operator. The first node is the TOP, so you don’t get some of the information you’d usually get from properties and tool tips there.

You get some of it in the details pane, but not all of it.

Well, that’s okay. Let’s look at the text of our query. I mean, no one’s just going to look at a query plan and be able to tell what their query was.

Alright, so no query text there.

Thinking Time

If you’re like me, you’re thinking two things:

  1. You just ran the query, bonehead, you know damn well what it was.
  2. I bet this’ll work if you collect sql_text in the Event.

And yeah, you’re right, I do know. This time.

But if I have multiple people using this technique to get plan information, it might not be so easy.

Adding the sql_text to the Event will show the query that ran in the Details pane, but it still won’t show the query text if you try to access it from the plan itself.

Collecting the text of a query won’t always be light, so I sorta get why it’s left out. Though I don’t think you’d add this to, like, every statement in a really long procedure, and if you’re doing this for a single really long query, you probably know what you’re getting yourself into.

But there’s nothing to uniquely identify a plan in the Event. If you wanna use other DMVs to get plan information, you have to set it up to grab the query and plan hashes from the get go.

Ideally, you’d set the session up to look like this.

… And then write a bunch of XQuery to parse the Event, join to various system views and functions, and maybe even convert that plan XML to something you could just click on.

But That’s For Next Time

It is, after all, Saturday morning, and my wife is once again staring at me because I’m sitting here writing instead of getting ready to leave the house.

Thanks for reading!

Previous Post
When You Need to Tune A View, Don’t Just Get Its Plan
Next Post
Unused Indexes – Are they really unused or have they just not been used YET?

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.

Menu
{"cart_token":"","hash":"","cart_data":""}