DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: General
       *****************************************************
       #Post#: 82--------------------------------------------------
       What’s the optional_spid in sys.dm_exec_plan_attributes? 
       By: srinivasma_exceldbp Date: July 21, 2014, 11:41 pm
       ---------------------------------------------------------
       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, the spid ( optional_spid )  in which
       a query that accesses non-dynamically bound temp tables is
       issued.
       *****************************************************