What’s the optional_spid in sys.dm_exec_plan_attributes?
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.