SQL Server Query Plans Need A Replay Button

Posted on by Erik Darling

No Repro, No Cry

Sometimes you pull a query plan from the plan cache or query store, and it’s not really clear why it was slow. You want to get an actual execution plan, but…

When you look at the query text, there are a whole bunch of parameters, and… You know what comes next.

CREATE OR ALTER PROCEDURE
    dbo.CollectemAll
(
    @Id int,
    @AcceptedAnswerId int,
    @AnswerCount int,
    @CommentCount int,
    @FavoriteCount int,
    @LastEditorUserId int,
    @OwnerUserId int,
    @ParentId int,
    @PostTypeId int,
    @Score int,
    @ViewCount int,
    @TheDiff int,
    @ClosedDate datetime,
    @CommunityOwnedDate datetime,
    @CreationDate datetime,
    @LastActivityDate datetime,
    @LastEditDate datetime,
    @Body nvarchar(MAX),
    @LastEditorDisplayName nvarchar(80),
    @Tags nvarchar(300),
    @Title nvarchar(500)
)

You have to dig into the plan XML, grab an unfortunate wall of text, and…

  • If it’s a stored procedure, grab the values and execute it
  • If it’s an ORM or other parameterized query, have a real bad time
<ColumnReference Column="@LastActivityDate" ParameterDataType="datetime" ParameterCompiledValue="'2008-01-01 00:00:00.000'" ParameterRuntimeValue="'2008-01-01 00:00:00.000'" />
<ColumnReference Column="@CreationDate" ParameterDataType="datetime" ParameterCompiledValue="'2008-01-01 00:00:00.000'" ParameterRuntimeValue="'2008-01-01 00:00:00.000'" />
<ColumnReference Column="@Score" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@PostTypeId" ParameterDataType="int" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />
<ColumnReference Column="@OwnerUserId" ParameterDataType="int" ParameterCompiledValue="(22656)" ParameterRuntimeValue="(22656)" />

You can either create a temporary stored procedure to execute the ORM query, or declare a bunch of variables and use dynamic SQL to have them behave like proper parameters. Local variables are not a substitute.

This sucks.

Storage Unit

Since all of these details are already stored in the query plan, why do we have to do all this work?

I get it to a point. Things like temporary objects or modification queries complicate things. But for simple select queries it should be a clear shot to hit play and have the query execute to get an actual execution plan.

There’s also limitations in the plan XML, like if you’re one of those awful people who writes 50 page queries that can’t fully be stored in there. This becomes somewhat less of an issue with query store, since the full query text is available alongside the execution plan.

But I’m fine with hitting errors in those cases, because you usually have a lot more factors to contend with.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Related Posts