The New Lightweight Query Plan Profile Hint

Last Updated 6 years ago

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.

CREATE EVENT SESSION query_plan_profile
    ON SERVER
    ADD EVENT sqlserver.query_plan_profile
    ( SET collect_database_name = ( 0 ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\query_plan_profile' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 5 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

ALTER EVENT SESSION query_plan_profile ON SERVER STATE = START;
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

CREATE EVENT SESSION query_plan_profile

ON SERVER

ADD EVENT sqlserver.query_plan_profile

( SET collect_database_name = ( 0 ))

ADD TARGET package0.event_file

( SET filename = N'c:\temp\query_plan_profile' )

WITH ( MAX_MEMORY = 4096KB,

EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY = 5 SECONDS,

MAX_EVENT_SIZE = 0KB,

MEMORY_PARTITION_MODE = NONE,

TRACK_CAUSALITY = OFF,

STARTUP_STATE = OFF );

GO

ALTER EVENT SESSION query_plan_profile ON SERVER STATE = START;

GO

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

SELECT   TOP 1000
         u.Id, u.DisplayName, u.Reputation, u.CreationDate, u.LastAccessDate
FROM     dbo.Users AS u
ORDER BY u.Reputation, u.CreationDate
OPTION ( USE HINT ( 'QUERY_PLAN_PROFILE' ));

1

2

3

4

5

SELECT TOP 1000

u.Id, u.DisplayName, u.Reputation, u.CreationDate, u.LastAccessDate

FROM dbo.Users AS u

ORDER BY u.Reputation, u.CreationDate

OPTION ( USE HINT ( 'QUERY_PLAN_PROFILE' ));

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.

For example, the query_post_execution_plan Event looks like this.

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.

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.

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:

You just ran the query, bonehead, you know damn well what it was.
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.

CREATE EVENT SESSION query_plan_profile
    ON SERVER
    ADD EVENT sqlserver.query_plan_profile
    ( SET collect_database_name = ( 0 )
     ACTION ( sqlserver.plan_handle, 
              sqlserver.query_hash_signed, 
              sqlserver.query_plan_hash_signed, 
              sqlserver.sql_text )
              )
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\query_plan_profile' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 5 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

CREATE EVENT SESSION query_plan_profile

ON SERVER

ADD EVENT sqlserver.query_plan_profile

( SET collect_database_name = ( 0 )

ACTION ( sqlserver.plan_handle,

sqlserver.query_hash_signed,

sqlserver.query_plan_hash_signed,

sqlserver.sql_text )

)

ADD TARGET package0.event_file

( SET filename = N'c:\temp\query_plan_profile' )

WITH ( MAX_MEMORY = 4096KB,

EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY = 5 SECONDS,

MAX_EVENT_SIZE = 0KB,

MEMORY_PARTITION_MODE = NONE,

TRACK_CAUSALITY = OFF,

STARTUP_STATE = OFF );

GO

… 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!