What’s the optional_spid in sys.dm_exec_plan_attributes?

What’s the optional_spid in sys.dm_exec_plan_attributes?

  • Comments 2

 

My answer to a question asked by Vedran Kesegić asked earlier this week in the MCMs group:

Queries accessing non-dynamically bound temp tables, if issued from different connections would recompile to bind to the right temp table. This would cause a lot of recompiles that would have a performance impact for a workload that uses such mechanics.

For example:

-- connection 1

create table #t (a int)

go

insert into #t values (2)

go

select * from #t  -- This would return 2.

go

-- connection 2

create table #t (a int)

go

insert into #t values (5)

go

select * from #t        -- This would return 5

go

In the above scenario, the select query in connection 1 will get cached. If now the select query in connection 2 is issued, it will cause a recompile. If, subsequently, the select query in connection 1 is reissued, it will cause another recompile.

To prevent the recompiles, we store the spid in which a query that accesses non-dynamically bound temp tables is issued.

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
  • Hi Nacho,

    Great post! I think there is one minor typo in one of your comments and connection to is mislabled as connection 1 again. (I was pretty sure I knew what you meant, but had to read it twice to make sure.)

    Thanks for blogging, I love your writing!

    Kendra

  • Thanks Kendra! It is corrected now. And thank you for reading my blog, I love you making me feel useful. ;-)

    Nacho

Page 1 of 1 (2 items)